[SQL Server] Database Mirroring : configuration d’une alerte mail en cas de basculement (et rebasculement automatique)

Ce billet explique comment configurer une alerte mail qui enverra des notifications en cas de basculement d’une base principale concernée par une session de database mirroring. Il donne également, en fin de billet, une astuce pour forcer un failback automatique en cas de basculement.

Contexte

Supposons que l’on souhaite créer une alerte mail qui envoie un message en cas de basculement (automatique ou manuel) d’une (ou plusieurs) base(s) de données concernée(s) par une session de database mirroring, via le SQL Agent.

Avant de se lancer dans l’aventure, assurez-vous que le Service Broker soit activé sur la base de données msdb, afin de pouvoir profiter des notifications WMI (Windows Management Instrumentation). Pour ce faire, la requête suivante peut être lancée :

ALTER DATABASE msdb SET ENABLE_BROKER
GO

Et pour la vérification :

SELECT name, is_broker_enabled
FROM sys.databases
WHERE name='msdb'
GO
Le contexte de travail sera SQL Server Management Studio (SSMS).

Modus operandi

Implémentation d’un compte Database Mail

L’intérêt du Database Mail est de pouvoir envoyer les messages d’alerte en cas de basculement. Pour comprendre comment configurer le Database Mail, voir ici.

Plus loin sera créé/ajouté un opérateur, qui servira de destinataire des messages d’alerte.

Création d’une alerte

Dans l’explorateur d’objets de SSMS, dépilez SQL Server Agent, puis double-cliquez sur Alerts (ou faites un clic-droit, puis New Alert…) :


  • Configuration de l’onglet General de l’alerte

Au sein de la fenêtre modale qui s’affichera, nommez l’alerte (i.e., Database Mirroring Failover Alert), changez le type d’alerte en WMI event alert, puis tapez la requête ci-dessous dans la zone Query :

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8

L’objet WMI DATABASE_MIRRORING_STATE_CHANGE permet de determiner l’état (State)
d’une ou plusieurs bases de données concernées par une session de database mirroring. Le tableau ci-dessous liste les valeurs de State, et leur signification (en gras, les états qui nous intéressent) :

Valeur

Description

0

Néant (principalement quand la session est en cours de démarrage).

1

Base synchronisée ; base principale avec témoin.

2

Base synchronisée ; base principale sans témoin.

3

Base miroir avec témoin.

4

Base miroir sans témoin.

5

Perte de connexion avec la base principale.

6

Perte de connexion avec la base secondaire.

7

Basculement manuel.

8

Basculement automatique.

9

Session suspendue.

10

Pas de quorum.

11

Base miroir en cours de synchronisation.

12

Session en mode synchrone, mais avec base principale dans l’incapacité d’interagir avec la base miroir.

13

Base principale en cours de synchronisation.

Pour de plus amples informations, vous pouvez faire un saut ici : http://msdn.microsoft.com/en-us/library/ms191502.aspx.

Niveau illustration, la configuration donnera :


A noter que vous pouvez également utiliser DatabaseName de DATABASE_MIRRORING_STATE_CHANGE pour filtrer les bases en fonction d’une ou plusieurs bases spécifiques à traiter.

  • Configuration de l’onglet Response.

Si l’opérateur ciblé n’existe pas déjà (ou aucun n’a été préalablement créé), cliquez sur New Operator (sinon, sur View Operator).

Cela aura pour effet d’ouvrir une fenêtre modale de création d’un nouvel opérateur (également ouvrable en faisant un clic-droit sur Operators du nœud SQL Server Agent de l’explorateur d’objets de SSMS, pour sélectionner New Operator…). Fenêtre au sein de laquelle doivent être spécifiés le nom de l’opérateur et le nom d’email, notamment :


Après validation, cochez l’option E-Mail pour l’opérateur ajouté, au sein de la fenêtre parente qui s’affichera :


  • Configuration de l’onglet Options.

Configurez le message d’alerte à envoyer par mail (et éventuellement les délais inter-réponses) :


Après validation, vous pouvez retrouver l’alerte (mais aussi l’opérateur) dans l’explorateur d’objets de SSMS :


Activation d’une session de messagerie pour le SQL Agent

Dans les propriétés du SQL Agent (accessibles à partir de l’explorateur d’objets, via clic-droit sur le nœud idoine), allez dans l’onglet Alert System, puis activez l’option d’utilisation d’une session de messagerie.

Comme le montre la figure ci-dessus, 2 informations comptent (en plus du remplacement de jetons pour toutes les réponses) : le système de messagerie à utiliser (Database Mail, dans notre cas) et le profil contenant le(s) compte(s) à utiliser pour communiquer avec le serveur SMTP (Mac Profile, par exemple, créé ici).

Pour aller plus loin…

Il est possible d’aller plus loin dans le modus operandi présenté dans ce billet. En effet, dans l’onglet Response de la fenêtre de création d’une nouvelle alerte, l’option Execute Job peut être cochée pour permettre à l’alerte de, en plus d’envoyer un mail, lancer un job SQL Agent (appelé, par exemple, FailbackMaBase) :


Ainsi, dans le cas du database mirroring, si l’on souhaite forcer automatiquement SQL Server à faire un failback en cas de basculement (inopiné) de la base principale MaBase, un job (i.e., FailbackMaBase, à planifier de façon très régulière) exécutant la requête suivante pourra être lancé par l’alerte :

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N'MaBase' AND mirroring_role_desc = 'PRINCIPAL')
 ALTER DATABASE MaBase SET PARTNER FAILOVER
GO
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s