Change Data Capture and Other SQL Server Features

  • Article

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

  • Change tracking

  • Database mirroring

  • Transactional replication

  • Restoring or Attaching a Database Enabled for 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 AdventureWorks2012 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 is not available when change data capture is enabled.

Restoring or Attaching a Database Enabled for Change Data Capture

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 Enterprise, the operation is blocked because change data capture requires SQL Server Enterprise. 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 disable change data capture in the database by using a supported edition of SQL Server, 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.

See Also

Concepts

Administer and Monitor Change Data Capture (SQL Server)