965
Chapter 41: Data Change Tracking and Capture
41
@source_name = 'Department',
@capture_instance = 'all';Summary
The need to track changes and synchronize changes with minimal performance impact to
the base database (system) is requested in so many organizations that Microsoft developed
multiple features to help you attack the problem.For those of you on Standard Edition: Change Tracking does all the hard work of track-
ing the Primary Key ID of each row that has been updated; add auto cleanup, which is
relatively easy to set up and use; and reliably return the net changes. Without ques-
tion, using Change Tracking sets you up for success with ETL processes and mobile device
synchronization.For those of you running on Enterprise Edition: Change Data Capture (CDC) is Microsoft’s
high-end feature intended for heavy transaction OLTP systems to capture changes for ETL
to the data warehouse.Key points to remember about CDC:■ (^) CDC uses the transaction log asynchronously to reduce the impact on OLTP transac-
tions, but there will be some impact.
■ (^) Using CDC, you can query for all changes or net changes.
■ (^) CDC continues to be reliable even when used with a group of databases made highly
available through the new AlwaysOn features.
■ (^) CDC continues to be enhanced to serve your data monitoring needs and can now
capture changes from an Oracle database.
c41.indd 965c41.indd 965 7/31/2012 10:17:22 AM7/31/2012 10:17:22 AM
http://www.it-ebooks.info