SQL Server 14 : c’est parti enfin presque …

Mis en avant

Pour ceux qui ont suivi un peu l’actualité, Microsoft a annoncé le 6 Avril la sortie de SQL Server 2014 à l’occasion du TechEd North America. Il est vrai que cette annonce peut surprendre d’autant plus que le 02 avril 2012 SQL Server 2012 sortait. Le planning des sorties des versions s’est en effet réduit. Mais qu’est-ce que nous réserve cette nouvelle version (et pas une R2 cette fois)  ?

Parmi les fonctionnalités les plus attendus et les plus prometteuses :

  • Hekaton : le nom de code du moteur de base de données entièrement in-memory avec un contrôle de concurrence multi-version sans mécanisme de verrouillage (locks et latchs).  On pourra également compiler les procédures stockées directement en code machine pour obtenir des gains de performance supplémentaires. Très prometteur !!!
  • L’extension du cache mémoire aux supports non volatiles avec l’utilisation de disques SSD
  • L’utilisation des index columnstores  avec possibilité de mises à jour, ce qui va étendre les scénarios d’utilisations de ces types d’index
  • L’extension du gouverneur de ressources aux IO. On a maintenant le scope complet de gestion des ressources CPU, RAM et IO
  • L’estimation des cardinalités par l’optimiseur de requêtes améliorée
  • Gestion des statistiques des colonnes et index possibles au niveau partition d’une table
  • Amélioration apportée pour SQL Server AlwaysOn (utilisation possible jusqu’à 8 réplicas, plus de downtime pendant les reconstructions d’index en ligne …)
  • Possibilité de scaling des ressources augmentée avec l’utilisation de Windows Server 2012 R2
  • Plateforme Hybrid-Cloud ready
  • ….

 

Pour plus de détails c’est par ici : SQL Server Blog

Vous avez la possibilité de vous inscrire pour l’obtention de SQL Server 2014 en CTP1 !

 

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