Microsoft SQL Server 2012 Bible

Enabling CDC

CDC is enabled at the database level first and then for every table that needs to be tracked. Because CDC reads from the transaction log, you might think that CDC requires the database to be set to full recovery model so that the transaction log is kept. However, SQL Server doesn't flush the log until after the transactions have been read by CDC, so CDC can work with any recovery model, even simple.

Also, and this is important, CDC uses SQL Agent jobs to capture and clean up the data, so SQL Agent must be running or data will not be captured.

Enabling the Database

To enable the database, execute the sys.sp_cdc_enable_db system stored procedure in the current database. It has no parameters:

EXEC sys.sp_cdc_enable_db

You can use the is_cdc_enabled column in sys.databases to determine which databases have CDC enabled on them:

SELECT *
FROM sys.databases
WHERE is_cdc_enabled = 1

This procedure creates six system tables in the current database:

• cdc.captured_columns: Stores metadata for tracked table's columns
• cdc.change_tables: Stores metadata for tracked tables
• cdc.ddl_history: Tracks DDL activity
• cdc.index_columns: Tracks table indexes
• cdc.lsn_time_mapping: Used for calculating clean-up time
• dbo.systranschemas: Tracks schema changes

These are listed in Object Explorer under the Database → Tables → System tables node.

Enabling Tables

After the database has been prepared for CDC, tables may be set up for CDC using the sys.sp_cdc_enable_table stored procedure, which ...