How to activate Change Data Capture (CDC) on newly added columns of underlying table without disabling CDC

Question

I have a requirement where I want to enable CDC on newly added columns of table, but I cannot disable CDC and enable it again. Is there any way I can achieve this?

I found a solution where I can copy old CDC table values into a temp table, then disable CDC and then enable CDC with new table schema. Later copying the temp table values into new CDC table and updating the LSN value.

Instead of the above I need a solution where I can include the new column into the CDC table while the CDC is enabled.

Answer 1

CDC supports two instances of capture tables. So, You can do following steps:

1. Add new column
2. Add new cdc-capture inctance
3. Move data from old table to the new one
4. Disable old cdc instance

This solution prevent you from stopping collecting changes and you won't lose data.

EXEC sp_cdc_enable_table
    @source_schema = N'common',
    @source_name = N'EntityTypes',
    @role_name = NULL,
    @filegroup_name = N'CDC',
    @capture_instance = 'common_EntityTypes'


ALTER TABLE common.EntityTypes
    ADD TestColumn int

EXEC sp_cdc_enable_table
    @source_schema = N'common',
    @source_name = N'EntityTypes',
    @role_name = NULL,
    @filegroup_name = N'CDC',
    @capture_instance = 'common_EntityTypes2'

INSERT INTO cdc.common_EntityTypes2_CT
(__$start_lsn, __$end_lsn,__$seqval,__$operation,__$update_mask,Id,Name)
SELECT
    __$start_lsn, 
    __$end_lsn,
    __$seqval,
    __$operation,
    __$update_mask,
    Id,
    Name
FROM cdc.common_EntityTypes_CT

EXEC sp_cdc_disable_table
    @source_schema = N'common',
    @source_name = N'EntityTypes',
    @capture_instance = 'common_EntityTypes'

Answer 2

The solution above works well when you have a single instance on the target table, but if you have two instances already, you might need to think a little further out and use some planning.

The question of loss of data is relevant but you might consider this question when designing the cdc process initially, one very pertinent item being, what is the end-point for the data? For example, if you are using a BI solution, perhaps using a warehouse, or a simple OLAP DB like a data-mart for a reporting solution, then you might consider introducing maintenance windows or utilizing a current maintenance window. The process is simple and not unlike the answer by Backs:

1. Transfer all current _CT data into the end-point.

2. Disable the current instance on the table (sp_cdc_disable_table)

3. Enable the instance again with the updated column list (sp_cdc_enable_table)

This allows for the changes to be effected even if two instances already target the same table with no loss of client data.

Note: the use of the column list parameter is not so much a requirement (if you wish to capture all columns) but it might be preferable for consistency when other developers might need to review the code. Albeit, it is just as easy to gain that data via the cdc.X system tables.