Home > Sql Server > Enabling Change Data Capture (CDC) on newly added columns on underlying table

Enabling Change Data Capture (CDC) on newly added columns on underlying table


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

Step 1: Assumption is that CDC is turned ON against table OrigTable and changes are already being tracked in dbo_OrigTable_CT.

Step 2: Copy the existing change tracking into a temp table

SELECT * INTO cdc.dbo_OrigTable_Temp FROM cdc.dbo_OrigTable_CT
Step 3: Suppose a new column NewColumn is added to OrigTable (the assumption is that the new column has been added such that the data in the dbo_OrigTable_CT still exists). Care must be taken when updating a table schema such that the actual table is not dropped and re-created as this will cause all entries in the CDC table to be wiped away. If this is unavoidable, take a BACKUP of the CDC tables. As a safety precaution, it is ALWAYS advisable to take a BACKUP of all CDC tables before any deployment. Hence Step 2 is performed before updating the OrigTable schema

Step 4: Add the new column to the temp table so that we do not have to manually add all columns when we copy back

ALTER TABLE cdc.dbo_OrigTable_Temp ADD <NewColumn> <datatype>
Step 5: Disable CDC on the source table, this will drop the associated CDC table
 EXEC sys.sp_cdc_disable_table@source_schema=‘dbo’,

@source_name=‘OrigTable’,

@capture_instance=‘dbo_OrigTable’
Step 6: Turn ON CDC with the new column
EXEC sys.sp_cdc_enable_table @source_schema=‘dbo’, @source_name=‘OrigTable’, @role_name=NULL,@captured_column_list=‘[ID],[FirstName],<NewColumn>’
Step 7: Insert values from the temp table back into the new CDC Table
INSERT INTO cdc.dbo_OrigTable_CTSELECT * FROM cdc.dbo_OrigTable_Temp
Step 8: 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.
UPDATE cdc.change_tables
 SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_OrigTable_Temp)
WHERE capture_instance = ‘dbo_OrigTable’;
Step 9: Drop the temp table
DROP TABLE cdc.dbo_OrigTable_Temp
Step 10: Validate if CDC is working properly. After some DML statements,
check to see if we are getting both old and new CDC records
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_OrigTable(sys.fn_cdc_get_min_lsn(‘dbo_OrigTable’), sys.fn_cdc_get_max_lsn(), ‘all update old’) ch

You can learn more about CDC at:

http://msdn.microsoft.com/en-us/library/bb522649.aspx

Have fun!

Advertisements
Categories: Sql Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: