Run the Script Manually to Support High-Availability Data Replication (HADR) on a Secondary Node

/* =============================================================================================================================================================
   ======================================================================
-- Script       : CDC Jobs to support HADR
-- Description  : It checks if HADR is enabled on the server.
--                If HADR is enabled, it adds a schedule for the CDC capture agent if it does not already exist.
--                  It updates the Change Data Capture Collection Agent to handle HADR.
--                  It attaches an additional schedule for the CDC capture job for HADR failback scenarios.
--                  It updates the Change Data Capture Cleanup Agent to handle HADR.
-- Usage        : Change the @DBname and exexute it.
============================================================================================================================================================= */
USE msdb;
GO

DECLARE @DBname NVARCHAR(128) = '<<DatabaseName>>'; --Declare Database Name

IF SERVERPROPERTY('IsHadrEnabled') = 1 --Check if HADR is enabled
BEGIN
	DECLARE @jobName NVARCHAR(128), @jobId UNIQUEIDENTIFIER, @stepId INT, @scheduleId INT; --Declare Variables

	--$BeginRegion: Adding HADR CDC schedule if not exists
	IF NOT EXISTS
	(
		SELECT *
		FROM msdb.dbo.sysschedules
		WHERE name = N'HADR CDC capture agent schedule'
			AND freq_type = 4 AND freq_interval = 1 AND freq_subday_type = 4 AND freq_subday_interval = 1 AND freq_relative_interval = 0 AND freq_recurrence_factor = 0
			AND active_start_date = 19900101 AND active_end_date = 99991231 AND active_start_time = 0 AND active_end_time = 235959
	)
	EXEC msdb.dbo.sp_add_schedule --Add HADR CDC schedule
		@schedule_name = N'HADR CDC capture agent schedule',
		@enabled = 1,
		@freq_type = 4,
		@freq_interval = 1,
		@freq_subday_type = 4,
		@freq_subday_interval = 1,
		@freq_relative_interval = 0,
		@freq_recurrence_factor = 1,
		@active_start_date = 19900101,
		@active_end_date = 99991231,
		@active_start_time = 0,
		@active_end_time = 235959;
	--$EndRegion: Adding HADR CDC schedule if not exists


	--$BeginRegion: Update Change Data Capture Collection Agent to handle HADR
	SELECT @jobName = NULL, @jobId = NULL, @stepId = NULL; --Reset Values

	SET @jobName = 'cdc.'+@DBname+'_capture';

	SELECT @jobId = J.job_id, @stepId = JS.step_id
	FROM msdb.dbo.sysjobs AS J
	INNER JOIN msdb.dbo.sysjobsteps AS JS ON JS.job_id = J.job_id
	WHERE J.name = @jobName
		  AND JS.step_name = 'Change Data Capture Collection Agent';
	
	IF @jobId IS NOT NULL AND @stepId IS NOT NULL
		EXEC msdb.dbo.sp_update_jobstep -- Update Change Data Capture Collection Agent to handle HADR
			@job_id = @jobId,
			@step_id = @stepId,
			@command = 
N'IF SERVERPROPERTY(''IsHadrEnabled'') = 1
    BEGIN
        IF EXISTS (
                        SELECT *
                        FROM sys.dm_hadr_database_replica_states AS DRS
                        INNER JOIN sys.availability_replicas AS AR
                            ON AR.group_id = DRS.group_id AND AR.replica_id = DRS.replica_id
                        INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
                            ON ARS.group_id = DRS.group_id AND ARS.replica_id = DRS.replica_id
                        WHERE AR.replica_server_name = @@SERVERNAME
                            AND DRS.database_id = DB_ID()
                            AND DRS.is_local = 1
                            AND DRS.is_primary_replica = 1
                            --AND DRS.database_state_desc = ''ONLINE''
                    )
            BEGIN
                EXEC sys.sp_MScdc_capture_job;
            END;
    END;
ELSE
    BEGIN
        EXEC sys.sp_MScdc_capture_job;
    END;
GO';
	--$EndRegion: Update Change Data Capture Collection Agent to handle HADR


	--$BeginRegion: CDC Capture Job Additional Schedule for HADR Fail Back Scenario
	SELECT @jobName = NULL, @jobId = NULL, @stepId = NULL; --Reset Values
	
	SET @jobName = 'cdc.'+@DBname+'_capture';

	SELECT @jobId = job_id
	FROM msdb.dbo.sysjobs
	WHERE name = @jobName;
	
	SELECT @scheduleId = schedule_id
	FROM msdb.dbo.sysschedules
	WHERE name = 'HADR CDC capture agent schedule';
	
	IF @jobId IS NOT NULL AND @scheduleId IS NOT NULL
		EXEC msdb.dbo.sp_attach_schedule --Attach CDC Capture Job Additional Schedule for HADR Fail Back Scenario
			@job_id = @jobId,
			@schedule_id = @scheduleId;
	--$EndRegion: CDC Capture Job Additional Schedule for HADR Fail Back Scenario


	--$BeginRegion: Update Change Data Capture Cleanup Agent to handle HADR
	SELECT @jobName = NULL, @jobId = NULL, @stepId = NULL; --Reset Values

	SET @jobName = 'cdc.'+@DBname+'_cleanup';

	SELECT @jobId = J.job_id, @stepId = JS.step_id
	FROM msdb.dbo.sysjobs AS J
	INNER JOIN msdb.dbo.sysjobsteps AS JS ON JS.job_id = J.job_id
	WHERE J.name = @jobName
		  AND JS.step_name = 'Change Data Capture Cleanup Agent';

	IF @jobId IS NOT NULL AND @stepId IS NOT NULL
		EXEC msdb.dbo.sp_update_jobstep --Update Change Data Capture Cleanup Agent to handle HADR
			@job_id = @jobId,
			@step_id = @stepId,
			@command = 
N'IF SERVERPROPERTY(''IsHadrEnabled'') = 1
    BEGIN
        IF EXISTS (
                        SELECT *
                        FROM sys.dm_hadr_database_replica_states AS DRS
                        INNER JOIN sys.availability_replicas AS AR
                            ON AR.group_id = DRS.group_id AND AR.replica_id = DRS.replica_id
                        INNER JOIN sys.dm_hadr_availability_replica_states AS ARS
                            ON ARS.group_id = DRS.group_id AND ARS.replica_id = DRS.replica_id
                        WHERE AR.replica_server_name = @@SERVERNAME
                            AND DRS.database_id = DB_ID()
                            AND DRS.is_local = 1
                            AND DRS.is_primary_replica = 1
                            --AND DRS.database_state_desc = ''ONLINE''
                    )
            BEGIN
                EXEC sys.sp_MScdc_cleanup_job;
            END;
    END;
ELSE
    BEGIN
        EXEC sys.sp_MScdc_cleanup_job;
    END;
GO';
	--$EndRegion: Update Change Data Capture Cleanup Agent to handle HADR
END;
GO