How to Enable CDC in SQL Server

This is a post on how to enable Change Data Capture (CDC) in SQL Server.

Enabling this on a database and table will allow the recording of activity when tables and rows have been modified. This is a feature available for us to configure within SQL Server and Azure SQL out of the box.

CDC uses the SQL Agent to record inserts, updates and deletes that happen on a table. Enabling this might be useful to help give a business insight on changes to Personal Identifiable Information (PII) over time for GDPR requirements. Or, another example could be real-time tracking of orders for pricing updates to a product.

For more examples and information on what CDC is, we can refer to MS Docs : What is change data capture (CDC)?

If you are considering enabling this feature, as always we should test and read through the software documentation to ensure all potential impacts and limitations have been reviewed. Some main considerations on the systems side with this one include the additional disk space your CDC tables will need, and performance depending on the number of tables/changes happening.

The demo below covers the following:
> Enabling Change Data Capture for a Database
> Enabling Change Data Capture for a Table

Enabling Change Data Capture for a Database

Before enabling CDC on a database we want to first check:
1. Is CDC already enabled.
Best to check this in larger or more unknown environments.
2. Is the database owner is set to something other than ‘sa’.
You’ll receive an error message if you don’t have it set to correctly.

We can check the above configured settings by querying sys.databases.

-- Check if Database is has CDC Enabled
SELECT is_cdc_enabled, *
FROM sys.databases;

-- (IF_REQUIRED) Change Database Owner to 'sa'
-- Error if <> 'sa' = "Could not update the metadata that indicates database demoBlog is enabled for Change Data Capture"
ALTER AUTHORIZATION ON DATABASE::[demoBlog] TO [sa]
GO

Enabling CDC for a SQL Server database is done executing the sys.sp_cdc_enable_db system stored procedure, as shown in this example:

USE [demoBlog];
GO
-- Enable CDC for Database ms sql
EXEC sys.sp_cdc_enable_db
GO
/* New system tables > 
	[cdc].[captured_columns];
	[cdc].[change_tables];
	[cdc].[ddl_history];
	[cdc].[index_columns];
	[cdc].[lsn_time_mapping];
	[dbo].[systranschemas];
*/

We can verify the database is enabled for CDC by checking sys.databases, and there’s new system tables for CDC as displayed in the screenshot above.

Enabling CDC for a Table

I’ve just enabled CDC on this demo database, so now it’s time to enable it on some tables.

We can check if CDC is already enabled by querying the sys.tables table and checking the is_tracked_by_cdc column.

To control the placement of change table data, we can create Filegroups ahead of enabling CDC on tables. Microsoft recommends we do this so that our change tables are separate from source tables.

I’m now creating a new File Group for CDC below:

-- Create File Group for CDC
ALTER DATABASE [demoBlog] ADD FILEGROUP [DemoBlog_CT];

ALTER DATABASE [demoBlog]
ADD FILE
(
    NAME = demoBlog_data1,
    FILENAME = 'D:\MSSQL_DATA\demoBlog_data1.ndf',
    SIZE = 500MB,
    FILEGROWTH = 50MB
) TO FILEGROUP [DemoBlog_CT];

SELECT * FROM sys.filegroups;
SELECT * FROM sys.database_files;

Use sys.sp_cdc_enable_table to enable CDC on a table, as per my next example below.

When a table is enabled for CDC, a record will be logged in the transaction log for each DML operation applied to the table from that point onwards.

USE [demoBlog];
GO 

-- Enable CDC on Table
EXEC sys.sp_cdc_enable_table
	@source_schema = N'dbo',
	@source_name   = N'posts',
	@role_name     = NULL,
	@filegroup_name = N'DemoBlog_CT'
GO
-- Job 'cdc.demoBlog_capture' started successfully.
-- Job 'cdc.demoBlog_cleanup' started successfully.

Upon executing the above SQL, new SQL Agent jobs are created for the CDC Collection.

One interesting note, “If both CDC and replication are enabled for a database, the log reader handles the population of the CDC change tables.”.

For an extended demo, here’s an example inserting a new of a row into our table, and then a querying the CDC metadata to watch it seed through:

I hope this post was useful and gave some good info! Feel free to add a comment below for any questions or suggested updates I can do here.