Microsoft SQL Server 2012 Bible

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 tracking 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 question, 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 transactions, 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.