Integrated Change Tracking, Change Data Capture and Data Synchronization – SQL Server 2008

What MSDN Says about it

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: Change Data Capture and Change Tracking.

Change Data Capture (Keeps intermediate history of changes)

Provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system. The changes that were made to user tables are captured in corresponding change tables. These change tables provide an historical view of the changes over time. Is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

The changes are tracked in a change table that stores the change alongside metadata that is relevant to the recorded change. In the case of inserts and deletes, the change table stores one row that records the row that was inserted or deleted. In the case of an update, the change table stores two rows: one for the before image of the data and one for the after image. You can use CDC either to return all changes to a row, or just to return the net changes. For example, if a LastName column in a row is changed twice, you can either view both changes as they occurred or just the final content of the row.

When you enable CDC for a table, new tables and functions are added to the database to enable you to review the changes that have been made to the data. In addition, two SQL Server Agent jobs are created. One job is used to populate the database change tables and the other is responsible for cleaning up the change tables. These jobs are pivotal to the CDC process, so SQL Server Agent must be /running for CDC to be enabled.

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Change tracking for MSDN overview click here

In SQL Server 2008 enables applications to obtain only changes that have been made to the user tables, along with the information about those changes. With change tracking integrated into SQL Server, complicated custom change tracking solutions no longer have to be developed.

Change tracking is an important building block for applications that synchronize and replicate data in scenarios in which end-to-end replication solutions do not work and a custom solution is required. For example, a scenario that requires synchronizing data with data stores that are not SQL Server databases or in which the store schemas are very different.

Applications can use change tracking to answer the following questions about the changes that have been made to a user table: 1. What rows have changed for a user table? Only the fact that a row has changed is required, not how many times the row has changed or the values of any intermediate changes. 2. Has a row changed? The fact that a row has changed and information about the change must be available and recorded at the time that the change was made in the same transaction. If an application requires information about all the changes that were made and the intermediate values of the changed data, using change data capture, instead of change tracking, might be appropriate. For more information, see Comparing Change Data Capture and Change Tracking and Change Data Capture.

From SQL Team Blog

The idea behind this feature is that it enables SQL Server to track data changes to your database rather than relying on you to create a change tracking technique. With SQL Server 2008, you can now simply turn on an option to enable change tracking

You tell SQL Server which tables you want it to monitor. From that point SQL Server will start monitoring these changes and store them in a separate change tracking table which you have access to. If you want to get the changes since a certain point in time you simply join the ChangeTable to your base table and presto you have all of the inserts, updates and deletes.

Advertisements