Sunday January 21st 2018

Categories

Insider

Archives

Tracking data changes in SQL Server 2008 and up

microsoft-sql-server-2008-logoCommonly asked question in interview question. Please note that beginning from SQL server 2008 Microsoft introduced change tracking and change data capture (CDC) to help answer questions about data that changes in a database.
Comparing Change Tracking to CDC

SQL Server 2008 introduces two tracking features that enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Before these features were available, custom tracking mechanisms had to be implemented in applications. These custom mechanisms often required schema changes to the tracked table or the use of triggers. Neither change data capture nor change tracking requires any schema changes at the source or the use of triggers.[1]

When implementing a method to track changes in your database, you need to decide between the change tracking and CDC Method. There difference and benefits are below.
Change Tracking Helpful and in-depth video on this topic
Provide functionality with DML statements.
Can answer question such as;
1. What rows in the table has been changed
2. Has particular row been updated?
3. Did an insert, update or delete occur?
4. Operates synchronously to provide change information immediately.
5. Provides lower storage overhead than CDC.
6. Provides built-in cleanup mechanism.
7. Uses Transaction commit time to determine the order of the changes.
8. Works without requiring schema changes to the table or additional triggers.
9. Must be enabled ate the database level by using ALTER DATABASE.

Change Data Capture Helpful and in-depth Video on this topic
Provides functionality with DML Statements.
Can answer the same question as change tracking, as well as following ones;
What were the intermediate changes made to the data since the last synchronization?
Use change tables to record modified column data and metadata that is required to apply changes to target environment. The column structure of the change table mirrors the structure of the source.
Uses the transaction log as input for the information added to change tables.
Operates asynchronously and changes are available only after the DML statement has completed,
Provides table valued function to allow access to the data in the change tables.
Requires the database to be enabled by using sys.sp_cdc_enable_db.
Requires Sql server 2008 Enterprise, developer or evaluation edition.[2]

1- Comparing Change Tracking and Change data Capture Tracking

2- Source Microsoft SQL Server 2008 Database Development Self Paced taring Kit MCTS Exam 70-433
by Tobia Theronton
Ann Weber
Mike Hotek and Grand Masters.