Switch CDC jobs by stopping, enabling and disabling them based on current AG replica role. To be called as response to state change alerts.

switch-cdc-jobs.sql

DECLARE	@sqlstmts	nvarchar(max);

WITH cte_sqlstmts AS
(
	SELECT
		N'exec msdb.dbo.sp_stop_job @job_name = ''' + [name] + N'''; ' AS sqlstmt
	FROM	msdb.dbo.cdc_jobs AS J1
	JOIN	msdb.dbo.sysjobs_view AS J2
	  ON	J1.job_id = J2.job_id
	JOIN	msdb.dbo.sysjobactivity AS A1
	  ON	J2.job_id = A1.job_id
	JOIN	msdb.dbo.syssessions AS S1
	  ON	A1.[session_id] = S1.[session_id]
	JOIN	(
			SELECT	MAX(agent_start_date) AS max_agent_start_date
			FROM	msdb.dbo.syssessions
			) AS S2
	  ON	S1.agent_start_date = S2.max_agent_start_date
	WHERE	sys.fn_hadr_is_primary_replica(DB_NAME(J1.database_id)) = 0
	  AND	run_requested_date IS NOT NULL AND stop_execution_date IS NULL
	UNION ALL
	SELECT
		N'exec msdb.dbo.sp_update_job @job_name = ''' + [name] + N''', @enabled = 0;' AS sqlstmt
	FROM	msdb.dbo.cdc_jobs AS J1
	JOIN	msdb.dbo.sysjobs AS J2
	  ON	J1.job_id = J2.job_id
	WHERE	sys.fn_hadr_is_primary_replica(DB_NAME(J1.database_id)) = 0
	  AND	J2.[enabled] = 1
	UNION ALL
	SELECT
		N'exec msdb.dbo.sp_update_job @job_name = ''' + [name] + N''', @enabled = 1;' AS sqlstmt
	FROM	msdb.dbo.cdc_jobs AS J1
	JOIN	msdb.dbo.sysjobs AS J2
	  ON	J1.job_id = J2.job_id
	WHERE	sys.fn_hadr_is_primary_replica(DB_NAME(J1.database_id)) = 1
	  AND	J2.[enabled] = 0
)
SELECT
	@sqlstmts = STUFF(
	(
	SELECT	' ' + sqlstmt
	FROM	cte_sqlstmts
	FOR XML PATH ('')
	), 1, 1, '');

--PRINT @sqlstmts
EXEC (@sqlstmts);