• Repost - originally posted 04-06-2012

SQL Server 2012 Availability Groups Lessons Pt 6

This is the sixth tip on SQL Server 2012 availability groups to make sure CDC works.

Tip 6: Change Data Capture (CDC) has been around since SQL Server 2008, but now you need to incorporate CDC with SQL Server 2012 availability groups in a high availability scenario (primary and secondary for failover).

CDC has two SQL Server Agent jobs (capture and clean up) that run to accomplish the data capture. These jobs exist on the primary server in the availability group setup, but you will also need to create them on the secondary server/s and have them disabled. But these jobs cannot be enabled on the secondary server as the CDC AG database is not accessible. But these jobs need to be enabled on the secondary server and disabled on the primary server when failover occurs.

The issue is how do you monitor if the database is the primary or the secondary? There is no monitor job like there is for database mirroring, so the solution is that you need to create your own job that monitors the state of the AG database and then enables or disables the CDC jobs as appropriate.

Alternatively you could setup an alert for alert 1480 'Role Change' and have that alert fire a job that enables the CDC jobs if they are disabled or enables them if they are disabled - still not a simple solution.

Recent Posts

See All