Thursday, August 26, 2010

SQL Change Tracking Vs CDC

I had posted about SQL change tracking couple of weeks ago. There is one more option available on SQL for capturing the data and can be used for auditing. That is called as CDC – Change Data Capture

Here is the comparison on these two features

Change Tracking (CT)

Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred

Change Data Capture (CDC)

Change Data Capture is asynchronous and uses the transaction log in a manner similar to replication. Past values of data are maintained and are made available in change tables by a capture process, managed by the SQL Agent, which regularly scans the T-Log. As with replication, this can prevent re-use of parts of the log. This Tracks when data has changed and includes the values as well. Entire table or subset of columns can be captured.

No comments:

Post a Comment