March 21, 2014 at 11:38 am
I'm working my way through the MS 70-643 (Data Warehousing for SQL 2012) training kit and since I can't directly ask the author questions, I'll ask them here:
I'm reading about enabling CDC (Change Data Capture) on a database, but it seems to me the way I would address this would be to have a modified date stamp on each row of the source table and build a job that looks for rows with a modified date stamp greater than the MAX date stamp on the target table in the warehouse (the last time the job was run). Then use UPDATES to make the rows in the target database match rows match the values in the source database, and of course INSERTS to move the new data.
So is using the CDC feature faster and more efficient? Does it detect only the columns what were modified and UPDATE only the modified columns rather than all columns that might have been modified? Or is it for source databases on systems that modified data stamps aren't used and can't be added because the application software is from 3rd party vendors?
March 21, 2014 at 12:00 pm
Don't have an answer to your question, but I have a question that may help. How would you capture deletes from the source table? CDC should capture this information for you as well as inserts and updates.
March 21, 2014 at 4:25 pm
Good point. As I read more I see that CDC can also be configured to track and capture multiple changed to the same rows between SSIS job runs. There are other ways all this can be accomplished, but CDC does make it simpler to design and implement.
March 25, 2014 at 4:50 am
dan-572483 (3/21/2014)
Good point. As I read more I see that CDC can also be configured to track and capture multiple changed to the same rows between SSIS job runs. There are other ways all this can be accomplished, but CDC does make it simpler to design and implement.
There is an overhead on the DBAs - you should get them on board if you are planning to introduce this.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply