Enable change tracking in Microsoft SQL Server
This page contains legacy content. Databricks recommends following the steps in Prepare SQL Server for ingestion using the utility objects script instead.
Learn how to enable change tracking in Microsoft SQL Server. You must enable either change tracking or change data capture (CDC) to use the SQL Server connector. Databricks recommends using change tracking for any table that has a primary key to minimize the load on the source database. If change tracking and CDC are both enabled, the connector uses change tracking. For guidance on which option to choose, see Change tracking vs. change data capture.
Enable change tracking on a database
Run the following, replacing <database-name> with the name of the database that you want to enable change tracking on. Set CHANGE_RETENTION to the maximum time that the gateway is likely to be down. After this amount of time has passed, a full refresh is required to resume the gateway.
ALTER DATABASE <database-name> SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON)
Enable change tracking on a table
Run the following, replacing <schema-name>.<table-name> with the name of the schema and table that you want to enable change tracking on.
ALTER TABLE <schema-name>.<table-name> ENABLE CHANGE_TRACKING
Grant VIEW CHANGE TRACKING on ingested tables
In addition to the privileges described in the source setup, the database user needs the VIEW CHANGE TRACKING privilege on the ingested tables or on a schema that contains tables being tracked.
To grant schema-level permissions, run the following:
GRANT VIEW CHANGE TRACKING ON SCHEMA::<schema-name> TO <database-user>;
To grant table-level permissions, run the following:
GRANT VIEW CHANGE TRACKING ON OBJECT::<schema-name>.<table-name> TO <database-user>;