Exécuter une étape de job Agent uniquement sur le primaire
Plusieurs variantes T-SQL pour qu'une étape de job SQL Server Agent ne s'exécute que sur le réplica primaire d'un groupe de disponibilité.
Sur un groupe de disponibilité, un job SQL Server Agent s'exécute sur chaque réplica qui le porte. Les extraits ci-dessous se placent au début d'une étape de job : ils arrêtent le travail lorsque le réplica local n'est pas le primaire. Ce sont des variantes ; en choisir une.
Vérifier le rôle du réplica local pour un groupe nommé
Cette version lit le rôle d'un groupe de disponibilité nommé sur le réplica local. C'est la forme recommandée dans la source.
IF SERVERPROPERTY ('IsHadrEnabled') = 1
Begin
if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = 'AG_Example'
and ars.is_local = 1) = 'PRIMARY'
begin
-- this server is the primary replica, do something here
print 'PRIMARY NODE'
end
else
begin
-- this server is not the primary replica, (optional) do something here
RAISERROR (N'It isn''t primary group !!! ', -- Message id.
16, -- Severity,
1 -- State,
); -- First argument supplies the string.
end
end
Comparer le nom du serveur avec le nom du réplica
DECLARE @ServerName NVARCHAR(256) = @@SERVERNAME
DECLARE @RoleDesc NVARCHAR(60)
SELECT @RoleDesc = a.role_desc
FROM sys.dm_hadr_availability_replica_states AS a
JOIN sys.availability_replicas AS b
ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName
IF @RoleDesc = 'PRIMARY'
BEGIN
--Logic for the job goes here
print 'exec'
END
Utiliser le rôle numérique du réplica local et démarrer un plan de maintenance
Dans sys.dm_hadr_availability_replica_states, la valeur role vaut 1 pour un réplica primaire et 2 pour un réplica secondaire. Cette version ne démarre un plan de maintenance que sur le primaire.
DECLARE @localReplicaRole int
select @localReplicaRole = Role from [master].sys.dm_hadr_availability_replica_states where is_local = 1;
-- Role = 1 => primary
-- Role = 2 => secondary
IF (@localReplicaRole = 1)
BEGIN
declare @plan_id uniqueidentifier
SELECT @plan_id = id FROM msdb.dbo.sysmaintplan_plans AS s WHERE s.name=N'<YOUR JOBNAME GOES IN HERE>'
exec msdb.dbo.sp_maintplan_start @plan_id
END
Faire échouer l'étape sur un réplica secondaire
Cette version fait échouer l'étape sur un réplica secondaire, afin que l'échec apparaisse dans l'historique du job.
IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
DECLARE @chk varchar(200) = (SELECT ARS.role_desc FROM sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id WHERE ARS.role_desc = 'PRIMARY')
IF @chk = 'PRIMARY'
BEGIN
print 'PRIMARY NODE'
END
ELSE
BEGIN
select 1/0
END
END
Remarques
- Le nom
AG_Exampleest un placeholder pour le nom du groupe de disponibilité. - La deuxième version compare
@@SERVERNAMEàreplica_server_name. La comparaison fonctionne lorsque les deux valeurs correspondent, ce qui est le cas pour l'instance sur laquelle le job s'exécute. - La première version lève une erreur de gravité 16, ce qui fait échouer l'étape sur un réplica secondaire. La dernière version force volontairement une division par zéro pour faire échouer l'étape. Choisir la forme qui correspond à la façon dont vos alertes traitent les étapes en échec.
- Les discussions d'origine sont dans les sources ci-dessous.
Références
- SQL Server Agent jobs and availability groups (dba.stackexchange, en anglais)
- SQL Server availability groups: add check if primary step to existing agent jobs (Mitch Wheat, en anglais)