What Is Change Data Capture In SQL Server?

I was working on a project where we had to record the activities on a database when tables or rows get modified. We thought about Microsoft's feature called the Change Data Capture in short called as CDC in SSMS.

What is Change Data Capture (CDC)?

Change Data Capture (CDC), first introduced in SQL Server 2008, is a valuable feature that tracks and captures changes made to SQL Server database tables without requiring additional programming efforts. Initially, CDC could only be enabled on databases in the SQL Server Enterprise edition, but starting with SQL Server 2016, this restriction was removed.

CDC monitors INSERT, UPDATE, and DELETE operations on database tables, recording detailed information about these changes in mirrored tables. These mirrored tables have the same column structure as the source tables, with additional columns to describe the changes. For each INSERT operation, CDC writes a record showing the inserted values. For each DELETE operation, it writes a record showing the deleted data. For each UPDATE operation, CDC writes two records: one showing the data before the change and another showing the data after the change.

Enabling CDC On The Database

Before enabling the CDC for any table, we should enable the CDC at the database level by using sp_cdc_enable_db system stored procedure. Any DBA working on this enable/disable should have a sys admin permission.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_enable_db;
GO

Now we can check if the CDC has been enabled using sys.databases DMV.

Enabling CDC On The Table

After enabling CDC on the database, we can enable the CDC at the individual table level by using sp_cdc_enable_table system stored procedure. You can enable with the below command.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'dtproperties',
@role_name = NULL; -- or specify a role for access control
GO

Similarly, to make sure that the CDC has been enabled at the table level, use sys.tables DMV.

Disabling CDC

To disable CDC, the command are similar to how we used for enabling CDC. First we will be disabling the CDC at the table level using below command.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'dtproperties',
@capture_instance = N'dbo_dtproperties'; -- This is optional if you have a single capture instance
GO

Then disable the CDC at the database level.

USE SQLArena_CDC;
GO
EXEC sys.sp_cdc_disable_db;
GO

Note: You can disable the CDC directly at the database level if you want to cut down a step.

Bottomline

CDC is a powerful feature for tracking changes to your data, but it’s essential to manage it properly to ensure your system's performance and integrity.