Using Change Data Capture (CDC) in SQL Server 2008

Problem As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Change Data Capture. Can you give us a detailed explanation of how we go about using this one? Solution Change Data Capture

problem
as we are looking through the new features in sql server 2008 we found a potentially interesting one called change data capture.  can you give us a detailed explanation of how we go about using this one?

Solution
Change Data Capture is a new feature in SQL Server 2008 that records insert, update and delete activity in SQL Server tables.  A good example of how this feature can be used is in performing periodic updates to a data warehouse.  The requirement for the extract, transform, and load (ETL) process is to update the data warehouse with any data that has changed in the source systems since the last time the ETL process was run.  Before CDC we might simply query a last updated DATETIME column in our source system tables to determine what rows have changed.  While this is simple and pretty effective, it is of no use in determining any rows that were physically deleted.  In addition we can't determine what was changed when; we can only access the current state of a row that has changed.  CDC provides a configurable solution that addresses these requirements and more.

In this tip we are going to gain an understanding of CDC by walking through a simple code sample to demonstrate how to:

Before we start reviewing the sample T-SQL code, let's discuss how CDC works at a high level.  After performing some setup and configuration steps (which we will cover below), CDC will begin scanning the database transaction log for changes to certain tables that you specify, and will insert these changes into change tables.  These change tables are created during the setup and configuration process.  The setup and configuration process will also create table-valued functions which can be used to query for the changes.  You use the table-valued functions in lieu of querying the underlying change tables directly.  Based on this high level description, let's proceed to the demo.

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008.  Some of the function names and stored procedure names have changed from the earlier CTPs.

Setup and Configuration

CDC is a feature that must be enabled at the database level; it is disabled by default.  To enable CDC you must be a member of the sysadmin fixed server role.  You can enable CDC on any user database; you cannot enable it on system databases.  Execute the following T-SQL script in the database of your choice to enable CDC:

declare @rc int exec @rc = sys.sp_cdc_enable_db select @rc -- new column added to sys.databases: is_cdc_enabled select name, is_cdc_enabled from sys.databases