Skip to main content

Enable change tracking in SQL Server

Preview

LakeFlow Connect is in gated Public Preview. To participate in the preview, contact your Databricks account team.

This article describes how to enable change tracking in SQL Server. Either change tracking or CDC is required for ingestion into Databricks. 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 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.

SQL
ALTER DATABASE <database-name> SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 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 you want to enable change tracking on.

SQL
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:

SQL
GRANT VIEW CHANGE TRACKING ON SCHEMA::<schema-name> TO <cdc-username>;

To grant table-level permissions, run the following:

SQL
GRANT VIEW CHANGE TRACKING ON OBJECT::<schema-name>.<table-name> TO <cdc-username>;

Set up DDL capture and schema evolution​

The SQL Server connector can track the data definition language (DDL) on ingested database objects and apply relevant table schema changes to the destination tables or add new tables in case of full schema replication.

To perform DDL capture, additional database object setup is required (for example, internal tables, stored procedures, and triggers). The T-SQL script provided in this section drops any pre-existing DDL support objects and creates the DDL support objects required to capture DDL changes happening on the database.

To set up DDL capture and schema evolution, do the following:

  1. Download the following T-SQL script:

    ddl_support_objects.sql

  2. Modify the script to set the mode value:

    • BOTH: Initializes both CT and CDC objects (default)
    • CT: Initializes CT objects
    • CDC: Initializes CDC objects
    • NONE: Deletes all pre-existing CT and CDC objects

  3. Run the script on each database that contains tables you want to replicate.

Grant privileges required for replication​

Grant the following permissions to the database user:

SQL
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.replicate_io_audit_ddl_1 TO <database-user>;
GRANT VIEW DEFINITION ON DATABASE::<database-name> TO <database-user>;
Last updated on