articles

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

Change Data Capture (CDC) in SQL Server

AVADHESH PATEL 7106 15-Sep-2012

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 @indename 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.



Updated 31-Mar-2020
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By