How Can I Modify Columns in a SQL Server Table With CT or CDC Enabled?

Question

How can I modify columns in a SQL Server table that has change tracking (CT) or change data capture (CDC) enabled without interrupting syncs or losing data?

Environment

Connector: SQL Server

Answer

To make data definition language (DDL) changes on a SQL Server table that we're already syncing with CT or CDC, follow the steps below:

1. Disable CT or CDC on the table using the appropriate command or procedure before you apply any changes. See the following example commands:

   • To disable CT:

     ALTER TABLE [schema].[table_name] DISABLE CHANGE_TRACKING;
     

   • To disable CDC:

     EXEC sys.sp_cdc_disable_table  
     @source_schema = N'schema',  
     @source_name = N'table_name',  
     @capture_instance = N'schema_table_name';  -- Optional if default
     

   For more information, see the applicable Microsoft resource below:

   • Disable Change Tracking for a Table
   • Disable Change Data Capture for a Table

2. While CT or CDC is disabled, modify the applicable columns in SQL Server.

3. Re-enable CT or CDC:

   • CT: Follow the steps outlined in Microsoft's Enable Change Tracking for a Table documentation.
   • CDC: Disable the existing capture instance, then create a new capture instance that includes every column you want Fivetran to sync. For more information, see Microsoft's Enable Change Data Capture for a Table documentation.

4. (Optional) Re-sync the table in Fivetran. During the connection's next sync, we will automatically detect that a previous tracking snapshot is invalid and perform a table re-sync. However, you can re-sync it immediately, by following the steps below:

   i. In Fivetran, go to your SQL Server connection page.

   ii. Select the Schema tab.

   iii. Find the relevant table, then click Re-sync.

   iv. In the confirmation pop-up window, click Start re-sync.