SQL Server 2016 : groupes de disponibilités AlwaysOn sans contrainte de domaine

Mis en avant

Etes vous au courant des nouvelles fonctionnalités de la prochaine version de Windows? Actuellement nous sommes en TP3 et durant mes investigations, j’ai été heureux de voir les nouvelles fonctionnalités de cluster.

En lisant cet article Microsoft, nous pouvons voir:

Workgroup Clusters: Clusters with nodes which are member servers / workgroup (not domain joined)

> Pour lire la suite (en anglais)

David Barbarin
MVP & MCM SQL Server

SQL Server 2014 : basculement de groupes de disponibilités impossible avec le gestionnaire de clusters

Mis en avant

Il y a quelques semaines, je travaillais pour un client qui voulait implémenter une solution de haute disponibilité avec SQL Server 2012 AlwaysOn avec les groupes de disponibilités. Nous avons effectués une batterie de tests de basculement et le client a tenté de basculer les groupes de disponibilités installés au travers de la console de gestion de clusters. Bien entendu, je lui ai dit que cela n’était pas une bonne pratique parce que celui-ci n’était pas au courant de l’état de synchronisation d’un groupe de disponibilité. Mais avec SQL Server 2014, ceci a visiblement complétement changé de ce que j’ai pu constaté. Je voudrais partager cette information avec vous.

Pour lire la suite (en anglais)

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

SQL Server FCI et AlwaysOn et configuration quorum … de Windows 2008 à Windows 2012 R2

Mis en avant

Après quelques missions d’architecture sur l’implémentation d’architectures AlwaysOn chez divers clients il est temps de faire un point sur les possibilités de configuration du quorum du cluster Windows. En effet, pour rappel SQL Server AlwaysOn ou SQL Server FCI  se basent tous les 2 sur une couche Windows Failover Cluster Service (WFCS). Par conséquent le bon fonctionnement des groupes de disponibilités AlwaysOn ou des instances en cluster SQL reposent en quelque sorte sur celui du cluster Windows. Lorsqu’on parle de cluster Windows on arrive très vite à parler de quorum. La notion même de quorum n’est d’ailleurs pas uniquement associée au cluster. On peut la retrouver également lorsqu’on parle d’architecture en miroir avec SQL Server en mode synchrone et basculement automatique. Le but de ce billet est simplement de parcourir un peu les différentes possibilités de configuration du quorum avec WFSC à partir de 2008 R2 et de faire un focus sur les dernières innovations proposées par Windows Server 2012 et Windows Server 2012 R2.

 

icon_arrow Windows Server 2008 R2

Au fur et à mesure des versions Microsoft a fait évoluer ses modèles de quorum dans un processus continue d’amélioration de disponibilité du cluster Windows dans des scénarios de plus en plus divers. Par exemple on est passé de 2 modèles de quorum avec Windows 2003 à 4  à partir de Windows 2008. Windows 2008 a plus tard introduit la notion de paramétrage des poids de vote (avec l’application du KB2494036) qui peut s’avérer très utile dans des scénarios de type  “géo-cluster”. Cependant si le paramétrage du poids de vote se révèle très utile dans certains cas il n’en reste pas moins que celui-ci reste statique. En fonction de la situation c’est à l’administrateur de cluster de changer la configuration des poids de vote afin de garantir le quorum lors de la défaillances de nœuds.

L’image suivante montre 3 scénarios pour une architecture cluster Windows et pourquoi la nature statique du paramétrage du poids de vote peut poser problème :

 

quorum_1

 

Dans cette architecture le nœud 4 est sur un site distant. Comme nous ne voulons pas que ce nœud influence le vote du quorum en cas de coupure réseau entre les 2 sites nous pouvons lui supprimer la possibilité de voter dans l’établissement du quorum.

Scénario 1 :

Dans ce scénario il nous reste donc 3  nœuds qui peuvent voter (node 1, node 2 et node 3). Dans ce cas nous avons la possibilité d’utiliser le modèle de quorum “nœuds majoritaire” parce que le nombre de nœuds est impair ici. Dans cette configuration nous pouvons perdre jusqu’à 2 nœuds : le nœud 4 qui n’influence pas le vote du quorum via notre configuration et un des nœuds restants. Dans le cas où le nœud 4 aurait un vote nous ne pourrions perdre qu’un seul nœud.

Scénario 2 :

Dans ce scénario nous perdons le nœud 2 suite à une défaillance matérielle. Dans ce cas le cluster reste en ligne puisqu’il faut au minimum 2 nœuds pour effectuer le quorum. Cependant la perte d’un autre nœud risque à ce moment de compromettre la disponibilité du cluster. Dans ce cas l’administrateur du cluster Windows a 2 plusieurs possibilités :

  • Changer le quorum en ajoutant un témoin pour augmenter le nombre de votes possibles afin d’avoir la majorité en cas de défaillance d’un nœud
  • changer le nombre de votes possible à 1 seul nœud et en cas de défaillance du nœud 1 le cluster resterait en ligne (Dans notre exemple le nœud 3 perd sa possibilité de voter lors du quorum).

Scénario 3 :

Le nœud 2 a été réparé et est à nouveau opérationnel. Il faut de nouveau changer la configuration de poids de vote des nœuds pour augmenter la disponibilité du cluster en cas défaillance. En effet le vote étant ciblé uniquement sur un seul nœud, il devient le SPOF de cette topologie haute disponibilité. L’administrateur du cluster doit à nouveau activer le vote pour les nœuds 2 et 3 afin de garder en ligne le cluster Windows.

Pour résumer, le but du jeu ici est de jouer avec le poids de votes des nœuds tout en gardant à l’esprit qu’il faut garder plus 50% des votes pour garder le quorum en ligne !!

 

icon_arrow Windows Server 2012

Windows Server 2012 a changé la donne et a introduit le concept de quorum dynamique. Cette nouvelle fonctionnalité est intéressante car elle permet au cluster de recalculer à la volée le nombre de votes nécessaires pour que le cluster reste au maximum disponible. Ainsi il est possible d’avoir un cluster en ligne avec moins de 50% des nœuds restants.  A noter qu’il est toujours possible d’exclure des nœuds dans le vote du quorum avec un recalcul automatique qui ne tiendra compte uniquement que des nœuds restants.

En reprenant les scénarios décrits précédemment :

Scénario 1 :

quorum_2

On peut voir qu’il existe une nouvelle propriété appelée DynamicWeight. Le nœud 4 ne dispose pas de poids de vote (NodeWeight = 0) et par conséquent celui-ci ne sera pas pris en compte dans le calcul dynamique du quorum (dynamicweight=0) contrairement aux autres nœuds (NodeWeight et Dynamicweight = 1).

Scénario 2 :

quorum_3

On voit ici que le nœud 2 a subit une défaillance (State = Down) et que le calcul dynamique du quorum a été effectué par le cluster Windows. Par conséquent seul le nœud 3 possède un poids de vote ce qui permettra de garder en ligne le cluster en cas de défaillance d’un autre nœud de la topologie (nœud 1 ou 3).

quorum_4

L’arrêt du nœud 3 a déclenché à nouveau le recalcul du quorum par le cluster Windows. Ce dernier a transféré le poids de vote au seul nœud restant en ligne (nœud 1).

quorum_5

Les nœuds 2 et 3 ont été remis en ligne et à nouveau le cluster Windows a recalculé le poids de vote des nœuds pour le quorum.

Même si cette fonctionnalité présente des avantages certains, elle n’est cependant pas parfaite. En effet pour que le quorum se recalcule correctement, il faut que la défaillance des nœuds soit séquentielle dans le temps et que le recalcul du quorum ait eu le temps de s’effectuer. De plus l’introduction d’une ressource témoin avec Windows Server 2012 reste inchangé par rapport à Windows Server 2008 R2 à savoir que le témoin possède un poids de vote statique dans tous les cas ce qui diminue paradoxalement la disponibilité du cluster dans certains cas. Modifions notre architecture en y ajoutant un nœud supplémentaire de la façon suivante :

 

quorum_6

L’ajout d’un nœud pose ici un problème quant à l’établissement du quorum en cas de défaillance d’un des nœuds autre que nœud 5. Celui-ci comme dans l’architecture précédente n’est pas pris en compte dans le vote du quorum, ce qui laisse un nombre pair de nœuds qui peuvent voter. Dans cette configuration et sans quorum dynamique la défaillance de 2 nœuds ferait tomber à coup sûr l’ensemble du cluster Windows. En d’autres termes, la défaillance d’un seul nœud est supportée dans cette configuration. L’ajout d’une ressource témoin permet de pallier ce problème en ajoutant un vote supplémentaire afin d’augmenter le nombre de nœuds qui peuvent être défaillants à 2. Cependant avec l’utilisation du quorum dynamique, le recalcul se fera en tenant compte uniquement les nœuds du cluster restants en ligne. Le nombre de vote final sera donc composé de celui des nœuds restants + celui de la ressource témoin. Que se passera-t-il au moment où il restera un seul nœud en ligne et si l’on perd notre ressource témoin ? Malheureusement le quorum ne pourra être établi car il faudrait avoir au minimum 2 votes.

 

quorum_7

 

Ci-dessous un extrait de ce que l’on peut retrouver dans les logs du cluster Windows dans le cas où l’on perd une ressource témoin et par la suite le dernier nœud permettant au cluster Windows de rester en ligne.

Ici la ressource témoin est un dossier partagé qui n’est plus accessible ([RES] File Share Witness … Failed to create / Failed to validate etc …). Cependant le cluster peut encore rester en ligne puisque 2 nœuds sont encore présents pour faire le quorum (2 votes sur 3).

quorum_10

Cependant si l’on perd le dernier nœud (ici SQL122) comme montré ci-dessous …

quorum_11

… le début des problèmes commence puisque le cluster, malgré l’utilisation du quorum dynamique, se base encore sur le vote de la ressource témoin comme montré ci-dessous (File Share Witness is quorum and we’re one off quorum …). En effet à ce moment il n’existe plus qu’un seul vote sur 3 pour réaliser le quorum ce qui n’est plus suffisant. Après quelques secondes le cluster Windows

quorum_8

quorum_9

 

icon_arrow Windows Server 2012 R2

C’est ici que Windows Server 2012 R2 va avoir toute son utilité avec l’utilisation du témoin dynamique. Cette fonctionnalité permet de pallier au problème que nous venons de voir précédemment. Le vote du témoin est maintenant lui aussi dynamique et le cluster Windows recalcule le nombre de votes en fonction de la situation en tenant compte de ce nouvel élément.

L’image suivante montre la même situation que précédemment avec une topologie SQL Server 2014 décrite-dessus avec 5 nœuds dont un qui n’aura pas de vote pendant l’établissement du quorum. Au final nous avons 4 nœuds avec un poids de vote à 1.

image

On a également une ressource témoin de type FileShareWitness mais avec une nouvelle propriété WitnessDynamicWeight reflétant le caractère dynamique de la ressource témoin.

quorum_13

quorum_14

Que se passe-t-il si l’on perd la ressource témoin ?

quorum_15

On peut voir que la répartition des votes octroyés aux nœuds du cluster a changé dynamiquement. Premièrement le système a supprimé le vote de la ressource témoin et il a ensuite recalculé les votes de chaque nœud en supprimant celui octroyé au nœud 2 (SQL142). Cette nouvelle configuration prend en compte le fait que le nombre de nœuds restants pouvant voter est devenu pair et qu’il faut supprimer un vote à un des nœuds pour que celui-ci devienne impair.

quorum_16

En arrêtant successivement les nœuds 2 et 4 le quorum est de nouveau recalculé comme avec Windows 2012. Le nœud 3 est le seul à posséder un poids de vote car il possède le groupe de ressource système (nom réseau + IP du cluster Windows). Cela permet également en cas de défaillance du nœud 1 (ou éventuellement du nœud 3) de garder le cluster Windows en ligne

 

quorum_17

 

L’utilisation du témoin dynamique est également intéressante dans des configurations géo-clusters où le nombre de nœuds sur chaque site est équivalent. Prenons par exemple la configuration suivante :

quorum_18

Le cluster Windows possède 4 nœuds répartis par pair sur chaque site. Tous les nœuds du cluster peuvent voter et comme nous sommes dans une configuration où le nombre de nœuds par site est pair une ressource témoin a été ajouté sur le 1er site pour augmenter la disponibilité du cluster en cas de défaillance du lien réseau inter-sites. En effet sans cette ressource témoin, si une défaillance survenait sur ce lien réseau  nous pourrions être dans un cas de “split brain” avec l’apparition d’un partitionnement du cluster Windows en 2 parties. De plus le nombre de votes dans ce cas ne suffirait plus puisque la perte d’un seul nœud ne serait plus acceptable. Avec l’introduction du témoin nous éliminons du coup ce phénomène de “split brain” et nous augmentons en même temps le nombre global  de nœuds qui pouvant être défaillants soit 2 nœuds. Cependant la perte de cette ressource témoin en mode statique nous ramènerait au problème décrit un peu plus haut dans le billet . L’utilisation du témoin dynamique plus une nouvelle fonctionnalité de Windows Server 2012 R2 appelée “tie breaker” permet de garder le nombre de noeuds restants pouvant voter impair. C’est ce que nous pouvons voir ici après la perte du témoin :

quorum_19

Cependant si ce recalcul dynamique est bénéfique ici il serait intéressant aussi de pouvoir contrôler quels nœuds auront la priorité sur les autres pendant le recalcul. Dans notre cas nous voudrions par exemple garder un maximum de disponibilité sur le site A et donc donner la priorité aux serveurs correspondants. Heureusement pour nous il est possible de configurer des priorités par nœud avec la propriété de cluster  LowerQuorumPriorityNodeID.

quorum_20

quorum_21

On voit ici que l’assignement des poids de vote a changé en fonction de notre paramétrage.

image

Maintenant si nous perdons totalement le site 2 nous pourrons garantir que le cluster Windows sera encore en ligne.

Bonne configuration de quorum !!

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

Cas d’utilisation d’un groupe de disponibilité multi-listeners

Mis en avant

Pour ceux qui ont déjà installé SQL Server AlwaysOn vous avez pu sans doute remarquer qu’il est possible d’avoir uniquement un seul listener pour un groupe de disponibilité. Sur le principe cela n’est pas gênant dans la plupart des configurations mais comment dans le cas où un client cherche absolument à décomissionner un serveur SQL avec une multitude d’application et qu’il est difficile de changer le nom de serveur dans la chaîne de connexion de ces applications.

Prenons un cas concret que j’ai pu rencontrer chez un client lors d’une migration et qui aurait pu poser problème lors du passage à une architecture haute disponibilité SQL Server AlwaysOn :

  • Plusieurs applications installées sur une centaine de postes clients depuis longtemps et qui ne sont plus supportées par les différents éditeurs La chaîne de connexion des applications est codée en dur (pas de chance …) et l’intervention des différents consultants peut coûter cher d’autant plus que ces derniers proposent de mettre à jour l’application pour résoudre le problème. hors le client n’a pas forcément le budget pour cela car il n’a pas pu anticiper ce problème … bref le changement du nom de serveur pour cette application n’est pas envisageable et pourtant il nous faut migrer les bases de données pour décomissionner au plus vite notre ancien serveur SQL vers une infrastructure AlwaysOn

Quelles solutions avons-nous à disposition ?

–> Nous pouvons créer un listener avec l’ancien nom du serveur SQL et son ancienne adresse IP mais deux problèmes se posent :

  • le but est à l’origine est de pouvoir décomissionner l’ancien nom de serveur qui n’est plus conforme à la nouvelle nomenclature générale de nommage et doit être absolument changé dès que possible.
  • le planning de migration nous impose de migrer les applications en douceur et nous impose de garder de migrer les applications facilement modifiables au départ et garder les applications statiques pour la fin. Cette option n’est donc pas envisageable.

–> Nous pouvons utiliser un alias CNAME avec l’ancien nom de serveur qui pointe vers le nouveau listener. Cela suppose que notre ancien serveur soit complètement décomissionné pour pouvoir utiliser notre nouveau listener mais notre planning de migration nous impose encore une fois de garder l’ancien serveur pendant la phase de migration jusqu’à la fin. 

Dans ce cas cas comment faire ? He bien une solution (parmi quelques autres) serait de créer un second listener pour notre groupe de disponibilité. Comment ? En fait l’astuce consiste à passer par le gestionnaire de cluster et de créer un nouveau point d’accès client (Client Access Point) pour le rôle associé au groupe de disponibilité concerné avec un nom réseau et une adresse IP :

aag_new_cllient_access_point

aag_new_cllient_access_point_2

Une fois la ressource associé au nouveau point d’accès client créé il suffit de le mettre en ligne et de configurer les dépendances associées au groupe de disponibilité. L’utilisation d’une logique de dépendance à base de AND est possible mais il est préférable d’utiliser une logique de dépendance à base de OR pour garder le groupe de disponibilité en ligne autant que possible.

aag_new_cllient_access_point_3

Une fois le tout configuré on peut avoir apparaître un nouveau listener dans SQL Server Management Studio.

aag_new_cllient_access_point_4

L’utilisation de la vue système sys.availability_group_listeners nous montre également que le groupe aag possède bien 2 listeners :

image

La colonne is_conformant indique que le listener peut être managé depuis SQL Server. On voit également que pour le moment le listener n’est pas encore exploitable car aucun port d’écoute est configuré. Puisque le listener est configurable depuis SQL Server nous pouvons changer facilement son port depuis SQL Server Management Studio.

image

image

Un test de connexion et le tout fonctionne !!

image 

 

Merci à Qiang Wang pour l’astruce

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

Analyse des problèmes de basculement survenus sur une infrastructure SQL Server 2012 AlwaysOn

Mis en avant

Récemment j’ai eu à débugger un problème client survenu sur une infrastructure haute disponibilité SQL Server 2012 AlwaysOn. Le problème était le suivant : les connexions sur le réplica primaire ont subitement  cessé vers 12:25 et il n’était plus possible de s’y connecter. Le client, dans la précipitation, a visiblement essayer de basculer le groupe de disponibilité sur le réplica secondaire mais visiblement sans succès. Le but de mon intervention était d’expliquer à mon client pourquoi il n’était plus possible de se connecter aux réplicas et pourquoi le basculement n’a pas eu l’effet escompté. 

Par où commencer ? Analyser un problème sur une infrastructure AlwaysOn n’est pas une mince affaire en réalité. SQL Server 2012 AlwaysOn fonctionne sur une première couche basique de cluster à basculement au niveau du système d’exploitation et une couche applicative au niveau SQL Server. Lorsqu’un problème survient cela peut donc se produire sur l’une des 2 couches voir sur les 2 couches simultanément.

 

Les connexions se sont arrêtées subitement

Commençons dans l’ordre des problèmes. Dans le cas présent les connexions s’effectuent directement en utilisant le listener du groupe de disponibilité concerné. Cela peut vouloir dire que le problème se situe à ce niveau mais aussi au niveau de l’instance SQL Server. Pour cela nous avons à disposition :

  • les logs Windows ou du cluster à basculement Windows
  • les logs SQL Server
  • Les fichiers de diagnostics fournis par la procédure stockée sp_server_diagnostics

 

D’autres outils peuvent nous aider comme les sessions d’événements étendus AlwaysOn mais dans notre cas nous n’en aurons pas forcément besoin. J’essayerai d’effectuer un billet sur le sujet. Revenons à notre sujet : les logs Windows nous révèlent ceci :

billet_alwayson_diag_1

 

On voit ici une série d’erreur à partir de 12:20 relatives au cluster Windows à basculement :

  • Event ID 1562 : Cette erreur décrit un problème au niveau du témoin fichier partagé utilisé dans la configuration AlwaysOn qui nous concerné (2 nœuds + 1 FSW). Le partage servant comme élément de vote pour le quorum n’est plus accessible pour une raison non connue
  • Event ID 1069 : Cette erreur indique simplement qu’il existe un problème au niveau du groupe de ressource "Cluster Group". Dans notre cas cette erreur est provoquée par le partage qui n’est plus accessible.
  • Event ID 1564 : Cette erreur nous informe que le partage de fichier ne pourra pas jouer son rôle d’arbitrage en cas de défaillance d’un des nœuds de la topologie. En effet dans notre configuration le FSW n’étant plus disponible la perte d’un nœud de cluster supplémentaire serait fatale car la majorité ne pourrait pas être atteinte et le quorum ne pourrait pas être formé.

A ce stade on peut simplement conclure que le partage de fichier utilisé en tant que vote pour le quorum n’est plus accessible.

 

On retrouve également des erreurs de type NETLOGON qui nous en dit plus sur la nature de notre problème :

  • Event ID 5783 : Cette erreur indique qu’un problème de connexion aux serveurs DNS ou aux contrôleurs de domaine est survenu. Un redémarrage des serveurs ? Un problème de connexion de réseau entre le réplica primaire et les serveurs DNS ou DC ? Des serveurs ne pouvant plus répondre à cause d’un engorgement des demandes ? Une intervention sur l’infrastructure virtuelle sous-jacente ? Difficile à dire à ce stade … Ce qui est certain est que la résolution de nom ne pouvait pas s’effectuer correctement vers le partage de fichier dans ce cas, ce qui peut expliquer que le FSW n’était plus disponible à ce moment là

 

billet_alwayson_diag_13

 

L’analyse des logs SQL nous dévoilent ceci :

billet_alwayson_diag_2

 

Des informations intéressantes ici qui confirment bel et bien qu’un problème d’infrastructure a bien eu lieu avec une liaison à un contrôleur de domaine défaillante (No authority could be contacted for authentication).

L’erreur Windows nous spécifie la même chose :

billet_alwayson_diag_14

 

Une autre voix d’investigation est possible. Avec SQL Server 2012 AlwaysOn, la ressource DLL SQL Server utilisée par le cluster Windows à basculement exécute une procédure stockée sp_server_diagnostics pour déterminer l’état de santé du réplica primaire utilisé dans une topologie AlwaysOn. Des informations détaillées sont envoyés périodiquement au travers de cette ressource et permet au cluster Windows d’initier un basculement en fonction de ces informations et des règles de basculement associées. Toutes ces informations de diagnostics produites par la procédure stockée sp_server_diagnostics sont enregistrées par défaut dans des fichiers SQLDIAG exploitables directement par la fonction sys.fn_xe_file_target_read_file() ou directement dans SQL Server Management Studio.

SELECT
 xml_data.VALUE('(event/@name)[1]','varchar(max)') AS 'Name',
 xml_data.VALUE('(event/@package)[1]','varchar(max)') AS 'Package',
  xml_data.VALUE('(event/data[@name=''node_name'']/value)[1]','varchar(max)') AS 'Node_Name',
 xml_data.VALUE('(event/data[@name=''instance_name'']/value)[1]','varchar(max)') AS 'Instance_Name',
 xml_data.VALUE('(event/@timestamp)[1]','datetime') AS 'Time',
 xml_data.VALUE('(event/data[@name=''state'']/value)[1]','int') AS 'State',
 xml_data.VALUE('(event/data[@name=''state_desc'']/value)[1]','varchar(max)') AS 'State Description',
 xml_data.VALUE('(event/data[@name=''failure_condition_level'']/value)[1]','int') AS 'Failure Conditions',
 xml_data.VALUE('(event/data[@name=''creation_time'']/value)[1]','datetime') AS 'Creation Time',
 xml_data.VALUE('(event/data[@name=''component'']/value)[1]','varchar(max)') AS 'Component',
 xml_data.VALUE('(event/data[@name=''data'']/value)[1]','varchar(max)') AS 'Data',
 xml_data.VALUE('(event/data[@name=''info'']/value)[1]','varchar(max)') AS 'Info',
 xml_data
FROM (
SELECT
 object_name AS 'event'  ,
 CONVERT(xml,event_data) AS 'xml_data'  
 FROM sys.fn_xe_file_target_read_file('C:\customer_alwayson_issue\primary_replica\*.xel',NULL,NULL,NULL)
) AS XEventData
WHERE  xml_data.VALUE('(event/data[@name=''creation_time'']/value)[1]','datetime') > '20130723 12:15:00'
ORDER BY TIME;

 

billet_alwayson_diag_3

 

On remarque ici 2 choses. La procédure stockée sp_server_diagnostics stipule que le groupe de disponibilité qui nous concerne n’a visiblement pas de problème (premières flèches en rouge) . Les informations fournies ensuite par cette procédure stockée sont classés en catégorie (composants système, ressources, sous système disque, exécution des requêtes et autre évènements utiles notamment extraites des différents ring buffers SQL Server).

Concernant le composant de groupe de disponibilité :

billet_alwayson_diag_4

 

Concernant le composant Events :

billet_alwayson_diag_5

billet_alwayson_diag_6

 

On retrouve ici des informations du ring buffer "security error" dans lequel sont enregistrés les erreurs d’authentification. Celui-ci qui nous indique une erreur survenue lors de l’authentification via les api utilisés par SQL Server et d’autres informations que l’on retrouve dans les journaux d’erreurs Windows. Dans mon cas on retrouve une multitude d’enregistrements de ce genre dans ce ring buffer.

Pour résumer, nous avons donc ici l’origine du problème de connexion : un contrôleur de domaine ou un serveur DNS non joignable qui empêche les authentifications sur le réplica primaire. Passons maintenant à notre problème de basculement qui n’a visiblement pas fonctionné. Après investigation, c’est le processus de basculement manuel qui n’a pas fonctionné via l’interface GUI. En effet l’interface demande une authentification vers le réplica cible avant d’effectuer la bascule et comme le processus d’authentification ne fonctionnait pas avec des comptes de domaine il était alors impossible à ce moment là de basculer. Le serveur SQL a donc été redémarré. On peut le confirmer ici en regardant les informations contenues dans les journaux SQL Server que le service a été arrêté suite à une demande du gestionnaire de service.

 

billet_alwayson_diag_7

 

… Le gestionnaire de réplica démarre, attend que le service de cluster démarre correctement et démarre le réplica local. On voit que l’état du réplica passe de NOT_AVAILABLE à RESOLVING_NORMAL

 

billet_alwayson_diag_12

 

…On voit ici que le réplica local pour le groupe de disponibilité qui nous concerne se prépare à redevenir réplica primaire. L’état du réplica passe donc de RESOLVING_NORMAL à PRIMARY_PENDING

 

billet_alwayson_diag_8

… L’écoute démarre sur le listener associé au groupe de disponibilité  et le réplica passe à son état final PRIMARY_NORMAL.

 

billet_alwayson_diag_9

… Le réplica a bien redémarré mais on voit qu’il existe toujours des problèmes de connexion sur le réplica primaire à cause d’un problème de connexion à une autorité d’authentification (le fameux contrôleur de domaine).

 

billet_alwayson_diag_10

 

Pourquoi le groupe de disponibilité n’a-t-il pas pu basculer vers notre réplica secondaire ? Les règles de basculement étaient pourtant bien paramétré avec un niveau de condition par défaut (3). Les logs du cluster Windows sur le réplica secondaire nous donne la réponse :

billet_alwayson_diag_11

 

L’event id 1254 correspond à un nombre de basculement qui a dépassé le seuil autorisé pour un groupe de ressource. Dans notre cas le groupe de ressource correspond bien à notre groupe de disponibilité. Le basculement de ce dernier était donc impossible ici. C’est la raison pour laquelle le groupe de disponibilité a redémarré sur le réplica 1 après son redémarrage manuel.  La boucle est donc bouclée !!

 

Bonne investigation !

 

David BARBARIN (Mikedavem)
MVP SQL Server

Détecter un basculement automatique avec SQL Server 2012 AlwaysOn

Mis en avant

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