CDC change Data Capture on High availibility AlwaysOn 2019

Question

Hello,

 I have three nodes (SQL Server 2019) Always On availability group configuration -FYI.

I have applied the CDC (Change Data Capture) jobs [cdc.DBName_capture] and [cdc.DBName_cleanup] on all three nodes. However, when a failover occurs from the primary node to a secondary node, the CDC jobs stop working and require manual intervention to start them.

Question: How can we ensure the CDC jobs start automatically after a failover to another node without manual intervention?

Alternatively, is there a script that can automatically enable the CDC jobs when a node becomes the primary and disable them when it becomes a secondary?

Answer 1

The AI answer gives an outline for how to address this in the realm of SQL 2019. I don't know how accurate the response is, but the idea seems sound, even if it spells kludge.

SQL 2019 is out of mainstream support, so it may be time to think about an upgrade, and with SQL 2025 or SQL 2022 there is a new feature, Contained Availability Groups, which offers new possibilities in this area. A Contained AG has its own master and msdb, which means that when the group fails over, so does master and msdb, and therefore jobs will keep on running.

I will need to add the disclaimer that I have not tested CDC on contained AGs, so I can't say how well it works.