En cas de mise en place d’un miroir de base de données, il faut penser à ce que les jobs de l’agent SQL soient actif sur le serveur principal et inactif sur le miroir. Mais que se passe-t-il au basculement ?
Cette procédure à planifier régulièrement (tous les 1/4 d’heure pas exemple) répond à cette attente
USE msdb;
GO
CREATE SCHEMA S_MIRROR;
GO
CREATE PROCEDURE S_MIRROR.P_SET_BATCH_STATE
@DB_NAME sysname
AS
DECLARE @MIRROR_ROLE TINYINT, -- 1 source, 2 miroir (cible)
@JOB_GUID UNIQUEIDENTIFIER,
@JOB_ABLE TINYINT,
@ERROR_STATE BIT,
@ERROR_MSG VARCHAR(max);
DECLARE @BATCH_TO_SET TABLE (BTS_GUID UNIQUEIDENTIFIER,
BTS_ENABLED TINYINT,
BTS_DONE BIT DEFAULT 0,
BTS_ERROR_STATE BIT);
-- quel est l'état de la base (source ou miroir ?)
SELECT @MIRROR_ROLE = mirroring_role
FROM master.sys.database_mirroring
WHERE database_id = @DB_NAME;
-- recherche s'il y a des incohérences entre la position des jobs
-- (activés ou non) et l'état de la base (source ou miroir)
IF EXISTS(SELECT *
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON j.job_id = js.job_id
WHERE database_name = @DB_NAME
AND CASE
WHEN enabled = 1 AND @MIRROR_ROLE = 2 THEN 1
WHEN enabled = 0 AND @MIRROR_ROLE = 1 THEN 1
ELSE 0
END = 1)
BEGIN
-- il y a des jobs à basculer
-- dans quel état bascule t-on les jobs ?
SET @JOB_ABLE = CASE
WHEN @MIRROR_ROLE = 2 THEN 0
WHEN @MIRROR_ROLE = 1 THEN 1
END;
-- recherche des travaux opérant sur une base spécifiée et à basculer
INSERT INTO @BATCH_TO_SET (BTS_GUID, BTS_ENABLED)
SELECT DISTINCT j.job_id, enabled
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
ON j.job_id = js.job_id
WHERE database_name = @DB_NAME
AND CASE
WHEN enabled = 1 AND @MIRROR_ROLE = 2 THEN 1
WHEN enabled = 0 AND @MIRROR_ROLE = 1 THEN 1
ELSE 0
END = 1;
-- tant qu'il existe encore un job non basculé...
WHILE EXISTS(SELECT *
FROM @BATCH_TO_SET
WHERE BST_DONE = 0)
BEGIN
-- recherche d'un job non encore basculé
SELECT TOP (1) @JOB_GUID = BTS_GUID
FROM @BATCH_TO_SET
WHERE BST_DONE = 0;
EXEC msdb.dbo.sp_update_job @job_id = @JOB_GUID,
@enabled = @JOB_ABLE;
IF @@ERROR <> 0
SET @ERROR_STATE = 1
ELSE
SET @ERROR_STATE = 0;
UPDATE @BATCH_TO_SET
SET BTS_DONE = 1,
BTS_ERROR_STATE = @ERROR_STATE
WHERE BTS_GUID = @JOB_GUID;
END; -- WHILE
END; --IF
-- concaténation des erreurs pour message
IF EXISTS(SELECT *
FROM @BATCH_TO_SET
WHERE BTS_ERROR_STATE = 1)
BEGIN
SET @ERROR_MSG = '';
SELECT @ERROR_MSG = @ERROR_MSG + ' - ' + CAST(BTS_GUID AS VARCHAR(64))
FROM @BATCH_TO_SET
WHERE BTS_ERROR_STATE = 1;
RAISERROR('Certains job n''ont pas pu basculer d''état lors du changement d''état du miroir : %s', 16, 1);
END;
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
