Dealing with Change Data Capture jobs for databases in Availability Groups
Problem Having Change Data Capture enabled on databases that are part of an Availability Group, each time a failover happens (automatically or manually), we have to remember to make sure that the CDC capture and cleanup jobs are created and the capture job is started accordingly on the primary replica. Let's be honest, we are not going to remember (nor we want to) that after doing a system update and doing multiple failovers or after automatic failover we have some manual work to do. If we do forget about it, we most likely going to end up wtih some data loss in our CDC tables and a gap in anything that might rely on that data. Solution Lets first point out few things that we have to keep in mind while dealing with the problem that we are presented: There might be more than one Availability Group in a Always On setup and each group might have primary role on different nodes. When enabling CDC on a database, jobs are only created on the currently active node. This means t...
