Disabling Change Tracking and enabling Change Data Capture in SQL Server Lakeflow

[Upendra_Dwivedi]

Hi All,

I want to know my lakeflow connect pipeline has stopped running because Change Tracking is enabled and table doesn't have a primary key and i want to switch to CDC now. Will it impact my pipeline. What are the steps i need to follow to on ingestion gateway and pipeline side.

[balajij8]

You can trigger a full refresh on this table to establish a clean CDC baseline. Pause the ingestion pipeline (and the gateway if running continuous Standard CDC) to prevent state conflicts while updating the source. Next, on the SQL Server instance, ensure CDC is enabled at the database level, then enable CDC on the target table. Because the table has no primary key, make sure the table CDC setup has net change support disabled, as supporting net changes requires a primary key or unique index. Once CDC is active on the table, explicitly disable Change Tracking on that table so Lake flow stops attempting to use it.

Before restarting the pipeline, verify through SQL Server system tables that the table reflects CDC as active and Change Tracking as disabled. Finally, re/start the gateway and pipeline, navigate to the table details within the Lake flow UI, and trigger a Full Refresh for the affected table so it can snapshot the current baseline and begin processing continuous CDC transactions seamlessly. Validate the changes in dev first. More details here

[Upendra_Dwivedi]

Hi Balaji,
Thankyou for responding to my question.

I tried implementing your solution and disabled CT only as CDC and CT both were enabled at database level and table level. Then i restarted gateway and expected it to not throw the same error i was getting previously which is:

Can you please tell why this is happening it should now pick CDC but still it is considering CT even if it is disabled. Please let me know if i misunderstood the error.

[balajij8]

Hi Upendra,

When both Change Tracking and Change Data Capture are enabled the connector generally prioritizes Change Tracking. Since CT requires the source table to have a primary key and table did not have one, it causes the pipeline to fail. Even after disabling CT, the same error may continue because the ingestion gateway caches the source database configuration and may take some time to detect changes. The gateway can take up to approximately 6 hours to refresh this information automatically. In addition, the pipeline may have existing streaming state or checkpoints that were established when CT was active.

On the SQL Server side, you can verify that CT has been disabled at both the database and table levels. You can also confirm that CDC is enabled at both the database and table levels. Once this is confirmed, restart the ingestion gateway. However, as the gateway may take time to pick up the updated configuration, the restart alone may not be sufficient to reinitialize the pipeline with CDC.

You need to perform a full refresh on the affected table. It forces the pipeline to re-snapshot the table and re-establish the change-tracking mechanism using the updated source configuration. You can trigger the full refresh from the pipeline UI by selecting Full Refresh for the specific table or use the Pipelines API with the parameters. This full refresh will take longer because the table needs to be re-snapshotted, but once it completes, incremental processing should resume using CDC.

You can try full refresh after switching from CT to CDC in dev. Confirm CT is disabled and CDC is enabled on SQL Server, restart the ingestion gateway, perform a full refresh for the affected table, monitor the pipeline update until it completes and then verify that incremental ingestion resumes using CDC. Switching to CDC can have a higher performance impact on the source database than CT, but it is necessary for a table without a primary key.