Introduction to CDC in Sql Server 2008


Change data capture is a facility to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in a relational format. The change tables used by change data capture contain columns that is same as the column structure of a tracked table. Also it will store the information about what changes occurred to the data.

Change data capture is now available only on the Enterprise, Developer, and Evaluation editions

How it works ?

The change data capture procedure gets data from SQL Server transaction log. When an insert, update, or delete happened to tracked tables, those changes with description will be added to the log. The log serves as input to the change data capture process. It extract the log information and adds metadata about the changes. There are inbuilt functions to get the changes that appear in the change tables over a specified range, it will provide report with filter for change data. This filtered data can be used to feed any decision making application or datawarehouse systems.

This is how change capture is enabled for a database

— to enable CDC
if not exists(select name, is_cdc_enabled
              from master.sys.databases  where name = ‘CDCTrackedDB’
               and is_cdc_enabled = 1)
 exec sys.sp_cdc_enable_db_change_data_capture

After that we may need to enable CDC for individual tables

— enable CDC for dbo.Employee table
exec sys.sp_cdc_enable_table_change_data_capture
  @source_schema = ‘dbo’ , @source_name = ‘Employee’, @role_name = ‘cdcManager’;

Advertisements

Comment please...

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s