Détecter un basculement automatique avec SQL Server 2012 AlwaysOn

Publié le par

Après avoir installé une infrastructure SQL Server 2012 AlwaysOn, il peut être intéressant d’être alerté lorsqu’un groupe de disponibilité basculement automatiquement d’un réplica à un autre pour une raison quelconque. Alors que pour les architectures en mirrroring nous avons built-in tout ce qu’il faut avec DATABASE_MIRRORING_STATE_CHANGE et des états en sortie qui nous permettent de savoir si un basculement manuel ou automatique a été effectué, c’est une autre paire de manche avec les infrastructures AlwaysOn. La documentation en ligne nous propose différentes outils comme  System Center, SQL Server Management Studio, les compteurs de performances ou encore Transact-SQL. Je rajouterai également PowerShell qui peut nous aider ici ou encore les outils tiers qui viendront compléter cette liste.

Voici une solution parmi d’autres. Pour ma part j’ai opté pour :

  • Une routine continue en T-SQL qui va chercher les changements d’états survenus sur les groupes de disponibilités présents sur un serveur sql. Si un changement est détecté un email est envoyé via databasemail aux personnes concernées
  • Un job SQL qui démarre et s’exécute lorsque le service de l’agent SQL Server démarre ou redémarre. Ce service est déjà monitoré par des outils tiers et si un problème survient au niveau de l’agent SQL l’information sera de toute façon remontée

 

icon_arrow Le code de la routine à intégrer

-- WORK TABLES
DECLARE @t_aag TABLE
(
   group_name SYSNAME NOT NULL,
   primary_replica VARCHAR(128) NOT NULL,
   primary_recovery_health NVARCHAR(80) NULL
);

DECLARE @t_aag_result TABLE
(
   [action] VARCHAR(50) NOT NULL,
   group_name SYSNAME NOT NULL,
   primary_replica_old VARCHAR(128) NULL,
   primary_replica_new VARCHAR(128) NOT NULL,
   primary_recovery_health NVARCHAR(80) NULL
);

INSERT @t_aag (group_name, primary_replica, primary_recovery_health)
SELECT [group_name], [primary_replica_new], [primary_recovery_health]
FROM [dbo].[dbi_alwayson_failover_logs];

WITH aag
AS
(
SELECT
    g.name AS group_name,
    primary_replica,
    primary_recovery_health_desc
    FROM sys.dm_hadr_availability_group_states AS ags
    JOIN sys.availability_groups AS g
    ON ags.group_id = g.group_id
)
MERGE dbo.dbi_alwayson_failover_logs AS t_aag
USING aag
ON aag.group_name = t_aag.group_name COLLATE Latin1_General_CS_AS_KS
WHEN matched AND aag.primary_replica != t_aag.primary_replica_new COLLATE Latin1_General_CS_AS_KS
THEN UPDATE SET event_time = CURRENT_TIMESTAMP,
                primary_replica_new = aag.primary_replica,
                primary_replica_old = primary_replica_new,
                primary_recovery_health = aag.primary_recovery_health_desc
WHEN NOT MATCHED BY TARGET
THEN INSERT VALUES (CURRENT_TIMESTAMP, aag.group_name, NULL, aag.primary_replica, aag.primary_recovery_health_desc)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action, inserted.group_name, deleted.primary_replica_new, inserted.primary_replica_new, inserted.primary_recovery_health
INTO @t_aag_result;

SELECT
    group_name,
    primary_replica_new
FROM @t_aag_result
WHERE [action] = 'UPDATE'


-- Reset work table
DELETE FROM @t_aag;
DELETE FROM @t_aag_result;
GO

 

Le script fonctionne de manière suivante :

  • 2 tables de travail @t_aag et @t_aag_result qui vont respectivement contenir la configuration des groupes de disponibilités présents sur le serveur SQL et le delta éventuel dans une configuration existante (action = update) qui impliquerait un basculement dans notre cas
  • Une boucle continue de détection de basculement avec une temporisation de 10 secondes
  • 1 table [dbo].[dba_alwayson_failover_logs] qui contiendra une ligne de données pour chaque  basculement de groupes de disponibilité détecté. Une procédure stockée dbo.dba.alwayson_mail se servira ensuite de cette table d’historique pour l’envoi des emails.
  • Une instruction MERGE utilisée ici pour mettre à jour en continue la configuration des groupes de disponibilités sur le serveur SQL concerné (les ajouts, les mises à jour et les suppressions de groupes de disponibilités) avec insertion d’une ligne de données en cas de mise à jour du réplica primaire pour un groupe de disponibilité (when matched and aag.primary_replica <> t_aag.primary_replica)

 

icon_arrow Le code de la table d’historique

CREATE TABLE [dbo].[dbi_alwayson_failover_logs](
    [event_time] [datetime] NOT NULL DEFAULT (getdate()),
    [group_name] [sysname] NOT NULL,
    [primary_replica_old] [VARCHAR](128) NULL,
    [primary_replica_new] [VARCHAR](128) NOT NULL,
    [primary_recovery_health] [nvarchar](80) NULL
) ON [PRIMARY]

 

Je ne mets pas ici volontairement le code la procédure d’envoi d’email car elle est plutôt spécifique à notre environnement de script. La logique est assez simple et consiste simplement à :

  • 1- parcourir la table d’historique [dbo].[dba_alwayson_failover_logs] et vérifier les lignes de données non traitées par la routine d’envoi d’email.
  • 2- Mettre à jour la valeur de colonne sent_by_email à 1 une fois l’email envoyé.

 

Et vous comment faites-vous ?

Bonne détection de basculement !

 

David BARBARIN (Mikedavem)
MVP SQL Server

2 réflexions au sujet de « Détecter un basculement automatique avec SQL Server 2012 AlwaysOn »

    • Bonjour Eric,

      La table [dbo].[dbi_alwayson_failover_logs] contient une ligne par groupe de disponibilité et celle-ci est mise à jour à chaque exécution du script et en fonction de la situation de chaque groupe de disponibilité à ce moment là.

      Il suffit de se servir de la requête à la fin du script pour savoir si un changement a été détecté par le scrip (action = UPDATE)

      SELECT
      group_name,
      primary_replica_new
      FROM @t_aag_result
      WHERE [action] = ‘UPDATE’

      Si cette requête renvoit au moins une ligne c’est qu’il y a changement. Après on peut imaginer un bout de script qui vérifie cela et qui envoie un mail en fonction avec sp_send_dbmail

      ++

Laisser un commentaire