Enable built-in CDC in Microsoft SQL Server

Important

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 built-in change data capture (CDC) in Microsoft SQL Server. You must enable either change tracking or 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 built-in CDC for the source database

1. Log into the database you want to enable for CDC.

2. Run the following T-SQL command in the database context:

   Azure SQL Database and on-prem SQL Server

   EXEC sys.sp_cdc_enable_db
   

   Amazon RDS for SQL Server

   EXEC msdb.dbo.rds_cdc_enable_db '<database-name>'
   

For more information, see Enable change data capture for a database in the SQL Server documentation.

Configure CDC retention period

Set the retention period to the maximum time (in minutes) that the gateway is likely to be down. After this amount of time has passed, a full refresh is required to resume the gateway.

EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 20160;

Enable built-in CDC on the source table

To enable CDC on the source table, run the following stored procedure in Azure SQL. Replace the values for source_schema, source_name, and role_name. @support_net_changes only supports a value of 1 if the table has a primary key.

• Replace the values for source_schema, source_name, and role_name.
• If the table has a primary key, @support_net_changes only supports a value of 1.

EXEC sys.sp_cdc_enable_table
@source_schema = N'MySchema',
@source_name   = N'MyTable',
@role_name     = NULL,
@supports_net_changes = 1

For more information, see Enable change data capture for a table in the SQL Server documentation.

Grant SELECT on the CDC schema

In addition to the privileges described in the source setup, the database user needs the SELECT privilege on the schema cdc. This schema contains the change tables that are created when CDC is enabled. Run the following T-SQL command:

GRANT SELECT ON SCHEMA::cdc to <database-user>;

Grant VIEW SERVER STATE (on-prem and RDS)

For on-premises and RDS instances only, the VIEW SERVER STATE privilege is required to query sys.dm_server_services, which is used to check the status of SQL Server Agent. Run the following T-SQL command:

GRANT VIEW SERVER STATE to <database-user>

Next step

Set up DDL capture and schema evolution