2

I am currently working on SSIS package for continuous incremental data loads of the tables (40 tables now and expected to increase in near future) from OLTP system to a Reporting database residing on a different server.

The request is for real time data sync between the two sources.

Should I continue and test it with just regular SSIS -> Staging -> merge route OR should I look into implementing CDC. Will that be faster than plain old ETL considering the number of tables involved?

2 Answers 2

1

CDC doesn't replace the SSIS -> Staging -> Merge route, it just makes the SSIS part easier - the Extraction of ETL. CDC makes this much easier, it's one of the use cases it was designed for. For every change, you get a copy of the row before and after the change, and you can grab a hold of it and then clean it up.

It does have some gotcha's that aren't obvious before you start. 2 big changes that caught me out were:

  • Schema changes are harder on the source tables, particularly for deployment tools that drop and create tables instead of just amending
  • If you are using mirroring or Availability Groups, the cdc reader will pause when the replica goes offline (see: Change Data Capture not running when Mirror is Suspended)

It also doesn't provide any way to do a full re-sync of the data, it's only ever additive.

I don't think it's possible to answer the question should I do one or the other, the answer (as is often the case) is "It Depends". It's definitely worth investigating though.

Also note, CDC is only available in Enterprise Edition up to SQL 2016 SP1 (when it becomes available in all editions).

1

Have you considered transactional replication? From what I see that would be the best option with the less administrative overhead.

That way you will have the DB in practically real time and can be relatively easily modified to include more tables in the future.

https://learn.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-2017

2
  • Thanks will try that out. Should I be concerned with the replication overhead?(I know CDC also has the same concerns since its replication under the hoods)
    – abbhey
    Oct 19, 2018 at 20:40
  • Not so much overhead, I'd say just to put the distribution on the "destination" server. Also be careful with the transaction log , if for any reason transactions are not replicate do the destination, it will grow until they are replicated.
    – dbamex
    Oct 22, 2018 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.