Modifier les jobs de l’agent pour ne tourner que sur le primaire

Problématique courante lorsqu’on a des groupes de disponibilité, les travaux de l’agent SQL Server doivent être dupliqués sur tous les noeuds. Mais ils ne doivent généralement s’exécuter que sur le réplica primaire, tout en étant actifs et prêts à fonctionner sur les secondaires en cas de basculement.

Cette procédure insère une nouvelle étape au début de chaque job, qui teste si le noeud est primaire pour un groupe désigné (avec la fonction fn_hadr_group_is_primary décrite dans un autre article). Si ce n’est pas le cas, on stoppe le travail à cette première étape. Le troisième paramètre debug (par défaut activé à 1) permet de tester la procédure sans faire la modification.

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_AG_AddJobStepToCheckPrimary]    Script Date: 05/05/2022 10:30:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER  PROCEDURE [dbo].[sp_AG_AddJobStepToCheckPrimary]
 @job_name sysname,
 @ag_name sysname,
 @debug BIT = 1
AS
SET NOCOUNT ON;
IF @job_name IS NULL 
BEGIN
	PRINT '@job_name parameter is NULL'
	RETURN
END
IF @ag_name IS NULL 
BEGIN
	PRINT '@ag_name parameter is NULL'
	RETURN
END

DECLARE @command NVARCHAR(MAX)
	SELECT @command = 'BEGIN TRAN;
        DECLARE @ReturnCode INT;
        EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=''' + @job_name + 
		''', @step_name=N''get_availability_group_role'', 
                @step_id=1, 
                @cmdexec_success_code=0, 
                @on_success_action=3, 
                @on_success_step_id=0, 
                @on_fail_action=3, 
                @on_fail_step_id=0, 
                @retry_attempts=0, 
                @retry_interval=0, 
                @os_run_priority=0, @subsystem=N''TSQL'', 
                @command=
        N''-- Detect if this instance''''s role is a Primary Replica.
-- If this instance''''s role is NOT a Primary Replica stop the job so that it does not go on to the next job step
DECLARE @rc int; 
SELECT @rc = master.dbo.fn_hadr_group_is_primary ('''''+@ag_name+''''');

IF @rc = 0
BEGIN;
    DECLARE @name sysname;
    SELECT  @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))));
    
    EXEC msdb.dbo.sp_stop_job @job_name = @name;
    PRINT ''''Stopped the job since this is not a Primary Replica'''';
END;'', 
        @database_name=N''master'', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
BEGIN; 
    PRINT ''-- Rollback: ''''' 
		+ @job_name + ''''''' ROLLBACK TRAN; 
END;
ELSE COMMIT TRAN;' -- + CHAR(10) + 'GO';

	PRINT @command;
	IF @debug = 0 EXEC (@command);
GO

USE [msdb]

/****** Object: StoredProcedure [dbo].[sp_AG_AddJobStepToCheckPrimary] Script Date: 05/05/2022 10:30:13 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE OR ALTER PROCEDURE [dbo].[sp_AG_AddJobStepToCheckPrimary]

@job_name sysname,

@ag_name sysname,

@debug BIT = 1

SET NOCOUNT ON;

IF @job_name IS NULL

BEGIN

PRINT '@job_name parameter is NULL'

RETURN

END

IF @ag_name IS NULL

BEGIN

PRINT '@ag_name parameter is NULL'

RETURN

END

DECLARE @command NVARCHAR(MAX)

SELECT @command = 'BEGIN TRAN;

DECLARE @ReturnCode INT;

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=''' + @job_name +

''', @step_name=N''get_availability_group_role'',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=3,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N''TSQL'',

@command=

N''-- Detect if this instance''''s role is a Primary Replica.

-- If this instance''''s role is NOT a Primary Replica stop the job so that it does not go on to the next job step

DECLARE @rc int;

SELECT @rc = master.dbo.fn_hadr_group_is_primary ('''''+@ag_name+''''');

IF @rc = 0

BEGIN;

DECLARE @name sysname;

SELECT @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID))));

EXEC msdb.dbo.sp_stop_job @job_name = @name;

PRINT ''''Stopped the job since this is not a Primary Replica'''';

END;'',

@database_name=N''master'',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0)

BEGIN;

PRINT ''-- Rollback: '''''

+ @job_name + ''''''' ROLLBACK TRAN;

END;

ELSE COMMIT TRAN;' -- + CHAR(10) + 'GO';

PRINT @command;

IF @debug = 0 EXEC (@command);