Columnstore index rebuild cannot start

Mis en avant

Il y a quelques semaines,j’étais en charge de migrer une infrastructure Datawarehouse sur une version plus récente de SQL Server (passage de 2005 à 2012). Après discussion avec le client nous avons décidé de profiter des nouvelles fonctionnalités offertes par 2012 et en particulier des index columnstore qui pouvaient potentiellement améliorer les performances de Reporting dans le contexte présent …

> Lire la suite (en anglais)

David Barbarin
MVP & MCM SQL Server

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

Mis en avant

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 :

 

image

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 :

image 

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

image

 

Ajoutons maintenant un fichier supplémentaire …

image

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

image

… et côté instance :

image

 

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 :

image

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 :

image

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 :

image

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 :

image

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

image

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

image

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

image

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

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

SQL Saturdays 2013 à Paris : les slides

Mis en avant

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

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

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

Mis en avant

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

 

alwayson_aag_lst

 

icon_arrow Les différents objets associés

 

L’objet d’ordinateur dans l’annuaire :

alwayson_ad_object

 

… l’enregistrement DNS :

alwayson_dns_record

 

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

alwayson_cluster_resource

 

… et les ressources qui le composent :

alwayson_cluster_resource2

 

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

alwayson_cluster_dependencies

 

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.

 

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

alwayson_netmon0

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.

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

alwayson_netmon1

 

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)

 

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

alwayson_spn_netmon

 

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)

 

icon_arrow 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

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.

 

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

image

 

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

image

 

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 …

image

 

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

image

 

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

image

 

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.

 

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

image

 

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

 

icon_arrow 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

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/

 

guss

David BARBARIN (Mikedavem)
MVP SQL Server

Créer un serveur lié avec une connexion en lecture seule vers un réplica secondaire SQL Server 2012 AlwaysOn

Mis en avant

Petite question que je me posais aujourd’hui vu qu’en ce moment je travaille souvent sur des environnements haute disponibilité avec SQL Server 2012 et AlwaysOn. Est-il possible de créer un serveur lié qui se connecte sur un réplica secondaire en lecture et profiter du coup d’une redirection transparente en cas de changement de rôle de réplica lors d’un basculement automatique par exemple ?

Le meilleur moyen de le savoir est de faire un test.

Créons tout d’abord un serveur lié en utilisant le provider SQL Server Native Client 11.0 pour pouvoir utiliser le paramètre de connexion applicationintent=readonly. Le script de création du serveur lié est le suivant :

 

EXEC master.dbo.sp_addlinkedserver    
 @server = N'LST_APPLIX',
 @srvproduct=N'',    
 @provider=N'SQLNCLI', -- Provider SQL Native Client    
 @datasrc=N'LST_APPLIX', -- Availability group listener    
 @provstr=N'applicationintent=readonly',  -- Readonly intent parameter    
 @catalog=N'ApplixEnterprise' --Target database to connect

 

icon_arrow Mon groupe de disponibilité est composé de 3 réplicas avec :

  • REPLICA1 (réplica primaire)
  • REPLICA2 (réplica secondaire en lecture seule utilisé en priorité)
  • REPLICA3 (réplica secondaire en lecture seule utilisé si REPLICA2 n’est plus joignable)

 

icon_arrow Mon serveur lié est quant à lui configuré sur un serveur standalone à part.

 

La requête suivante illustre l’état de note scénario :

 

SELECT  
 g.name AS group_name,    
 r.replica_server_name,    
 rs.role_desc,    
 rs.operational_state_desc,    
 ro.replica_server_name AS replica_ro_server_name,    
 rol.routing_priority,    
 r.secondary_role_allow_connections_desc    
FROM sys.dm_hadr_availability_replica_states AS rs    
 JOIN sys.availability_groups AS g    
  ON rs.group_id = g.group_id    
 JOIN sys.availability_replicas AS r    
  ON r.replica_id = rs.replica_id    
   AND r.group_id = rs.group_id    
 JOIN sys.availability_read_only_routing_lists rol    
  ON rol.replica_id = r.replica_id    
 JOIN sys.availability_replicas AS ro    
  ON ro.replica_id = rol.read_only_replica_id    
WHERE g.name = 'ApplixGrp'    
 AND r.replica_server_name = 'REPLICA1'

 

image

 

Tentons de retrouver le serveur sur lequel le serveur lié pointe :

SELECT *    
FROM OPENQUERY(LST_APPLIX, 'SELECT @@SERVERNAME AS SERVER_NAME');

qui donne

image

 

Ok visiblement cela fonctionne. En utilisant le listener la requête est automatiquement redirigée vers le REPLICA2 comme le stipule notre configuration.

 

On lance maintenant un basculement manuel de groupe de disponibilité ApplixGrp de REPLICA1 vers REPLICA2. REPLICA2 devient donc le nouveau primaire.REPLICA1 et REPLICA3 deviennent les réplicas secondaires. On exécute à nouveau nos requêtes :

 

SELECT  
 g.name AS group_name,    
 r.replica_server_name,    
 rs.role_desc,    
 rs.operational_state_desc,    
 ro.replica_server_name AS replica_ro_server_name,    
 rol.routing_priority,    
 r.secondary_role_allow_connections_desc    
FROM sys.dm_hadr_availability_replica_states AS rs    
 JOIN sys.availability_groups AS g    
  ON rs.group_id = g.group_id    
 JOIN sys.availability_replicas AS r    
  ON r.replica_id = rs.replica_id    
   AND r.group_id = rs.group_id    
 JOIN sys.availability_read_only_routing_lists rol    
  ON rol.replica_id = r.replica_id    
 JOIN sys.availability_replicas AS ro    
  ON ro.replica_id = rol.read_only_replica_id    
WHERE g.name = 'ApplixGrp'    
 AND r.replica_server_name = 'REPLICA2'

 

image

 

et

SELECT *    
FROM OPENQUERY(LST_APPLIX, 'SELECT @@SERVERNAME AS SERVER_NAME');

qui donne

image

 

Comme on peut le voir l’utilisation d’un serveur lié vers une architecture SQL Server 2012 AlwaysOn avec une redirection des connexions en lecture seule vers les réplicas secondaires fonctionne très bien !

 

Bon paramétrage de serveurs liés