Search code examples
ssisoracle11gsql-server-2008-r2cdc

CDC calling SSIS real time in SQL Server 2008 R2

I need to push data from SQL Server 2008 R2 in real time to Oracle 11g. The solution proposed are to use CDC and SSIS.

What I understand is CDC is just like a system table which captures changes in registered tables. SSIS is more like an ETL tool.

What I understand is that someone or process need to execute SSIS for pulling data from CDC and then pushing it to Oracle tables.

So do we need to schedule it?? Or there is some other mechanism such that it will be called automatically when there is data is CDC tables.

If it need to be scheduled then it’s not real time (more like near real time).

Solution

  • How much latency is in real time? 0 seconds? For SQL > Oracle that's impossible.

    Is 1 second acceptable? what about 5 minutes?

    You need to accept some kind of latency, you'll never get real time from SQL to Oracle. I work with a system transferring data from many systems (including SQL Server) into Oracle but it has about 10 minutes latency. It applies all changes found in the last ten minutes. It could probably be improved but there is actually no requirement to.

    In answer to your question, yes, something has to call schedule SSIS (possible a scheduler)

    You could technically call a SSIS package from a table trigger so that it runs instantly that a change is made, but a package takes 1000's of times longer to run than it does to make changes to a table so that's not going to work.

    So practically speaking you have to accept some change queuing at the source and some change queuing at the target.

    If you need much less latency you should consider a queue type setup (SSIS not involved) where SQL Server changes are posted to some kind of queue on the Oracle side and processed by Oracle

    To reduce load on the SQL Server source, use native SQL Server CDC http://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx rather than manually created triggers and log tables.

    Whatever mechanism you use to transfer changed data to the subscriber (Scheduled SSIS, send to a queue... whatever), the impact is the same. SSIS gives you no performance benefits.