Continuing to previous post link, where I explained the reason of CDC capture job failure for a database enabled in transactional replication with remote distributor; here I would like to discuss the scenario of enabling change data capture for a database with local distributor.
In this article we will discuss about interoperatibility of SQL server transactional replication and change data capture together. SQL server transactional replication is used to replicate all or a set of objects (tables/stored procedures/functions etc) from publisher to subscriber database by replicating the transactions from log file while change data capture is used to capture essential log records to track changes in source tables and store them in change tables with mirror column structure as of tracked source tables.
As both technologies depend on transaction log records to capture the changes, we need to keep few things in mind to implement them together in on-premise SQL server and SQL server managed instance.
- Change data capture and transactional replication both uses sp_replcmds stored procedure to capture changes from transaction log.
- Please note that SQL server does not allow change data capture job and log reader agent job to be created on the same server , hence DBA should not manually create capture job.
- In case change data capture is enabled for a database, SQL server will delete the capture job during configuration of transaction replication on the database. Log reader agent will capture changes for both replication and change tables through sp_replcmds.
- If someone creates capture job manually on a published database, both log reader agent and change data capture job would try to read the transaction log at same time, below error will be encountered for one of process.
Status: 0, code: 22903, text: 'Another connection is already running 'sp_replcmds' for Change Data Capture in the current database.'. - If replication is disabled on the database having CDC enabled, capture job will be re-created.
- Changes are first written to distribution database followed by change tables and they are committed together. Latency in publisher to distribution database will cause latency in writing changes to change tables as well.
- Publishing Stored Procedure Execution in Transactional Replication option is not available if change data capture is enabled on the database.
- Although change data capture feature is availabile for Azure SQL database but Azure SQL database cannot be published in transactional replication.
Implementation:
Enabling both CDC and transactional replication for the same database does not require any additional steps. Both features can be enabled by following standard steps for configuring them.
In summary, SQL server uses log reader agent to track changes both for transactional replication and change data capture. SQL has implemented this behavior to avoid log contention between processes of transactional replication and CDC. SQL Server automatically creates change data capture job if replication gets disabled on a CDC enabled database.