Mohan Mohan - 2 months ago 17
SQL Question

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

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

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'