Environment

-> Create a Job called “SQL Server Agent Job Synchronization” on all the Database Servers as part of your Alwayson Availability group. In my Environment, the Job will be created on Database Server JBSERVER1, JBSERVER2 and JBSERVER3. The Job “SQL Server Agent Job Synchronization” will have the below script executed as part of it.

-- Script to sync SQL Server Agent Jobs from Primary Replica to Secondary Replica in an Always On Availability Group
-- Dont forgot to change the listener name below
SET NOCOUNT ON;

DECLARE @primary_replica NVARCHAR(128),
        @local_replica NVARCHAR(128),
        @job_name NVARCHAR(128),
        @job_id UNIQUEIDENTIFIER,
        @tsql NVARCHAR(MAX),
        @sql NVARCHAR(MAX);

				

-- Get the primary replica name
SELECT @Primary_Replica = primary_replica
FROM sys.dm_hadr_availability_group_states a INNER JOIN sys.availability_group_listeners b
ON a.group_id=b.group_id where b.dns_name='DISL' ---Change the LISTENER NAME here

-- Get the current replica name (where this script is running)
SELECT @local_replica = @@SERVERNAME;

-- If this server is the primary replica, no need to sync jobs
IF @local_replica = @primary_replica
BEGIN
    PRINT 'This server is the primary replica. No job sync required.';
    RETURN;
END


-- Create a table to store jobs from the primary replica
IF OBJECT_ID('tempdb..#primary_jobs') IS NOT NULL
    DROP TABLE #primary_jobs;

CREATE TABLE #primary_jobs (
    job_id UNIQUEIDENTIFIER,
    job_name NVARCHAR(128)
);

-- Insert jobs from primary replica into the temp table
SET @sql = 'INSERT INTO #primary_jobs (job_id, job_name)
            SELECT job_id, name FROM [' + @primary_replica + '].msdb.dbo.sysjobs';

EXEC sp_executesql @sql;

-- Loop through jobs on primary replica and compare with local (secondary) replica
DECLARE job_cursor CURSOR FOR
SELECT job_id, job_name
FROM #primary_jobs;

OPEN job_cursor;
FETCH NEXT FROM job_cursor INTO @job_id, @job_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Check if the job exists on the local (secondary) replica
    IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = @job_name)
    BEGIN
        PRINT 'Job missing on secondary replica: ' + @job_name;

        -- Script job creation from the primary replica
        DECLARE @job_creation_script NVARCHAR(MAX) = '';
        DECLARE @step_creation_script NVARCHAR(MAX) = '';
        DECLARE @schedule_creation_script NVARCHAR(MAX) = '';

        -- Step 1: Script the job creation
        SET @job_creation_script = 'EXEC msdb.dbo.sp_add_job @job_name = ''' + @job_name + ''', @enabled = 1, @description = ''' + @job_name + ''';';
        
        -- Step 2: Script the job steps from the primary replica
        DECLARE @step_id INT,
                @step_name NVARCHAR(128),
                @subsystem NVARCHAR(128),
                @command NVARCHAR(MAX),
                @on_success_action INT,
                @on_fail_action INT;
				

						set @sql=N''
				set @sql =         'SELECT step_id, step_name, subsystem, command, on_success_action, on_fail_action  INTO ##Primary_Job_jbs_wiki_details
        FROM [' + @primary_replica + '].msdb.dbo.sysjobsteps 
        WHERE job_id = '''+convert(nvarchar(max),@job_id)+''';'
		EXECUTE master.sys.sp_executesql @sql;

        DECLARE step_cursor CURSOR FOR 
        SELECT step_id, step_name, subsystem, command, on_success_action, on_fail_action 
        FROM ##Primary_Job_jbs_wiki_details;

        OPEN step_cursor;
        FETCH NEXT FROM step_cursor INTO @step_id, @step_name, @subsystem, @command, @on_success_action, @on_fail_action;

        WHILE @@FETCH_STATUS = 0
        BEGIN
		
            SET @step_creation_script = @step_creation_script + 'EXEC msdb.dbo.sp_add_jobstep 
                    @job_name = ''' + @job_name + ''', 
                    @step_name = ''' + @step_name + ''', 
                    @subsystem = ''' + @subsystem + ''', 
                    @command = ''' + REPLACE(@command, '''', '''''') + ''', 
                    @on_success_action = ' + CAST(@on_success_action AS NVARCHAR(10)) + ',
                    @on_fail_action = ' + CAST(@on_fail_action AS NVARCHAR(10)) + ';';
                    
            FETCH NEXT FROM step_cursor INTO @step_id, @step_name, @subsystem, @command, @on_success_action, @on_fail_action;
        END
		drop table ##Primary_Job_jbs_wiki_details
        CLOSE step_cursor;
        DEALLOCATE step_cursor;

        -- Step 3: Script the job schedule from the primary replica
        DECLARE @schedule_name NVARCHAR(128),
                @enabled INT,
                @freq_type INT,
                @freq_interval INT,
                @freq_subday_type INT,
                @freq_subday_interval INT,
                @freq_relative_interval INT,
                @freq_recurrence_factor INT,
                @active_start_date INT,
                @active_start_time INT;

				set @sql = N''
		set @sql = 'SELECT s.name, s.enabled, s.freq_type, s.freq_interval, s.freq_subday_type, s.freq_subday_interval, 
               s.freq_relative_interval, s.freq_recurrence_factor, s.active_start_date, s.active_start_time INTO ##Primary_Job_jbs_wiki_details1
        FROM [' + @primary_replica + '].msdb.dbo.sysschedules AS s
        INNER JOIN [' + @primary_replica + '].msdb.dbo.sysjobschedules AS js ON s.schedule_id = js.schedule_id
        WHERE js.job_id = '''+convert(nvarchar(max),@job_id)+''';'
		EXECUTE master.sys.sp_executesql @sql;

        DECLARE schedule_cursor CURSOR DYNAMIC FOR 
        SELECT s.name, s.enabled, s.freq_type, s.freq_interval, s.freq_subday_type, s.freq_subday_interval, 
               s.freq_relative_interval, s.freq_recurrence_factor, s.active_start_date, s.active_start_time 
        FROM ##Primary_Job_jbs_wiki_details1 s;

        OPEN schedule_cursor;
        FETCH NEXT FROM schedule_cursor INTO @schedule_name, @enabled, @freq_type, @freq_interval, @freq_subday_type, 
                                              @freq_subday_interval, @freq_relative_interval, @freq_recurrence_factor, 
                                              @active_start_date, @active_start_time;

        WHILE @@FETCH_STATUS = 0
        BEGIN
			SET @schedule_creation_script = @schedule_creation_script + 'EXEC msdb.dbo.sp_add_jobschedule 
                    @job_name = ''' + @job_name + ''', 
                    @name = ''' + @schedule_name + ''', 
                    @enabled = ' + CAST(@enabled AS NVARCHAR(10)) + ', 
                    @freq_type = ' + CAST(@freq_type AS NVARCHAR(10)) + ', 
                    @freq_interval = ' + CAST(@freq_interval AS NVARCHAR(10)) + ', 
                    @freq_subday_type = ' + CAST(@freq_subday_type AS NVARCHAR(10)) + ', 
                    @freq_subday_interval = ' + CAST(@freq_subday_interval AS NVARCHAR(10)) + ', 
                    @freq_relative_interval = ' + CAST(@freq_relative_interval AS NVARCHAR(10)) + ', 
                    @freq_recurrence_factor = ' + CAST(@freq_recurrence_factor AS NVARCHAR(10)) + ', 
                    @active_start_date = ' + CAST(@active_start_date AS NVARCHAR(10)) + ', 
                    @active_start_time = ' + CAST(@active_start_time AS NVARCHAR(10)) + ';';

            FETCH NEXT FROM schedule_cursor INTO @schedule_name, @enabled, @freq_type, @freq_interval, @freq_subday_type, 
                                                  @freq_subday_interval, @freq_relative_interval, @freq_recurrence_factor, 
                                                  @active_start_date, @active_start_time;
        END
		DROP TABLE ##Primary_Job_jbs_wiki_details1
        CLOSE schedule_cursor;
        DEALLOCATE schedule_cursor;

        -- Combine all scripts and execute to create the job on the secondary replica
        SET @tsql = @job_creation_script + @step_creation_script + @schedule_creation_script;

        EXEC sp_executesql @tsql;
        
        PRINT 'Job created on secondary replica: ' + @job_name;
    END

    FETCH NEXT FROM job_cursor INTO @job_id, @job_name;
END

CLOSE job_cursor;
DEALLOCATE job_cursor;

-- Cleanup
DROP TABLE #primary_jobs;


PRINT 'Job sync completed.';

-> Create a Linked Server to query the primary Replica. In my Environment, Linked servers JBSERVER2 and JBSERVER3 will be created on JBSERVER1. Linked servers JBSERVER1 and JBSERVER3 will be created on JBSERVER2. Linked servers JBSERVER1 and JBSERVER2 will be created on JBSERVER3.

-> The job will gracefully exit with a message “Script cannot run on primary Replica” if the job executes on Primary Replica. If the Job executes on the Secondary replica, It queries the list of SQL Server Agent Jobs on the primary replica and will create the jobs that are missing on the Secondary Replicas.

-> This solution just adds the missing jobs on the Secondary Replicas, but will not Drop Jobs on the Secondary Replica that are not present on the Primary.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.