How Can I Add New Columns to a CDC-Enabled Connector?

Question

How can I add a new column while I have change data capture (CDC) enabled on my connector?

Environment

SQL Server connector that uses CDC as its incremental sync method.

Answer

SQL Server CDC instances do not automatically add new columns, so you must create a new CDC instance to replace your existing instance. For more information, read this excerpt from Microsoft's CDC documentation:

To accommodate a fixed column structure change table, the capture process responsible for populating the change table will ignore any new columns that are not identified for capture when the source table was enabled for change data capture. If a tracked column is dropped, null values will be supplied for the column in the subsequent change entries. However, if an existing column undergoes a change in its data type, the change is propagated to the change table to ensure that the capture mechanism does not introduce data loss to tracked columns.

It is possible to create a second capture instance for the table that reflects the new column structure... When the transition is effected, the obsolete capture instance can be removed.

To sync a new column, do the following:

1. In your Fivetran dashboard, pause the connector.

2. In your source database, add a new column.

3. Create a new CDC instance for the table that contains that new column.

   i. Get the name of the capture instance of the table storing the added column:

   EXEC sys.sp_cdc_help_change_data_capture @source_schema = '<schema>', @source_name = '<table>';
   

   where '<schema>' and '<table>' are the schema and table that contain the added column.

   ii. Disable the current CDC instance:

   EXEC sys.sp_cdc_disable_table @source_schema = [<schema>], @source_name   = [<table>], @capture_instance = [<capture_instance>];
   

   where [<schema>] and [<table>] are the schema and table that contain the added column, and [<capture_instance>] is the capture instance name for the table storing the added column.

   iii. Create a new CDC instance:

   EXEC sys.sp_cdc_enable_table @source_schema = [<schema>], @source_name   = [<table>], @role_name     = [<username>], @supports_net_changes = 0;
   

   where [<schema>] and [<table>] are the schema and table that contain the added column, and [<username>] is the Fivetran user name.

   NOTE: When @supports_net_changes is set to 1, an additional non-clustered index is created on the change table. Because this index needs to be maintained, enabling net changes can degrade CDC performance.

4. Delete the old CDC instance. Ensure that there is only one CDC instance for the table.

5. In your Fivetran dashboard, unpause your connector and re-sync the table.