Skip to main content

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.
  • 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 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.

  1. Download the script: utility_script.sql

  2. Open the script in SQL Server Management Studio (SSMS), Azure Data Studio, or your preferred SQL client.

  3. Connect to your SQL Server instance as a user with the db_owner role.

  4. Make sure that you are connected to the target database.

  5. Run the script.

  6. Verify installation:

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

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 and sets up DDL support objects to handle schema changes. 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
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.

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;

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​

Last updated on