Edit

Change data capture and other features

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article describes how the following features interact with change data capture:

Change tracking

Change data capture and change tracking can be enabled on the same database. No special considerations are required. For more information, see Work with Change Tracking (SQL Server).

Database mirroring

A database that is enabled for change data capture can be mirrored. To ensure that capture and cleanup happen automatically after a failover, follow these steps:

  1. Ensure that SQL Server Agent is running on the new principal server instance.

  2. Create the capture job and cleanup job on the new principal database (the former mirror database). To create the jobs, use the sp_cdc_add_job stored procedure.

To view the current configuration of a cleanup or capture job, use the sys.sp_cdc_help_jobs stored procedure on the new principal server instance. For a given database, the capture job is named cdc.database_name_capture, and the cleanup job is named cdc.database_name_cleanup, where database_name is the name of the database.

To change the configuration of a job, use the sys.sp_cdc_change_job stored procedure.

For information about database mirroring, see Database Mirroring (SQL Server).

Transactional Replication

Change data capture and transactional replication can coexist in the same database, but population of the change tables is handled differently when both features are enabled. Change data capture and transactional replication always use the same procedure, sp_replcmds, to read changes from the transaction log. When change data capture is enabled on its own, a SQL Server Agent job calls sp_replcmds. When both features are enabled on the same database, the Log Reader Agent calls sp_replcmds. This agent populates both the change tables and the distribution database tables. For more information, see Replication Log Reader Agent.

Consider a scenario in which change data capture is enabled on the AdventureWorks2022 database, and two tables are enabled for capture. To populate the change tables, the capture job calls sp_replcmds. The database is enabled for transactional replication, and a publication is created. Now, the Log Reader Agent is created for the database and the capture job is deleted. The Log Reader Agent continues to scan the log from the last log sequence number that was committed to the change table. This ensures data consistency in the change tables. If transactional replication is disabled in this database, the Log Reader Agent is removed and the capture job is re-created.

Note

When the Log Reader Agent is used for both change data capture and transactional replication, replicated changes are first written to the distribution database. Then, captured changes are written to the change tables. Both operations are committed together. If there is any latency in writing to the distribution database, there will be a corresponding latency before changes appear in the change tables.

The proc exec option of transactional replication isn't available when change data capture is enabled.

Database restore or attach

SQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:

  • If a database is restored to the same server with the same database name, change data capture remains enabled.

  • If a database is restored to another server, by default change data capture is disabled and all related metadata is deleted.

    To retain change data capture, use the KEEP_CDC option when restoring the database. For more information about this option, see RESTORE.

  • If a database is detached and attached to the same server or another server, change data capture remains enabled.

  • If a database is attached or restored with the KEEP_CDC option to any edition other than Standard, Enterprise, or SQL Managed Instance, the operation is blocked because change data capture requires SQL Server Standard, Enterprise, or SQL Managed Instance editions. Error message 934 is displayed:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either restore database without KEEP_CDC option, or upgrade the instance to one that supports change data capture.

You can use sys.sp_cdc_disable_db to remove change data capture from a restored or attached database.

After restoring a database on Azure SQL Managed Instance, CDC will remain enabled, but you must ensure that the scan and cleanup jobs are added and running. You can manually add the jobs by running sys.sp_cdc_add_job.

Contained databases

Change data capture isn't supported in contained databases.

Serverless databases

If a serverless database is enabled for Change Data Capture (CDC) is in pause state, CDC won't run. The CDC scan shall not affect autopause.

Availability groups

When you use Always On availability groups, change enumeration should be done on the secondary replica to reduce disk load on the primary.

Failover (local & GeoDR) in Azure SQL Database

In case of local and GeoDR failover scenarios, if the database is enabled for change data capture (CDC), capture and cleanup happen automatically on the new primary, following the failover.

Point-in-time-restore (PITR) in Azure SQL Database

If you enabled change data capture (CDC) on your Azure SQL Database as SQL user, point-in-time-restore (PITR) retains the CDC as well in the restored DB, unless it's restored to subcore SLO. If restored to subcore SLO, CDC artifacts won't be available.

If you enabled CDC on your Azure SQL Database as an Azure AD user, PITR retains the CDC if restored to same or higher SLO than the source database. PITR to subcore SLO fails as mentioned under Known issues and limitations.

Azure Active Directory in Azure SQL Database

If you create a database in Azure SQL Database as an Azure AD user and enable change data capture on it, a SQL user (for example, even sys admin role) won't be able to disable/make changes to change data capture artifacts. However, another Azure AD user is able to enable/disable change data capture on the same database.

Similarly, if you create an Azure SQL Database as a SQL user, enabling/disabling change data capture as an Azure AD user won't work.

See Also

Administer and Monitor change data capture (SQL Server)