Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Purpose of CDC Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 11:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 529, Visits: 1,569
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?
Post #1553627
Posted Friday, March 21, 2014 12:00 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 23,081, Visits: 31,616
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.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1553631
Posted Friday, March 21, 2014 4:25 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 529, Visits: 1,569
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.
Post #1553706
Posted Tuesday, March 25, 2014 4:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 4,985, Visits: 11,681
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1554368
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse