Prepare SQL Server for ingestion using the utility objects script

Complete the SQL Server database setup tasks to ingest into Databricks using Lakeflow Connect.

Requirements

The user running the script must be a member of the db_owner role. This role is only required for running the setup script, not for the ingestion user.

To add a user to the db_owner role, use one of the following methods:

Modern SQL Server (2012+): Use ALTER ROLE

SQL
USE [your_database];
ALTER ROLE db_owner ADD MEMBER [your_setup_user];
GO

Legacy SQL Server or restricted environments: Use sp_addrolemember

SQL
USE [your_database];
EXEC sp_addrolemember 'db_owner', 'your_setup_user';
GO

For CT setup: Change tracking must be available on the platform.
For CDC setup: Change data capture must be available on the platform.

Step 1: Install or upgrade utility objects

This step installs the utility stored procedures and functions needed for SQL Server setup. For details about what gets installed, see SQL Server utility objects script reference.

The same script handles both a first-time installation and an upgrade from a previous version. Follow the numbered steps below. Steps marked (Upgrade only) apply only if a previous version of the utility objects is already installed. Skip them for a first-time installation.

important

(Upgrade only) Stop the gateway (gateway-based pipeline) or pause the pipeline (integrated CDC) before running the script. Pausing avoids a schema change failing fast and needing a retry during the brief cutover.

Download the script:

Download utility_script.sql
Open the script in SQL Server Management Studio (SSMS), Azure Data Studio, or your preferred SQL client.
Connect to your SQL Server instance as a user with the db_owner role.
Make sure that you are connected to the target database.
(Upgrade only) Pause ingestion: stop the gateway (gateway-based pipeline) or pause the pipeline (integrated CDC).
Run the script. On an upgrade, the script recreates the utility functions and removes only legacy replicant-prefixed objects. It leaves your existing DDL support objects in place, so the current DDL trigger keeps capturing schema changes until you re-run the setup procedures in the next step.
(Upgrade only) Re-run the setup procedure for each capture method you use: lakeflowSetupChangeTracking if you use change tracking, and lakeflowSetupChangeDataCapture if you use CDC. This carries your objects forward to the new version and restores the ingestion user's permissions. Pass @User only. You do not need @Tables, because change tracking and CDC stay enabled on your tables through an upgrade.
SQL
```
-- If you use change tracking:
EXEC dbo.lakeflowSetupChangeTracking
    @User = 'your_ingestion_user';  -- omit @User if you did not pass it originally

-- If you use CDC:
EXEC dbo.lakeflowSetupChangeDataCapture
    @User = 'your_ingestion_user';  -- omit @User if you did not pass it originally
```
Pass any non-default options again so the upgrade preserves your configuration:
- Change tracking: By default the DDL audit table and trigger are not created, so most upgrades pass nothing extra here. If your install uses DDL schema-change capture (it has a lakeflowDdlAudit table), pass @CreateDdlSupportingObjects = 1 on lakeflowSetupChangeTracking to move those objects to the new version. Installs from before this option existed created them automatically, so include the flag if you rely on DDL capture even if you never set it.
- CDC: If you use @AllowDisablePreExistingCaptureInstances = 1 on lakeflowSetupChangeDataCapture, include it again.

Verify installation:

SQL
SELECT dbo.lakeflowUtilityVersion() AS UtilityVersion;
SELECT dbo.lakeflowDetectPlatform() AS Platform;

(Upgrade only) Resume ingestion: resume the gateway or resume the pipeline.

note

The db_owner role is only required for the user running this setup script. The ingestion user (specified in the @User parameter in subsequent steps) requires only the specific permissions granted by the setup procedures. See Microsoft SQL Server database user requirements for details.

note

(Upgrade only) Avoid making schema changes to tracked tables during the upgrade window. The cutover runs in a transaction, so a concurrent schema change is not lost, but it can block briefly or fail fast and need a retry until the cutover completes.

Step 2: Enable change tracking (for tables with primary keys)

Change tracking is a lightweight mechanism that tracks changes to table rows. This step enables CT at the database level on specified tables. To also capture schema changes (DDL), pass @CreateDdlSupportingObjects = 1 to create the DDL support objects. This is opt-in and off by default. For details, see lakeflowSetupChangeTracking in SQL Server utility objects script reference.

SQL
-- Enable change tracking on specific tables
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'Sales.Orders,Production.Products,HR.Employees',
    @User = 'your_ingestion_user',
    @Retention = '2 DAYS';

Alternative options:

For all tables with primary keys: @Tables = 'ALL'
For specific schemas: @Tables = 'SCHEMAS:Sales,HR,Production'
For database-level setup only (no table enablement): @Tables = NULL

Step 3: Enable change data capture (for tables without primary keys)

CDC captures insert, update, and delete activity and is particularly useful for tables without primary keys. This step enables CDC at the database level, sets up capture instance management, and creates triggers for automatic schema change handling. For details, see lakeflowSetupChangeDataCapture in SQL Server utility objects script reference.

SQL
-- Enable CDC on specific tables (particularly those without primary keys)
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'your_ingestion_user';

Alternative options:

For all tables: @Tables = 'ALL'
For specific schemas: @Tables = 'SCHEMAS:Sales,HR'
For database-level setup only: @Tables = NULL

To let Lakeflow Connect take over a table's pre-existing (non-Lakeflow) CDC capture instance instead of leaving it in place, set @AllowDisablePreExistingCaptureInstances = 1.

note

You can use either change tracking or CDC, or you can use both. Databricks recommends using change tracking for tables with primary keys (step 2) and CDC for tables without primary keys (step 3) for comprehensive coverage.

Capture instance management

Lakeflow Connect uses a prefix-based naming convention to manage CDC capture instances without affecting pre-existing capture instances created by other systems or processes.

Lakeflow capture instance naming

Lakeflow Connect creates and manages capture instances using the following naming pattern:

lakeflow_<schema>_<table>_1
lakeflow_<schema>_<table>_2

Lakeflow Connect only manages capture instances that match this naming pattern. Pre-existing capture instances with different names are preserved and remain unaffected by Lakeflow operations.

note

In utility script versions earlier than 1.4, Lakeflow Connect used the New_ prefix for capture instances (for example, New_schema_table_1). If you are upgrading from an earlier version, run the updated utility script to migrate to the lakeflow_ naming convention. The script automatically handles backward compatibility with existing New_-prefixed capture instances during the transition.

Capture instance slot requirements

SQL Server allows a maximum of 2 capture instances per table. For Lakeflow Connect to work with CDC:

At least one of the two capture instance slots must be available for Lakeflow to create its lakeflow_ prefixed instance.
If both slots are already occupied by non-Lakeflow capture instances, Lakeflow Connect cannot create and manage its own capture instance. Although Lakeflow can read from a pre-existing capture instance, it cannot perform full refresh or schema evolution operations.

tip

If both capture instance slots are occupied, use change tracking instead, or remove one of the existing capture instances if it's no longer needed.

Coexistence with other CDC consumers

Lakeflow Connect can safely coexist with other CDC consumers on the same table:

Pre-existing capture instances are preserved during all Lakeflow operations (for example, full refresh and schema evolution).
Lakeflow only drops and recreates its own lakeflow_ prefixed instances when needed.
Other systems consuming CDC data from non-Lakeflow capture instances continue to function without interruption.

Operations that recreate Lakeflow capture instances:

The following operations cause Lakeflow to drop and recreate its lakeflow_ prefixed capture instances (but not others):

Full refresh operations
Adding columns to tables (ADD COLUMN)

Example scenario:

If a table has a pre-existing capture instance named my_app_cdc:

Lakeflow Connect creates lakeflow_schema_table_1.
Both capture instances coexist safely.
When Lakeflow performs a full refresh or schema evolution, it only recreates lakeflow_schema_table_1.
The my_app_cdc instance remains untouched and continues to function for the other system.

Step 4: Grant additional permissions (if needed)

This step grants the necessary system and table-level permissions for the ingestion user. While steps 2 and 3 grant CT- and CDC-specific permissions, this step ensures that the user has all required SELECT permissions. For details, see lakeflowFixPermissions in SQL Server utility objects script reference.

SQL
-- Grant system-level and table-level permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'your_ingestion_user',
    @Tables = 'Sales.Orders,Production.Products,HR.Employees';

Alternative options:

For all tables: @Tables = 'ALL'
System permissions only: @Tables = NULL
Specific schemas: @Tables = 'SCHEMAS:Sales,HR'

note

The setup procedures in steps 2 and 3 automatically grant necessary CT and CDC permissions, but you might have to run this procedure to grant additional table-level SELECT permissions or if permissions were revoked.

Step 5: Verify setup

Run the following queries to confirm that change tracking and CDC are properly configured on your database and tables:

SQL
-- Check Change Tracking status
SELECT
    d.name AS DatabaseName,
    ctd.is_auto_cleanup_on,
    ctd.retention_period,
    ctd.retention_period_units_desc
FROM sys.change_tracking_databases ctd
INNER JOIN sys.databases d ON ctd.database_id = d.database_id
WHERE d.name = DB_NAME();

-- Check tables with Change Tracking enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.is_track_columns_updated_on,
    ct.begin_version,
    ct.cleanup_version
FROM sys.change_tracking_tables ct
INNER JOIN sys.tables t ON ct.object_id = t.object_id;

-- Check CDC status
SELECT
    DB_NAME() AS DatabaseName,
    is_cdc_enabled
FROM sys.databases
WHERE database_id = DB_ID();

-- Check tables with CDC enabled
SELECT
    SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
    ct.capture_instance,
    ct.start_lsn,
    ct.create_date
FROM cdc.change_tables ct
INNER JOIN sys.tables t ON ct.source_object_id = t.object_id;

Upgrade utility objects

Upgrading uses the same script as a first-time installation. Follow Step 1: Install or upgrade utility objects, including the steps marked (Upgrade only), which cover pausing ingestion, re-running the setup procedures, and resuming ingestion.

note

To revert to a previous version of the utility objects script, contact Databricks Support.

Example: Hybrid approach

note

This example uses 'ALL' to enable CT and CDC on all tables for simplicity. For production use, consider the common scenarios on this page to target specific schemas or tables.

SQL
-- Step 1: Already completed (script installed)

-- Step 2 & 3: Enable both CT and CDC
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'ALL',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'ALL',
    @User = 'lakeflow_user';

-- Step 4: Grant all necessary permissions
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

Common scenarios

Scenario 1: Change tracking only (specific schemas)

SQL
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,Production',
    @User = 'lakeflow_user',
    @Retention = '2 DAYS';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'SCHEMAS:Sales,Production';

Scenario 2: CDC only (specific tables)

SQL
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords',
    @User = 'lakeflow_user';

EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'Staging.ImportData,Logs.AuditTrail,dbo.TempRecords';

Scenario 3: Hybrid approach (CT for some schemas, CDC for specific tables)

SQL
-- Enable CT on transactional schemas
EXEC dbo.lakeflowSetupChangeTracking
    @Tables = 'SCHEMAS:Sales,HR',
    @User = 'lakeflow_user',
    @Retention = '3 DAYS';

-- Enable CDC on specific staging tables without primary keys
EXEC dbo.lakeflowSetupChangeDataCapture
    @Tables = 'Staging.ImportData,Logs.AuditTrail',
    @User = 'lakeflow_user';

-- Grant permissions on all tables
EXEC dbo.lakeflowFixPermissions
    @User = 'lakeflow_user',
    @Tables = 'ALL';

Additional resources

SQL Server utility objects script reference
Configure Microsoft SQL Server for ingestion into Databricks
Microsoft SQL Server database user requirements
Track data changes (SQL Server) in the SQL Server documentation

Requirements​

Step 1: Install or upgrade utility objects​

Step 2: Enable change tracking (for tables with primary keys)​

Step 3: Enable change data capture (for tables without primary keys)​

Capture instance management​

Lakeflow capture instance naming​

Capture instance slot requirements​

Coexistence with other CDC consumers​

Step 4: Grant additional permissions (if needed)​

Step 5: Verify setup​

Upgrade utility objects​

Example: Hybrid approach​

Common scenarios​

Scenario 1: Change tracking only (specific schemas)​

Scenario 2: CDC only (specific tables)​

Scenario 3: Hybrid approach (CT for some schemas, CDC for specific tables)​

Additional resources​

Requirements

Step 1: Install or upgrade utility objects

Step 2: Enable change tracking (for tables with primary keys)

Step 3: Enable change data capture (for tables without primary keys)

Capture instance management

Lakeflow capture instance naming

Capture instance slot requirements

Coexistence with other CDC consumers

Step 4: Grant additional permissions (if needed)

Step 5: Verify setup

Upgrade utility objects

Example: Hybrid approach

Common scenarios

Scenario 1: Change tracking only (specific schemas)

Scenario 2: CDC only (specific tables)

Scenario 3: Hybrid approach (CT for some schemas, CDC for specific tables)

Additional resources