CDC table not working after adding new columns to the source table

Question

Two new columns were added to our source table while CDC was still enabled on the table. I need the new columns to appear in the CDC table but do not know what procedure should be followed to do this? I have already disabled CDC on the table, disabled CDC on the DB, added the new columns to the cdc.captured_columns table, and enabled CDC. But now I am getting no data in the CDC table!

Is there some other CDC table that must be updated after columns are added to the source table? These are all the CDC tables under the System Tables folder:

• cdc.captured_columns <----- where I added the new columns
• cdc.change_tables
• cdc.dbo_myTable_CT <------ table where change data was being captured
• cdc.ddl_history
• cdc.index_columns
• cdc.lsn_time_mapping
• dbo.systranschemas

Answer 1

I recommend reading Tracking Changes in Your Enterprise Database. Is very detailed and deep. Among other extremly useful bits of info, there is such as:

DDL changes are unrestricted while change data capture is enabled. However, they may have some effect on the change data collected if columns are added or dropped. If a tracked column is dropped, all further entries in the capture instance will have NULL for that column. If a column is added, it will be ignored by the capture instance. In other words, the shape of the capture instance is set when it is created.

If column changes are required, it is possible to create another capture instance for a table (to a maximum of two capture instances per table) and allow consumers of the change data to migrate to the new table schema.

This is a very sensible and well thought design that considers schema drift (not all participants can have the schema updated simultaneously in a real online deployment). Having a multi-staged approach (deploy DDL, capture new CDC, upgrade subscribers, drop old CDC capture) is the only feasible approach and you should follow suit.