Does Change Data Capture work on Transaction Replication Subscriber

  • Hello. I'm working on an incremental ETL solution and would like to use CDC. The source database for the ETL is a subscriber in transactional replication. I can't think of any issues with this but I don't have an environment to test it out in. I'll have to request that a vendor configure CDC and don't want to do that unless it will work. Anything I've seen online is talking about configuring CDC on the publisher. I need to know if I can put it on the subscriber. Thanks!

  • Looks like it: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

    It appears to leverage some of the same processes... but this may refer to the publisher rather than the subscriber:

    The logic for change data capture process is embedded in the stored procedure sp_replcmds, an internal server function built as part of sqlservr.exe and also used by transactional replication to harvest changes from the transaction log. In SQL Server and Azure SQL Managed Instance, when change data capture alone is enabled for a database, you create the change data capture SQL Server Agent capture job as the vehicle for invoking sp_replcmds. When replication is also present, the transactional logreader alone is used to satisfy the change data needs for both of these consumers. This strategy significantly reduces log contention when both replication and change data capture are enabled for the same database.

    The switch between these two operational modes for capturing change data occurs automatically whenever there is a change in the replication status of a change data capture enabled database.

     

  • Thanks. I didn't see this page in particular, but I did watch the video on it previously. I could not pull out a definitive answer to the question though. As you mentioned, it sounds like it could be talking about setting up CDC in the publisher vs. the subscriber. I can't think of a reason why it wouldn't work...you can republish a subscribed database so it sounds like something similar. I'll dig into the rest of the article and see if I can pull something out.

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply