SQL Server 2012 | David Barbarin

SQL Server AlwaysOn : Considérations sur l’ajout d’un fichier de bases de données

Mis en avant

Publié le 18 janvier 2014 par mikedavem

Ce billet fait suite à quelques discussions échangés lors d’un cours sur SQL Server AlwaysOn que j’ai pu donner ces derniers temps. Une des parties du cours ciblaient certaines tâches qu’un administrateur pouvait avoir à effectuer sur une base de données concernée par un groupe de disponibilité dans un environnement complètement asymétrique. Je tiens à dire tout de suite que Microsoft conseille d’avoir des environnements similaires entre réplicas pour une administration plus simple. Maintenant que cela est dit, prenons une situation où un administrateur de bases de données doive ajouter un fichier de données supplémentaire à une base de données faisant parti d’un groupe de disponibilité dont l’architecture en place est la suivante :

Une architecture avec 4 réplicas. Je précise de suite que cette architecture est tout à fait fictive et que le placement de fichier pas forcément optimale pour une telle architecture mais le principe est de bien comprendre les problèmes qu’impliquent une telle asymétrie dans notre architecture. La convention de placement de fichier suivante : <LETTER>:\<SQLSERVER>\<INSTANCE>\<TYPEF_FICHIER>. Pour l’instance SQL141\IRONMAN pour les fichiers de données qui seront hébergés sur le disque E: nous aurons le placement suivant E:\SQLSERVER\IRONMAN\DATA.

L’ajout du fichier doit se faire en respectant la convention de placement en place. La question à 1 euros est-ce que je peux ajouter un fichier de données sans perturber l’architecture en place ?

…….

….

Pour le savoir rendez-vous dans la suite de ce billet !

La base de données concernée se nomme AGDB avec le schéma de répartition suivant :

Côté groupe de disponibilité voici ce que nous avons :

Ajoutons maintenant un fichier supplémentaire …

et voilà le résultat côté groupe de disponibilité :

… et côté instance :

Comme dirait l’autre, ça c’est fait !!! Bon reprenons nos esprits et regardons un peu ce qui se passe dans le journal des erreurs SQL Server d’une des instances SQL Server concernées :

On voit rapidement que le problème est la convention de placement des fichiers de données. En effet SQL Server tente de propager la création de fichier depuis le primaire vers les secondaires qui n’ont pas du tout les mêmes chemins de fichiers. Une erreur est levée et tant que le problème n’est pas réglé la réplication est suspendue.

Ok … que doit-on faire ici ? Réinitialiser le tout en supprimant la base de données AGDB du groupe de disponibilité et repartir d’une nouvelle sauvegarde en prenant soin de changer les chemins de fichiers ? Possible mais cela veut dire qu’il faut tout rependre … avec des bases de données de petite taille c’est jouable mais imaginez seulement une base de données de plus d’une centaine de Go, le tout multiplié par 3 car nous avons 3 réplicas secondaires … Une autre solution ? En fait oui .. on peut récupérer une sauvegarde de journal et la restaurer sur chacun des réplicas. Mais comment faire car il n’est pas possible d’appliquer des sauvegardes sur une base de données d’un groupe de disponibilités sur un réplica secondaire. En fait il faut supprimer la base de données du groupe de disponibilité sur chaque secondaire de la manière suivante :

Il se peut que des messages d’erreurs apparaissent stipulant un problème de création de fichiers sur un mauvais chemin mais on peut les ignorer. L’important est de pouvoir retrouver nos bases de données sur les réplicas secondaires en mode de restauration :

On peut maintenant appliquer une restauration du journal effectuée après avoir rencontré notre problème en veillant à restaurer notre fichier (AGDB2) qui pose problème vers le bon chemin :

et en ajoutant à nouveau la base de données au groupe de disponibilité pour le réplica concerné

… ainsi de suite pour les autres réplicas et le tour est joué :

… avec une répartition qui respecte la convention en vigueur :

Bien qu’il est possible de revenir à une situation pérenne plus ou moins facilement cela reste à mon avis fortement déconseillé car ce genre de configuration ne peut que compliquer l’administration courante d’un administrateur de bases de données. Ici nous avons ajouté un fichier de données mais que se passerait-il si nous devions ajouter en urgence un fichier journal en urgence dans un environnement SQL Server AlwaysOn complètement asymétrique ? …

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

Publié le 15 décembre 2013 par mikedavem

Répondre

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.

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 :

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 !!

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 :

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 :

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

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

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 :

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.

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

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

… 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

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.

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.

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

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.

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

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 :

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 :

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.

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

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

Publié le 6 décembre 2013 par mikedavem

Répondre

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

SQL Saturdays 2013 à Paris : les slides

Mis en avant

Publié le 7 octobre 2013 par mikedavem

Répondre

Comme promis voici les slides de ma session sur les événements étendus. Malheureusement je n’ai pas pu effectuer toutes les démonstrations que je voulais .. eh oui il faut bien commencer par expliquer comment fonctionne les événements étendus et cela m’a pris un peu plus de temps que prévu . Mais ce n’est que partie remise, je garde cette partie pour une session uniquement orientée démonstration. J’espère pouvoir vous la présenter rapidement.

Merci aux personnes qui ont voulu assister à ma présentation et de assister de manière générale à ce premier SQL Saturdays Paris. Ce fut un bon moment d’échange et de rencontres !

David BARBARIN (Mikedavem)
MVP SQL Server

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

Mis en avant

Publié le 2 septembre 2013 par mikedavem

Répondre

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.

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;

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

Détecter un basculement automatique avec SQL Server 2012 AlwaysOn

Mis en avant

Publié le 8 août 2013 par mikedavem

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

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)

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

Gérer les erreurs générées par la commande DBCC CHECKDB dans un script T-SQL

Mis en avant

Publié le 5 août 2013 par mikedavem

Répondre

La vérification d’intégrité des bases de données s’effectue par la commande DBCC CHECKDB. Le résultat de cette commande est assez verbeuse par défaut et peut poser problème lorsque la vérification d’intégrité est utilisé dans des scripts T-SQL personnalisés de maintenance par exemple.

Posons un peu le contexte et le pourquoi de ce billet. En réalité j’ai eu l’occasion de travailler (comme beaucoup je pense) sur la création de plans de maintenance "custom". L’idée a été de construire un jeu de script permettant de pouvoir gérer l’ensemble des tâches que l’on peut avoir à utiliser pour la maintenance des bases de données. Une de ces tâches concerne la vérification d’intégrité des bases. Cette vérification se fait à l’aide de la commande bien connue DBCC CHECKDB. Dans notre contexte l’idée est de pouvoir exécuter une tâche de maintenance et d’enregistrer dans une table de log les données suivantes :

Nom de la tâche
Nom de la base concernée
Durée de la tâche
Statut de la tâche (OK ou en échec)
Envoi par mail des tâches en échec à posteriori avec le détail de l’erreur

Ceci nous permet d’avoir une traçabilité des tâches de maintenance par la suite et pouvoir les consommer à des fins d’analyse.

Lorsque l’on scripte une vérification d’intégrité à l’aide de la commande DBCC CHECKDB on s’aperçoit rapidement que la gestion des erreurs liées à cette commande n’est pas triviale. C’est que nous verrons dans le cadre de ce billet.

Une sortie verbeuse

Voici la sortie d’une commande DBCC CHECKDB sur une base de données corrompue :

DBCC CHECKDB ('DemoCorruptMetadata')

DBCC results for ‘DemoCorruptMetadata’.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
DBCC results for ‘sys.sysrscols’.
There are 872 rows in 9 pages for object "sys.sysrscols".
DBCC results for ‘sys.sysrowsets’.
There are 124 rows in 1 pages for object "sys.sysrowsets".
DBCC results for ‘sys.sysclones’.
There are 0 rows in 0 pages for object "sys.sysclones".
DBCC results for ‘sys.sysallocunits’.
There are 138 rows in 2 pages for object "sys.sysallocunits".
DBCC results for ‘sys.sysfiles1′.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for ‘sys.sysseobjvalues’.
There are 0 rows in 0 pages for object "sys.sysseobjvalues".
DBCC results for ‘sys.syspriorities’.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for ‘sys.sysdbfrag’.
There are 0 rows in 0 pages for object "sys.sysdbfrag".
DBCC results for ‘sys.sysfgfrag’.
There are 0 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for ‘sys.sysdbfiles’.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for ‘sys.syspru’.
There are 0 rows in 0 pages for object "sys.syspru".
DBCC results for ‘sys.sysbrickfiles’.
There are 0 rows in 0 pages for object "sys.sysbrickfiles".
DBCC results for ‘sys.sysphfg’.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for ‘sys.sysprufiles’.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for ‘sys.sysftinds’.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for ‘sys.sysowners’.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for ‘sys.sysdbreg’.
There are 0 rows in 0 pages for object "sys.sysdbreg".
DBCC results for ‘sys.sysprivs’.
There are 136 rows in 1 pages for object "sys.sysprivs".
DBCC results for ‘sys.sysschobjs’.
There are 2180 rows in 29 pages for object "sys.sysschobjs".
DBCC results for ‘sys.syscolpars’.
There are 698 rows in 11 pages for object "sys.syscolpars".
DBCC results for ‘sys.sysxlgns’.
There are 0 rows in 0 pages for object "sys.sysxlgns".
DBCC results for ‘sys.sysxsrvs’.
There are 0 rows in 0 pages for object "sys.sysxsrvs".
DBCC results for ‘sys.sysnsobjs’.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for ‘sys.sysusermsgs’.
There are 0 rows in 0 pages for object "sys.sysusermsgs".
DBCC results for ‘sys.syscerts’.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for ‘sys.sysrmtlgns’.
There are 0 rows in 0 pages for object "sys.sysrmtlgns".
DBCC results for ‘sys.syslnklgns’.
There are 0 rows in 0 pages for object "sys.syslnklgns".
DBCC results for ‘sys.sysxprops’.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for ‘sys.sysscalartypes’.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for ‘sys.systypedsubobjs’.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for ‘sys.sysidxstats’.
There are 130 rows in 1 pages for object "sys.sysidxstats".
DBCC results for ‘sys.sysiscols’.
There are 317 rows in 3 pages for object "sys.sysiscols".
DBCC results for ‘sys.sysendpts’.
There are 0 rows in 0 pages for object "sys.sysendpts".
DBCC results for ‘sys.syswebmethods’.
There are 0 rows in 0 pages for object "sys.syswebmethods".
DBCC results for ‘sys.sysbinobjs’.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for ‘sys.sysaudacts’.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for ‘sys.sysobjvalues’.
There are 134 rows in 13 pages for object "sys.sysobjvalues".
DBCC results for ‘sys.syscscolsegments’.
There are 0 rows in 0 pages for object "sys.syscscolsegments".
DBCC results for ‘sys.syscsdictionaries’.
There are 0 rows in 0 pages for object "sys.syscsdictionaries".
DBCC results for ‘sys.sysclsobjs’.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for ‘sys.sysrowsetrefs’.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for ‘sys.sysremsvcbinds’.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for ‘sys.sysxmitqueue’.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for ‘sys.sysrts’.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for ‘sys.sysconvgroup’.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for ‘sys.sysdesend’.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for ‘sys.sysdercv’.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for ‘sys.syssingleobjrefs’.
There are 156 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for ‘sys.sysmultiobjrefs’.
There are 107 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for ‘sys.sysguidrefs’.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for ‘sys.sysfoqueues’.
There are 0 rows in 0 pages for object "sys.sysfoqueues".
DBCC results for ‘sys.syschildinsts’.
There are 0 rows in 0 pages for object "sys.syschildinsts".
DBCC results for ‘sys.syscompfragments’.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for ‘sys.sysftsemanticsdb’.
There are 0 rows in 0 pages for object "sys.sysftsemanticsdb".
DBCC results for ‘sys.sysftstops’.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for ‘sys.sysftproperties’.
There are 0 rows in 0 pages for object "sys.sysftproperties".
DBCC results for ‘sys.sysxmitbody’.
There are 0 rows in 0 pages for object "sys.sysxmitbody".
DBCC results for ‘sys.sysfos’.
There are 0 rows in 0 pages for object "sys.sysfos".
DBCC results for ‘sys.sysqnames’.
There are 100 rows in 1 pages for object "sys.sysqnames".
DBCC results for ‘sys.sysxmlcomponent’.
There are 100 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for ‘sys.sysxmlfacet’.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for ‘sys.sysxmlplacement’.
There are 19 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for ‘sys.sysobjkeycrypts’.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for ‘sys.sysasymkeys’.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for ‘sys.syssqlguides’.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for ‘sys.sysbinsubobjs’.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for ‘sys.syssoftobjrefs’.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for ‘sys.queue_messages_1993058136′.
There are 0 rows in 0 pages for object "sys.queue_messages_1993058136".
DBCC results for ‘sys.queue_messages_2025058250′.
There are 0 rows in 0 pages for object "sys.queue_messages_2025058250".
DBCC results for ‘sys.queue_messages_2057058364′.
There are 0 rows in 0 pages for object "sys.queue_messages_2057058364".
DBCC results for ‘sys.filestream_tombstone_2089058478′.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2089058478".
DBCC results for ‘sys.syscommittab’.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for ‘sys.filetable_updates_2121058592′.
There are 0 rows in 0 pages for object "sys.filetable_updates_2121058592".
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

On se rend vite compte que la quantité d’information qui nous intéresse ici reste négligeable par rapport à la quantité totale d’information produite en sortie par notre commande. Ici la commande DBCC CHECKDB a produit 162 lignes d’information alors que moins de 10 lignes nous intéresse ici. Heureusement pour nous, nous pouvons jouer avec les options NO_INFOMSGS et ALL_ERRORMSGS pour limiter le nombre d’informations produites par DBCC CHECKDB. Pour rappel NO_INFOMSGS permet d’éliminer les messages d’information produites en sortie et ALL_ERRORMSGS permet d’afficher toutes les erreurs rencontrées lors d’une opération de vérification de bases de données. Par défaut seulement les 1000 premiers messages d’erreur sont affichés depuis SQL Server Management Studio.

Utilisation des options NO_INFOMSGS, ALL_ERRORMSGS

DBCC CHECKDB ('DemoCorruptMetadata') WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.

On a ici bien réduit le nombre de lignes produites par notre commande DBCC CHECKDB avec l’affichage du détail des erreurs rencontrées et 2 messages d’information qui ne sont ni plus ni moins qu’un récapitulatif de ces erreurs.

Gestion de la sortie de la commande DBCC CHECKDB dans un script T-SQL

Après avoir limité le nombre d’information utiles comment nous en servir pour construire une logique de maintenance par script ? L’idée ici est simplement de pouvoir détecter un problème d’intégrité et de prévenir les personnes concernées (notamment l’équipe DBA) qu’un problème est survenu à ce moment là

A priori 2 voies d’exploitation évidentes peuvent être prises en compte :

On peut utiliser la variable @@ERROR et vérifier que celle-ci soit différente de 0
On peut utiliser les blocs de gestions d’erreur TRY CATCH

Avec la variable @@ERROR :

dbcc checkdb('DemoCorruptMetadata') WITH no_infomsgs, all_errormsgs;
IF @@ERROR <>0
BEGIN
print '';
print '--> error during dbcc checkdb';
END

Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.

–> error during dbcc checkdb

La variable @@ERROR nous permet effectivement de pouvoir détecter les erreurs engendrées par la commande DBCC CHECKDB. Il suffit ensuite de coder la logique de maintenance qui nous intéresse. Cependant si on utilise notre script T-SQL dans un job SQL Server, les erreurs produites impliqueront une étape de job en échec. La question est donc de savoir ce résultat est attendu ou non ? Effectivement ce n’est pas parce qu’une commande DBCC CHECKDB produit une erreur qu’un job SQL Server doit forcément partir en échec. En réalité tout dépendra de la logique d’exécution globale concernée par ce script. On peut tout à fait traiter l’erreur de job et effectuer une surveillance sur l’état de ce job par envoi d’alerte ou via des outils tiers par exemple. On peut aussi vouloir enregistrer l’erreur sans que le job parte en échec, avec l’exécution d’étapes supplémentaires sans pour autant avoir une logique d’exécution complexe du style "si étape 1 échec –> aller à l’étape 3 sinon aller à l’étape 2 etc … A mon humble avis les jobs SQL Server ne sont pas vraiment désignés pour gérer des logiques complexes de workflow . Dans le cadre de notre script nous avons retenu une solution générique et simple : si une tâche génère une erreur alors celle-ci est enregistrée dans une table de log et l’information pourra être envoyé à posteriori par email. Le choix est laissé à l’utilisateur final.

Avec le bloc de gestion d’erreur BEGIN TRY, CATCH :

BEGIN try
dbcc checkdb('DemoCorruptMetadata') WITH no_infomsgs, all_errormsgs;
END try
BEGIN catch
print '--> error during dbcc checkdb';
END catch

CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘DemoCorruptMetadata’.

Comme on peut le remarquer le bloc de gestion TRY , CATCH ne va malheureusement pas nous être d’une grande utilité ici car les dernières lignes d’information produites par la commande DBCC CHECKDB empêchent notre bloc de gestion d’erreur de détecter les erreurs produites par notre commande DBCC CHECKDB quelques lignes plus haut.

Avons nous une autre alternative dans ce cas ? La réponse est oui. Il est également possible d’enregistrer les erreurs produites lors d’une vérification d’intégrité dans une table SQL en utilisant l’option TABLERESULTS de cette manière :

Utilisation de l’option TABLERESULTS

DBCC CHECKDB('DemoCorruptMetadata')
WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS

Il suffit ici d’extraire les informations produites dans la table et de gérer sa propre logique de maintenance. Dans mon cas je remplis une variable de table avec le résultat de la commande DBCC CHECKDB et l’option TABLERESULETS et je vérifie qu’au moins une ligne existe pour détecter la présence d’une erreur (la commande DBCC CHECKDB ne produit aucune ligne d’erreur si aucun problème d’intégrité n’est détecté). L’information est ensuite enregistrée dans une table de log avec le numéro d’erreur et sera envoyé par mail à postériori.

L’option TABLERESULTS est-elle là solution miracle ? Malheureusement non car la commande DBCC CHECKDB peut produire certaines erreurs propres à son exécution et qui stopper franchement le script en cours d’exécution. C’est le cas de certaines erreurs sévères rencontrés par la commande DBCC CHECKDB.

Gestion des erreurs liées à l’exécution même de la commande DBCC CHECKDB

Voici quelques cas où l’exécution de la commande DBCC CHECKDB provoquera une erreur grave et un arrêt net du script T-SQL en cours d’exécution.

- Après avoir corrompu la page PFS (page id = 1) à l’aide d’un éditeur hexadécimal de ma base DemoCorruptMetada le script SQL se stoppera net avec le message d’erreur suivant :

Msg 0, Level 11, State 0, Line 0
A severe error occured on the current command. The results, if any, should be discarded

- De la même manière Après avoir supprimer une ligne dans la table système sys.syscolpars en redémarrant l’instance SQL Server en mode mono utilisateur et se connectant en mode administrateur dédié la commande DBCC CHECKDB génère le message suivant :

Msg 0, Level 20, State 0, Line 0
A severe error occured on the current command. The results, if any, should be discarded

Pour une description des niveaux d’erreurs voir la documentation en ligne SQL Server (section "Understanding DBCC errors messages"). Dans ce cas il faudra effectivement gérer l’erreur de manière différente. Dans mon cas au début de chaque tâche une ligne est enregistrée dans une table de log. Si le script T-SQL est arrêté a date de fin de la tâche ne sera pas renseigné. Un email est envoyé pour les cas suivants :

Le statut de la tâche est égale à false
Le statut de la tâche est inconnu ou la date de fin d’une tâche n’est pas renseignée

Bonne gestion d’erreur !!

David BARBARIN (Mikedavem)
MVP SQL Server

Considération sur les listeners associées aux groupes de disponibilités avec SQL Server 2012 AlwaysOn

Mis en avant

Publié le 2 juillet 2013 par mikedavem

Répondre

Lorsqu’on crée un groupe de disponibilité avec SQL Server et AlwaysOn on l’associe par la suite à un listener. Lorsque ce dernier est créé plusieurs objets lui sont associées :

Un objet d’ordinateur dans l’annuaire active directory
Une entrée DNS de type A
Une ressource cluster associée à au groupe de ressource créé et qui représente le groupe de disponibilité

Dans l’exemple suivante un groupe de disponibilité nommé AdventureWorks2 a été créé. Le listener associé s’appelle LST_ADVW2

Les différents objets associés

L’objet d’ordinateur dans l’annuaire :

… l’enregistrement DNS :

… Le groupe de ressource lié au groupe de disponibilité :

… et les ressources qui le composent :

Jetons un coup d’œil aux dépendances existantes entre les ressources du groupe de ressource AdventureWorksGrp2 :

Ici on vient bien les dépendances existantes : le groupe de disponibilité AdventureWorksGrp2 dépend de la ressource "nom réseau" qui représente notre listener (LST_ADVW2) qui elle même dépend de la ressource "Adresse IP" qui vous l’aurez devinez, correspond à l’adresse IP de notre listener.

Port d’écoute du listener

Est-ce qu’un listener doit écouter sur le même port que les réplicas du groupe disponibilité ? Est-ce possible ? C’est une question qui s’est posé rapidement dans mon cas lorsqu’il a fallu installer des réplicas n’écoutant pas sur le port qui n’écoutaient pas sur le port par défaut (1433). On peut même aller à l’extrême et prendre le cas où chaque réplica écoute sur un port différent. Dans ce cas quel port dois-je paramétrer pour mon listener ? Un listener peut tout à fait écouter sur un port différent que les réplicas. Le choix du numéro de port dépend évidement de vos besoins, contraintes de sécurité etc … Prenons un exemple avec 2 réplicas qui écoutent sur 2 ports différents (1499 et 1500) et un listener qui écoute sur le port 1433.

[AlwaysOn Instance]

Cette configuration fonctionne parfaitement. Par curiosité on peut voir ce que cela donne sur une trace réseau via NetMon par exemple.

On peut voir ici une connexion depuis SQL Server Management Studio vers le listener lst_advw.insentia.lab (IP virtuelle : 192.168.0.61)

Dans notre cas le listener écoute sur le port 1433 et la connexion est automatiquement redirigée vers le réplica concerné en toute transparence. Au niveau de la chaine de connexion l’impact est minime : le nom du listener remplace tout simple le nom de l’instance SQL Server. Cependant que se passe-t-il maintenant si nous changeons le port d’écoute du listener vers un port non standard ? En réalité les choses se compliquent un peu et il existe plusieurs cas :

On spécifie le numéro de port directement dans la chaine de connexion
On a la possibilité d’utiliser le format suivant : [nom listener]\[nom instance] sous certaines conditions :
- On utilise des instances nommées qui portent le même nom pour l’ensemble des réplicas
- Le service SQL Browser est démarré et opérationnel sur l’ensemble des réplicas pour effectuer la résolution de nom de l’instance vers le numéro de port adéquat.

Cas de la redirection vers un réplica secondaire en lecture seule

AlwaysOn nous fournit la possibilité d’être redirigé vers un réplica secondaire en lecture seule en se connectant via le listener à condition que les règles de redirection aient été définies (routes et priorités). Dans ce cas une trace NetMon nous révèle la chose suivante :

On peut constater ici qu’après la phase d’authentification la connexion de SQL Server Management Studio initialement effectuée vers le listener lst_advw.insentia.lab (port 1433) est redirigé vers le réplica secondaire en lecture seule REPLICA2 (port 1498)

Kerberos, SPN et redirection vers un réplica secondaire en lecture seule

La documentation en ligne Microsoft stipule que pour utiliser Kerberos dans une architecture AlwaysOn il faut ajouter les SPN correspondants à nos listeners. Cependant je n’ai pas trouvé dans la documentation le cas où le listener effectuait une redirection d’une connexion en lecture seule vers un réplica secondaire en lecture seule. Dans ce cas ajouter les SPN pour les listeners ne suffit pas et l’on doit également ajouter les réplicas secondaires en lecture seule.

Voici une trace netmon d’une tentative de connexion en erreur à un réplica secondaire avec Kerberos :

On peut voir ici 2 requêtes TGS depuis ma station cliente (192.168.0.100) vers mon contrôleur de domaine (192.168.0.1) :

TGS request vers le service MSSQLSvc/lst_advw.insentia.lab (port 1433) : listener
TGS request vers le service MSSQLSvc/replica2.insentia.lab (port 1498) : réplica secondaire en lecture seule

Le contrôleur de domaine répond avec une erreur pour la deuxième demande car il n’y a pas de SPN paramétré pour REPLICA2 dans mon cas (KDC_ERR_S_PRINCIPAL_UNKNOW)

Listeners vs alias (DNS / SQL) ?

Une question intéressante surtout lorsqu’on a l’habitude de lier le point de connexion des applications via un alias DNS ou SQL par exemple. Ainsi lorsqu’on change de serveur de bases de données avec un nouveau nom, il suffit d’utiliser de modifier l’alias en conséquence. Le changement est donc relativement transparent pour les applications. Avec SQL Server AlwaysOn, lorsqu’un un listener est créé nous avons vu qu’une entrée DNS est également effectuée. De plus on a la possibilité d’associer plusieurs listeners à un seul groupe de disponibilité ce qui peut tout à fait remplacer les alias. Bien entendu chaque méthode présente des avantages mais aussi des inconvénients. Goden Yao (Program Manager , SQL Server Engine High Availability) a écrit un billet intéressant à ce sujet.

Bon paramétrage de listeners !!

David BARBARIN (Mikedavem)
MVP SQL Server

SQL Server 2012 AlwaysOn et plans de maintenance par défaut

Mis en avant

Publié le 13 juin 2013 par mikedavem

Lors de ma session GUSS du 23 avril 2013 à propos de AlwaysOn on m’a demandé si les plans de maintenance SQL Server pouvaient être utilisés dans ce contexte. J’ai répondu négativement à cette réponse sans pour autant être vraiment explicite. Pourtant lorsqu’on créé un plan de maintenance avec SQL Server 2012 et qu’on utilise une tâche de sauvegarde des bases on peut s’apercevoir que si une base de données est concernée par un groupe de disponibilité une option apparait automatiquement "For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings". L’apparition de cette option implique que par défaut les plans de maintenance SQL Server ou du mois les tâches de sauvegarde sont en théorie capables de s’aligner sur la politique de sauvegarde dictée par le groupe de disponibilité en question mais malgré ce je persiste à dire que les plans de maintenance par défaut ne sont pas adaptés (du moins encore) aux environnements AlwaysOn et c’est ce que je vais tenter d’expliquer dans ce billet.

Des tâches de sauvegardes qui gèrent et prennent en compte les préférences de sauvegarde d’un groupe de disponibilité

Commençons par le début. Effectivement les tâches de sauvegardes des plans de maintenance par défaut détectent l’appartenance ou non d’une base de données à un groupe de disponibilité.

J’ai créé une base de données DUMMY faisait parti du groupe de disponibilité

J’ai paramétré mes préférences de sauvegarde de la manière suivante :

Dans un plan de maintenance par défaut je glisse une tâche de sauvegarde de type FULL en choisissant la base de données DUMMY …

… et on s’aperçoit qu’une option supplémentaire apparait

Cette option permet de ne pas tenir compte des préférences de sauvegarde configurés dans les groupes de disponibilités pour un réplica. Par défaut cette option n’est pas activée et la tâche de sauvegarde prendra en compte les préférences de sauvegardes des groupes de disponibilités (de préférence sur les secondaires, uniquement sur les secondaires, uniquement sur le primaire, sur n’importe quel réplica).

On peut voir que la commande T-SQL générée utilise la fonction sys.fn_hadr_backup_is_preferred_replica() pour la base de données DUMMY

Notez également que j’ai activé l’option COPY_ONLY pour les sauvegardes. En effet comme ma politique de sauvegarde stipule que les réplicas secondaires seront utilisés en priorité, seules les sauvegardes avec l’option COPY_ONLY fonctionneront si le réplica concerné sera secondaire au moment de la sauvegarde. Mon plan de maintenance fonctionne parfaitement même après test de failover d’un réplica vers un autre. Cependant comme vous le savez certainement un plan de maintenance n’est jamais aussi simple.

Restons sur notre tâche de sauvegarde et ajoutons-y quelques bases de données utilisateurs en mode de récupération FULL et qui ne font pas parti d’un groupe de disponibilité et c’est la que le premier problème arrive. Ma politique de sauvegarde de type full se fait toujours avec l’option COPY_ONLY dans mon cas puisque mes sauvegardes se feront en priorité sur le secondaire et que ceux-ci ne supportent que les sauvegardes avec l’option COPY_ONLY. Le problème est qu’ici la création d’une base de données utilisateur en mode de récupération FULL fera planté la tâche de sauvegarde des journaux de transactions. Pourquoi ? Tout simplement parce qu’une sauvegarde FULL avec l’option COPY_ONLY ne va pas initialiser la séquence des LSN utiles pour nos sauvegardes des journaux de transactions. Alors bien entendu je peux créer 2 plans de maintenance par exemple :

Un plan de maintenance qui concernera uniquement les bases de données en mode de récupération FULL associées à un groupe de disponibilité
Un plan de maintenance qui concernera les autres bases de données

Il faudra dans ce cas répartir manuellement les bases de données dans les différents plans de maintenance. On perd ici toute notion de gestion dynamique des plans de maintenance selon moi. Si j’ajoute une base de données par exemple, je n’ai pas forcément envie de modifier ensuite mon plan de maintenance … Bien entendu ce raisonnement ici ne tient que pour les cas où les sauvegardes sont déportés sur le secondaire. Si les sauvegardes sont effectuées uniquement sur le primaire, l’utilisation de l’option COPY_ONLY n’a plus lieu d’être.

Une autre chose à noter ici est le manque de support de l’option CHECKSUM qui permet pour rappel d’une part de vérifier le checksum de chaque page de données sauvegardée d’une base de données (si l’option est bien entendu activée au niveau de celle-ci) et de générer un checksum global pour le média de sauvegarde.

La tâche de vérification d’intégrité

Vérifier l’intégrité de ses bases de données avant de les sauvegarder est plutôt une opération recommandée. Mais dans une architecture AlwaysOn on peut se poser la question de savoir où est-ce que je dois lancer cette tâche. Si dans d’autres environnements le choix est plutôt évident car les sauvegardes ne peuvent se faire que sur le serveur "principal" , le raisonnement n’est pas tout à fait le même quand on parle de déplacement des sauvegardes sur un réplica secondaire. En effet, vu que ma politique de sauvegarde gère des préférences et des priorités de réplicas pour les sauvegardes ces dernières peuvent être lancés de n’importe où en fonction de la situation présente et des paramètres de configuration. En plus mes bases de données sont accessibles en lecture écriture depuis un réplica primaire et peuvent être inaccessible depuis mes les réplicas secondaires. Il faut également que je vérifie l’intégrité de ma base à ce niveau. Cela fait potentiellement 2 points de vérification d’intégrité des bases sur n réplicas (n pouvant aller jusqu’à 5 pour le moment mais avec SQL14 on pourra monter jusqu’à 8 !).

Si on regarde de plus près la tâche de vérification d’intégrité des plans de maintenance par défaut de SQL Server on s’aperçoit qu’il n’y a aucun paramètre de gestion des groupes de disponibilités :

Dans ce cas comment puis-je faire pour lancer ma tâche ? On peut prendre le cas extrême qui dit qu’il faut lancer cette tâche de vérification des bases sur tous les réplicas .. et pourquoi pas après tout ?

Je vois déjà 2 problèmes à cela :

Même si la vérification d’intégrité initiée par la commande DBCC CHECKDB ne génère pas de verrou par défaut (utilisation des snapshots de bases de données) celle-ci peut avoir un impact négatif sur les performances IO et sur l’augmentation de la REDO queue sur les réplicas secondaires. Une augmentation de cette file d’attente sur les secondaires peuvent avoir un impact sur le RPO et RTO en fonction du type de réplication choisi.
On peut tout à fait utiliser un réplica secondaire pour faire des sauvegardes et second réplica secondaire en standby uniquement pour effectuer du failover ou dans une procédure de disaster recovery. Dans ce cas la tâche de vérification d’intégrité des bases des plans de maintenance par défaut SQL Server étant incapable de savoir si la base de données concernée fait parti ou non d’un groupe de disponibilité et si la base de données en question est accessible en lecture seule ou non provoquera une erreur dans le plan de maintenance en fonction de la situation

Les tâches de mise à jour des statistiques et des indexes

On a exactement le même problème ici. En regardant les tâches du plan de maintenance associées aux statistiques et aux indexes il n’y a aucun paramètre de configuration lié aux groupes de disponibilité. Dans une architecture AlwaysOn on ne peut mettre à jour les indexes et les statistiques d’une base que sur le réplica primaire. Je peux également avoir des bases de données qui ne sont pas concernées par un groupe de disponibilité. Dans ce cas il est tout à fait possible qu’un réplica secondaire d’un groupe de disponibilité soit l’instance principale d’une autre base de données. Je peux effectivement créer un plan de maintenance spécifique pour cela et activer / désactiver les jobs SQL Server associées en fonction du cas mais encore une fois cela m’oblige à gérer manuellement les bases de données à répartir dans les plans de maintenance …

De plus dans ce genre d’architecture l’utilisation des tâches par défaut de maintenance des indexes ne sont pas forcément conseillés car elles peuvent être excréments couteuse en écriture journalisée et de bande passante pour la réplication de ces écritures vers les autres réplicas. En effet, et beaucoup d’articles l’expliquent déjà, il n’y a pas la possibilité de définir des seuils de réorganisation ou de reconstruction d’indexes. La politique du tout ou rien ici n’est pas forcément très approprié.

Pas besoin d’aller plus loin je pense pour vous dire que les plans de maintenance SQL Server ne sont pas adaptés pour SQL Server 2012 AlwaysOn. Que fais-t-on dans ce cas là ? Pour le moment pour ma part j’ai créé un jeu de script de maintenance des bases de données pour des architectures AlwaysOn installées chez nos clients. Je n’ai pour le moment pas trouvé d’outil tiers permettant de gérer la maintenance des bases de données de A à Z pour ce type d’environnement mais je reste à l’écoute si jamais vous avez plus d’information là dessus Sourire

Bonne maintenance de vos architectures AlwaysOn !

David BARBARIN (Mikedavem)
MVP SQL Server

Webcast sur SQL Server 2012 AlwaysOn pour les fermes Sharepoint 2013 : les slides et la vidéo sont disponibles

Mis en avant

Publié le 12 juin 2013 par mikedavem

Répondre

Un billet en retard pour dire que les slides et la vidéo de la session GUSS que j’ai eu l’occasion d’animer sur SQL Server 2012 AlwaysOn et SharePoint 2013 sont disponibles sur le site du GUSS

>> http://guss.fr/2013/06/01/webcast-guss-avril-2013-les-bases-alwayson/

David BARBARIN (Mikedavem)
MVP SQL Server

Identifiez-vous

Créer un compte

David Barbarin

MVP DataPlatform – MCM SQL Server

Archives pour la catégorie SQL Server 2012

SQL Server AlwaysOn : Considérations sur l’ajout d’un fichier de bases de données

Mis en avant

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

Mis en avant

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

Mis en avant

SQL Saturdays 2013 à Paris : les slides

Mis en avant

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

Mis en avant

Détecter un basculement automatique avec SQL Server 2012 AlwaysOn

Mis en avant

Gérer les erreurs générées par la commande DBCC CHECKDB dans un script T-SQL

Mis en avant

Considération sur les listeners associées aux groupes de disponibilités avec SQL Server 2012 AlwaysOn

Mis en avant

SQL Server 2012 AlwaysOn et plans de maintenance par défaut

Mis en avant

Webcast sur SQL Server 2012 AlwaysOn pour les fermes Sharepoint 2013 : les slides et la vidéo sont disponibles

Mis en avant

Identifiant
Mot de passe

Mot de passe oublié ?