Skip to content

SQL Server 2008: Change Data Capture

September 9, 2011

SQL Server 2008 introduces a compelling new feature to mitigate this problem. Change data capture uses an asynchronous log reader mechanism to propagate changes at the table grain to capture instance tables in a transactionally consistent manner, through windowed API functions:

Asynchronous Log Reader: Whenever a change is made to the database, the engine first writes an entry to the database log before writing it to the database store. This is an existing SQL Server feature that change data capture uses to good effect. A change data capture log reader job can be scheduled to run at regular intervals or during server idle time. The log reader trawls the logs and copies the changes into a configured set of tables along with metadata to help identify when and how the original changes were made. Because the reader operates asynchronously, the cost on the source system can be amortized over a longer period (or moved to non-operational hours such as 3 A.M.). Also, the mechanism requires no changes to the schema of the source system (though it needs to be hosted on SQL Server 2008).

Table grain: Change data capture functionality can be switched on or off at the table level. This means the solution can be implemented incrementally—change data capture is not an all-or-nothing proposition. When a table is enabled for change data capture, the database automatically generates a capture instance table (also called a shadow table) that contains the data changes along with extra metadata to audit the operations. If the shape of the source table changes (for instance a column is added or deleted) the technology can continue to deliver a stable shape in order to not break any downstream processes while they are updated.

Transactionally-consistent: Since the change data capture feature relies on the database log, it is fully aware of transactions and thus can provide related changes across multiple tables correctly.

Windowed API functions: A host of functions are available to query and manage the capture instance tables. For instance, the APIs can be used to select all the data changes from the last batch window (such as 24 hours), with each row tagged with its operation (insert, update, or delete) as well as a mask identifying which specific columns changed.

Reference to Book online of Microsoft

Advertisements

From → DBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: