Handling DDL on Change Data Capture (CDC) enabled Table

SQL Server does not automatically track certain DDL changes in the Change Data Capture log after CDC was enabled on the table. This includes adding new columns to a table after CDC was enabled for the table. To make the CDC log reflects the DDL changes on the source table, it must disable CDC and re-enable CDC on the CDC enabled table. Here comes an example:

USE master

GO

-- Create a database

CREATE DATABASE DbTestCDC;

GO

USE DbTestCDC;

GO

-- Create a table

CREATE TABLE TableTestCDC

(

    ID int NOT NULL PRIMARY KEY,

    FirstName varchar(30),

    LastName varchar(30),

    Email varchar(50)

);

-- Enable CDC for the current database

EXEC sys.sp_cdc_enable_db;

GO

SELECT name, is_cdc_enabled FROM sys.databases;

GO

-- Enable CDC for the specified table in the current database

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

@role_name     = NULL,

@supports_net_changes = 1

GO

-- Check CDC enabled on that table

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';

-- Check CDC retention (default 4320 minutes = 3 days)

SELECT DB_NAME(database_id), * FROM msdb.dbo.cdc_jobs;

-- Testing

INSERT INTO TableTestCDC Values (1, 'Jorge', 'Ramos', 'ramos@yahoo.com');

INSERT INTO TableTestCDC Values (2, 'Andrew', 'Strudwick', 'andrew@yahoo.com');

INSERT INTO TableTestCDC Values (3, 'Thomas', 'Tuchel', 'tuchel@gmail.com');

DELETE FROM TableTestCDC WHERE ID = 1;

UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ' WHERE ID = 2;

DELETE FROM TableTestCDC WHERE ID = 3;

SELECT * FROM TableTestCDC;

GO

WAITFOR DELAY '00:00:05';

GO

-- Query CDC data

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');

SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,

CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,

*

FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');

GO

-- ALTER TABLE on the source table, e.g. add a new column

ALTER TABLE dbo.TableTestCDC ADD NewCol varchar(50) NULL;

GO

-- Disable CDC on that table, and then re-enable CDC to reflect the table DDL changes

EXECUTE sys.sp_cdc_disable_table   

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

    @capture_instance = N'all';

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'TableTestCDC',

@role_name     = NULL,

@supports_net_changes = 1

GO

EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'TableTestCDC';

-- Testing (with New column)

INSERT INTO TableTestCDC Values (4, 'Jorge', 'Ramos', 'ramos@yahoo.com', 'NewCol 1');

INSERT INTO TableTestCDC Values (5, 'Andrew', 'Strudwick', 'andrew@yahoo.com', 'NewCol 2');

INSERT INTO TableTestCDC Values (6, 'Thomas', 'Tuchel', 'tuchel@gmail.com', 'NewCol 3');

DELETE FROM TableTestCDC WHERE ID = 4;

UPDATE TableTestCDC SET LastName = 'ABC', FirstName = 'XYZ', NewCol = 'New Col X' WHERE ID = 5;

DELETE FROM TableTestCDC WHERE ID = 6;

SELECT * FROM TableTestCDC;

GO

-- Query CDC data

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_TableTestCDC');

SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS changeTime,

CASE [__$operation] WHEN 1 THEN 'delete' WHEN 2 THEN 'insert' WHEN 3 THEN 'before update' WHEN 4 THEN 'after update' END AS operation,

*

FROM cdc.fn_cdc_get_all_changes_dbo_TableTestCDC(@from_lsn, @to_lsn, N'all update old');

GO