Change Data Capture and Other SQL Server Features
This topic describes how the following features interact with change data capture:
Change tracking
Database mirroring
Transactional replication
Database restore or attach
Change Tracking
Change data capture and change tracking can be enabled on the same database. No special considerations are required. For more information, see Change Tracking.
Database Mirroring
A database that is enabled for change data capture can be mirrored. To ensure that capture and cleanup happen automatically on the mirror, follow these steps:
Ensure that SQL Server Agent is running on the mirror.
Create the capture job and cleanup job on the mirror after the principal has failed over to the mirror. To create the jobs, use the stored procedure sys.sp_cdc_add_job (Transact-SQL).
For more information about database mirroring, see Database Mirroring.
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. For more information, see Capture Job. 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 AdventureWorks 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.
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 932 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.