How Can I Add New Columns to a CDC-Enabled Connection?
Question
How can I ensure that a new column syncs in a connection with with change data capture (CDC) enabled?
Environment
- Connector: SQL Server
- Incremental sync method: CDC
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, see the Microsoft documentation on handling changes to a source table.
To sync a new column, do the following:
In Fivetran, go to your SQL Server connection page.
In the top right, pause the connection by setting the Enabled toggle to OFF.
In your source database, add a new column.
Create a new CDC instance for the table that contains that new column. Replace placeholders, such as
<schema>, with the actual values:i. Get the name of the
capture instanceof the table storing the added column:EXEC sys.sp_cdc_help_change_data_capture @source_schema = '<schema>', @source_name = '<table>';ii. Delete the current CDC instance:
EXEC sys.sp_cdc_disable_table @source_schema = [<schema>], @source_name = [<table>], @capture_instance = [<capture_instance>];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;When
@supports_net_changesis set to1, 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.Verify that only one CDC instance exists for the table. The result should contain a single row for the new capture instance:
EXEC sys.sp_cdc_help_change_data_capture @source_schema = '<schema>', @source_name = '<table>';In Fivetran, resume your connection by setting the Enabled toggle to ON.
Re-sync the affected table. For instructions, see How to Trigger Historical Re-Syncs for Fivetran Connections and Tables.