Set up DDL capture and schema evolution

Important

This page contains legacy content. Databricks recommends following the steps in Prepare SQL Server for ingestion using the utility objects script instead.

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

DDL capture requires the additional setup of database objects, including internal tables, stored procedures, and triggers. The ddl_support_objects.sql Transact-SQL (T-SQL) script drops any pre-existing DDL support objects and creates the DDL support objects required to capture DDL changes on the database.

1. Download the ddl_support_objects.sql script.

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. (Recommended) Optionally, modify the script to set the replicationUser variable to your SQL Server database user.

   If replicationUser is defined, the script grants all privileges that are required to work with the DDL support objects to the user. Otherwise, you must grant each privilege manually.

4. Run the script on each database that you want to ingest.

   Important

   Don't run the script on the master database.

   If you run the script in a third-party tool, select the entire script before you run it.

Next step

Ingest data from SQL Server