Dela via

  • Artikel

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

  • 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 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 database, follow these steps:

  1. Ensure that SQL Server Agent is running on the mirror.

  2. After the principal database has failed over to the mirror database, create the capture job and cleanup job on the former mirror database, which is now the principal 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. For a given database, the capture job is named cdc.<database_name>_capture, and the cleanup job is named cdc.<database_name>_cleanup.

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

For 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 AdventureWorks2008R2 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 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.