SQL Change Data Capture (CDC) table not working after adding new columns to the source table and Using CDC with a frequently changing schema

Introduction

Added two new columns were to our source table while CDC was still enabled on the table. We need the new columns to appear in the CDC table but it will not start captureing.

Background

We are looking into enabling Sql Server Change Data Capture (CDC) for a new subsystem we are building.

We are being pushed for having a complete history traceability, and CDC would nicely solve this requirement with minimum effort on our scinario.

Did a small test where we created a table, enabled CDC for that table, and then added a new column to the table. Changes to the new column is not registered in the CDC table.

Is there a mechanism to update the CDC table to the new schema, and are there any best practices to how you deal with captured data when migrating the database schema?

Using the below code

I have recently started looking at CDC to do data auditing into application. I'm not an expert or DBA , but I think I did some research and have some answers for those problems.

CDC will help us achieve our goal of a completely traceable history, but I don't think it will get you all of the way that you look for audit. When CDC is turned ON against a table and the underlying table schema changes, such as adding/removing a column, changing datatype etc., these changes are not captured in the CDC table. Below are the steps on how to capture these new changes.

In summary, we have to copy the data from the CDC table to a temp table and after re-enabling CDC on the updated table, assign the correct __$start_lsn in the cdc.change_tables

/* Enter restricted mode so we don't lose data changes during this process*/

DECLARE @sql VARCHAR(8000)
DECLARE @tblName VARCHAR(100)
DECLARE @colname VARCHAR(100)
DECLARE @dbType VARCHAR(100)
DECLARE @cdcInst VARCHAR(100)
DECLARE @cdctblName VARCHAR(100)
DECLARE @cdcTemptblName VARCHAR(100)

/* Provide Table which you add new column from Database*/
SET @tblName = 'customer'
SET @cdcInst = 'dbo_' + @tblName
SET @cdctblName = 'cdc.dbo_' + @tblName + '_CT'
SET @cdcTemptblName = 'cdc.dbo_' + @tblName + '_Temp' 

/*Set Column name and data type added into you Table Schema*/
SET @colname = 'OfficePhoneNum'
SET @dbType = 'varchar(50)'


	/*Take backup of CDC table by copying data from the CDC table to a temp table*/
	SET @sql = 'SELECT * INTO ' + @cdcTemptblName + ' FROM  ' + @cdctblName
	EXEC(@sql) 

	/*Add new column to actual table*/
    SET @sql = 'ALTER TABLE dbo.'+ @tblName +' ADD '+ @colname + ' '+ @dbType +' NULL '    
    EXEC(@sql)        

	/*Add new column to temp CDC table created*/
    SET @sql = 'ALTER TABLE '+ @cdcTemptblName +' ADD '+ @colname + ' '+ @dbType +' NULL '    
    EXEC(@sql)        

	/*Disable CDC on the source table, this will drop the associated cdc table*/
	exec sys.sp_cdc_disable_table 
	@source_schema='dbo',
	@source_name= @tblName, 
	@capture_instance= @cdcInst

	WAITFOR DELAY '00:00:10'

	/*Enable CDC for the table which recreates the CDC table*/
	EXEC sys.sp_cdc_enable_table
	@source_schema = 'dbo',
	@source_name   = @tblName,
	@role_name     = NULL,
	@capture_instance = @cdcInst,
	@filegroup_name = 'CDC_TestAudit',
	@supports_net_changes = 1

	WAITFOR DELAY '00:00:10'

	/*Insert values from the temp CDC table back into the new CDC Table*/

	SET @sql = 'INSERT INTO ' + @cdctblName + ' SELECT * FROM ' + @cdcTemptblName
	EXEC(@sql)  

	/*Update the start lsn in the change_tables to map to first entry in the CDC table.
	This table is queried by most of the system-defined procs/functions, hence this needs
	to be updated with the accurate start_lsn ELSE the queries will return results POST
	updates i.e. all the old data will exist in the CDC table but will not be retrieved
	as the start_lsn does not match.*/

	SET @sql = 'UPDATE cdc.change_tables
    SET start_lsn = (SELECT MIN(__$start_lsn) FROM ' +@cdcTemptblName + ') 
			WHERE capture_instance = ''' + @cdcInst+'''';
	EXEC (@sql)

	/* Drop the temp table */  
	SET @sql = 'DROP TABLE ' + @cdcTemptblName
	EXEC (@sql)