Managing CDC Jobs on an Availability Group

So, here’s the deal. You’ve got Change Data Capture going in your environment and want to move your database to an Availability Group on SQL Server 2012. That’s great, lots of advantages there. But what happens to CDC when your database fails over to a different node? Since the capture process runs through SQL Agent jobs you will no longer be getting the CDC information post-failover. This is the scenario I ran into recently. In order to mitigate the problem I’ve created a simple script to handle the enabling and disabling of jobs on both replicas. Of course, the jobs will need to be created on each replica before it will work. But this is an easy way of making sure you can take advantage of the benefits of AG’s while still keeping up your CDC captures. I have this running every five minutes on each node. Feel free to adapt/improve/delete it to your heart’s content. Note that I have hard-coded the replica_id. I know, not the most elegant method. But good enough for a first version.

****Update****

Through painful experience I’ve discovered that the replica id can change if you remove the replica and add it back in. A slightly better method of checking utilizes sys.dm_hadr_availability_group_states. In this case we’re checking against the instance name, which will always remain the same.

IF (select primary_replica from sys.dm_hadr_availability_group_states) = ‘YourReplicaName’

****Update 2****

For SQL Server 2014 and above you are not allowed to even run the USE statement against a database that is on a read-only replica (ie your secondary replica used for reporting). For that reason I’ve altered it to use sp_start_job instead. The updated version below also simplifies other syntax a bit.

--Check to see if this instance is the Primary Replica.
IF (select primary_replica from sys.dm_hadr_availability_group_states) = 'YourReplicaName'
	BEGIN
		--If this is the Primary and the capture job is disabled, enable and start it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_capture') = '0'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_capture', @enabled = 1;
				EXEC msdb.dbo.sp_start_job N'cdc.YourJobName_capture' ;  
			END
		--If this is the Primary and the cleanup job is disabled, enable it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_cleanup') = '0'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_cleanup', @enabled = 1;
			END
	END
--Actions to take if this instance is not the Primary Replica.
ELSE
	BEGIN
		--If this is not the Primary and the capture job is enabled, disable it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_capture') = '1'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_capture', @enabled = 0;
			END
		--If this is not the Primary and the capture job is enabled, disable it.
		IF (select enabled from MSDB.dbo.sysjobs where name = 'cdc.YourJobName_cleanup') = '1'
			BEGIN
				EXEC msdb.dbo.sp_update_job @job_name = N'cdc.YourJobName_cleanup', @enabled = 0;
			END
	END

****Update 3****

Check out this post to learn about firing off jobs based on SQL alerts. This would be a better approach than checking on a timed interval.