Home > DeveloperSection > Articles > Change Data Capture (CDC) in SQL Server

Change Data Capture (CDC) in SQL Server


Database Database 
Ratings:
0 Comment(s)
 4116  View(s)
Rate this:

Change Data Capture (CDC) in SQL Server

Change Data Capture (CDC) is a powerful feature included in SQL Server 2008. Using change data capture, it is possible to determine which values have been added, changed, or deleted within a table. Setting up CDC is done by specifying tables to monitor.

Under the hood, CDC is written using the same repl logreader function that transactional replication uses. Don’t worry too much though. If you are like me, then when you hear the word replication, you start running for the hills. Well, CDC is not as much maintenance. The biggest thing you have to worry about with CDC, is that the disk that contains the log never gets full. When that does happen, and it should be rarely, then yes it is a pain. What you have to do in that case is flip the do not truncate bit for the log file to ‘no’. That aside, let’s get started.

First thing you want to do is enable change data capture. This is done at the database level.

USE AdventureWorks

GO

 

DECLARE @ReturnCode int

 

EXEC @ReturnCode = sys.sp_cdc_enable_db

 

SELECT @ReturnCode

 

SELECT

    name

    ,is_cdc_enabled

FROM sys.databases

 

Change Data Capture (CDC) in SQL Server

Even though we performed the above action, nothing will happen until we define the tables and columns that it should monitor. When defining a table to monitor, there are a few parameters that need to be passed:

                                                              CDC Parameters

Parameter

Description

@source_schema

The schema name of the table to monitor (ex: dbo)

@source_name

The name of the table to monitor

@role_name

A database or server role which is used to grant access to the data. If the specified role does not exist, it will be created. Note: DB Owner role can always access the data

@supports_net_changes

When enabled you are able to retrieve all changed values within a single row for a given time period (using LSN’s – Log Sequence Numbers). Otherwise, multiple rows are returned.

@captured_column_list

List of columns to capture. Must either include primary key, or specify a unique index using the @index_name parameter

@filegroup_name

Name of the filegroup to store the Change Data on

 

Let’s enable a table to run CDC:

EXEC sys.sp_cdc_enable_table

@source_schema          = 'Sales'       --mandatory

, @source_name          = 'Customer'    --mandatory

, @role_name            = 'cdc_manager' --mandatory

, @supports_net_changes = 0

, @captured_column_list = 'CustomerID,CustomerType,TerritoryID'

, @filegroup_name       = N'PRIMARY';

 

Once we run the above sample, we’ll notice there are two new jobs running under the SQL Server Agent.

Change Data Capture (CDC) in SQL Server

The first job, cdc.AdventureWorks_capture, is not much different than a replication job. Basically this job runs the repl log reader by executing it in an infinite loop. The next job, is the cleanup job which prunes the data by only retaining two days of data. (This is configurable however).

Now that we’ve enable CDC for the Sales.Customer table, let’s update some records to see how CDC works.

-- Update some records

UPDATE TOP (1) Sales.Customer

SET CustomerType = 'S'

WHERE CustomerType != 'S'

 

-- Query the capture table

SELECT *

FROM cdc.Sales_Customer_CT

 

Change Data Capture (CDC) in SQL Server

From the capture table output, we see two records. One record represents the original value (update mask = 3), while the second record represents the new changed value (update mask = 4).

Here are the update mask values:

 ·          1 = delete

 ·          2 = insert

 ·          3 = update (old values)

 ·          4 = update (new values)

In our next article we will go into depth in querying CDC and the internals.


Don't want to miss updates? Please click the below button!

Follow MindStick