Datafly.pro

Sur le secondaire, lorsqu'un des groupes devient primaire, nous allons forcer le basculement des groupes qui n'ont pas basculé avec un job de l'agent SQL Server (appelons le "failover other groups"), qui lancera ce script :

WAITFOR DELAY '00:00:05';
IF EXISTS
(
    SELECT *
    FROM sys.dm_hadr_availability_group_states hags
    WHERE UPPER(primary_replica) = UPPER(@@SERVERNAME)
)
   AND EXISTS
(
    SELECT *
    FROM sys.dm_hadr_availability_group_states hags
    WHERE UPPER(primary_replica) <> UPPER(@@SERVERNAME)
)  -- Il y a des primaires et des secondaires sur ce noeud
BEGIN
    -- failover all secondary groups
    SELECT ag.name
    FROM sys.dm_hadr_availability_group_states hags
        INNER JOIN sys.availability_groups ag
            ON ag.group_id = hags.group_id
    WHERE UPPER(hags.primary_replica) <> UPPER(@@SERVERNAME);
    IF @@ROWCOUNT > 0
    BEGIN
        DECLARE @SQLCmd NVARCHAR(MAX) = N'';
        SELECT @SQLCmd = @SQLCmd + N'ALTER AVAILABILITY GROUP ' + ag.name + N' FAILOVER; WAITFOR DELAY ''00:00:01''; '
        FROM sys.dm_hadr_availability_group_states hags
            INNER JOIN sys.availability_groups ag
                ON ag.group_id = hags.group_id
        WHERE UPPER(hags.primary_replica) <> UPPER(@@SERVERNAME);
        PRINT @SQLCmd;
        EXEC (@SQLCmd);
    END;
END;

Nous allons déclencher ce job à partir d'une alerte SQL Server Agent. L'alerte sera positionnée sur le numéro d'erreur 1480 (AG Role Change).

Cette alerte se déclenchant à chaque changement de rôle, il nous faudra la filtrer pour ne la déclencher que si un des groupes devient PRIMARY sur le serveur concerné (nous ferons cela en filtrant sur le message d'erreur, dans l'exemple ci-dessous pour une version française). Il faut aussi penser à configurer un délai entre réponses pour que le basculement de chaque groupe ne redéclenche pas l'alerte à nouveau.

EXEC msdb.dbo.sp_add_alert @name=N'1480 - AG Role Change',
        @message_id=1480,
        @severity=0,
        @enabled=1,
        @delay_between_responses=90,
        @include_event_description_in=0,
        @event_description_keyword=N'« RESOLVING » en « PRIMARY »',
        @category_name=N'[Uncategorized]',
        @job_name=N'Failover other groups'

La même configuration sera installée à l'identique sur les deux noeuds.

La principale difficulté consiste à éviter une réentrance : c'est donc les différents délais qui vont permettre de laisser du temps au basculement. Un réglage fin de ces délais est donc primordial, avec quelques tests à la clé : délai entre réponses, délai au début du job...

Inconvénients, ces délais vont allonger les temps de bascule, et toutes les bases ne seront pas disponibles en même temps, le temps que toute l'opération s'effectue. Si vous avez une meilleure solution, mettez un petit commentaire !

Mais sinon, il ne vous reste plus qu'à vous munir d'une édition Enterprise...