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 :

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.

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

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

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.

…

Un test de connexion et le tout fonctionne !!

Merci à Qiang Wang pour l’astruce

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

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 :

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à

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

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 :

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.

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é :

Concernant le composant Events :

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.

… 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

…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

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

… 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).

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 :

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