SELECT INTO et exécution en parallèle

Mis en avant

Il y a peu de temps, en tant que consultant j’ai dû fournir quelques bonnes pratiques en terme d’architecture pour un environnement dont l’activité d’écriture était prédominante avec un import de données depuis différents sources dans des tables SQL Server. Au cours d’une discussion mon client m’a demandé quelles étaient les nouvelles fonctionnalités de SQL Server qui pourraient potentiellement améliorer la vitesse du processus d’import. J’ai eu en tête une amélioration intéressante proposée autour de la commande SELECT INTO qui est souvent utilisé dans des environnements avec ETL. En effet, il est maintenant possible d’exécuter cette commande avec exécution parallèle …

>> Pour lire la suite (en anglais)

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

In-Memory tables, Bw-Tree, and stockage

Mis en avant

SQL Server 2014 a introduit les indexes hash avec les tables in-memory. J’ai décrit certaines de leurs caractéristiques dans un blog précédent.
Ces indexes sont vraiment efficaces pour des opérations de recherche ciblées mais possèdent quelques limitations pour d’autres comme les opérations de balayage, les prédicats à base d’inégalités ou encore les balayages triés dans un ordre spécifique. Il existe maintenant un autre type d’index (index non cluster ou Bw-Tree) qui permet de répondre à cette problématique et tout comme les indexes hash ils incorporent également une chaîne de données dans leur structure au niveau feuille.

Dans ce billet je voudrais partager avec vous certains aspects intéressants du stockage qui les concernent.

>> Pour lire la suite (en anglais)

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

SQL Server 2014: Supprimer les fichiers sur disque associés à une table In-Memory

Mis en avant

Est-ce que vous ne vous êtes jamais demandé ce qui se passerait si les fichiers d’une table Hekaton étaient accidentellement supprimés du système de fichier ? Est-ce que cela compromettrait le fonctionnement global de la table voir même de l’instance SQL Server ? J’ai souvent entendu cette question au cours des événements auxquels j’ai pu assister mais sans jamais vraiment avoir de réponse. Dans ce billet (en anglais) je vous propose de voir ce qu’il en est.

Bonne lecture !

David BARBARIN (Mikedavem)
MVP et MCM SQL Server

SQL Server 2014 : AlwaysOn, chiffrement TDE et chiffrement des sauvegardes

Mis en avant

SQL Server 2014 commence à pointer le bout de son nez avec la CTP2. Cette nouvelle version va apporter pas mal de nouveautés côté moteur notamment la plus attendue Hekaton mais d’autres seront également de la partie comme une amélioration continue de la haute disponibilité avec SQL Server AlwaysOn, la sécurité avec le chiffrement des sauvegardes ou encore la limitation des actions possibles pour un administrateur de bases de données, les index columstore index pouvant être mis à jour et bien d’autres. Dans ce billet j’ai choisi de parler de SQL Server AlwaysOn et du chiffrement des bases de données et des sauvegardes. Un premier billet sur la possibilité de chiffrer les sauvegardes avec SQL14 a déjà par Christophe Laporte. Je vous invite à le lire si vous voulez en savoir un peu plus sur cette fonctionnalité.

En testant SQL Server 2014, AlwaysOn et le chiffrement certaines questions me sont venus à l’esprit.  Ces questions sont les suivantes :

  • Est-ce qu’une table Hekaton (In memory) est chiffrée si la base de données qui l’héberge utilise TDE ?
  • Est-ce qu’il est possible d’implémenter TDE avec une base de données déjà membre d’un groupe de disponibilité ?
  • Quel avantage à avoir du chiffrement de sauvegardes de bases de données si celles-ci le sont déjà avec TDE ?

 

icon_arrow Est-ce qu’une table Hekaton (In memory) est chiffrée si la base de données qui l’héberge utilise TDE ?

Tout dépend ici de quoi l’on parle. Une table Hekaton réside en mémoire mais selon sa durabilité les données la concernant peuvent être stockées sur disque. TDE chiffre les données sur disque et pas en mémoire. Un processus de chiffrement et déchiffrement opère en temps réel sur les fichiers de données et journaux de transactions (cf la BOL) . Donc ici on peut se demander si le chiffrement s’effectue pour les tables Hekaton durables car celles-ci utilisent des fichiers de données et des fichiers delta afin de pouvoir remonter les données en cas de problème.

 

alwayson_tde_0

Activons maintenant TDE sur la base de données AdventureWorks2012.

alwayson_tde_01

… et là SQL Server nous donne rapidement la réponse attendue :

alwayson_tde_03

Les tables Hekaton durables en réalité reposent sur un stockage de type filestream. On peut le voir sur l’image ci-dessous :

alwayson_tde_04

Un coup d’œil sur la documentation en ligne de SQL Server à propos de TDE nous permet de confirmer que le chiffrement n’est pas valable pour ce type de conteneur. Cela est également confirmé par ce lien pour SQL14. –> TDE n’est donc pas supporté avec l’utilisation des tables Hekaton.

 

icon_arrow  Est-ce qu’il est possible d’implémenter TDE avec une base de données déjà membre d’un groupe de disponibilité ?

Nous avons implémenté une belle architecture AlwaysOn et une des bases de données d’un groupe de disponibilité requiert maintenant d’être chiffré pour des raisons de sécurité. Comment dois je procéder dans ce cas ? Est-ce qu’il suffit d’activer TDE sur la base de données située sur le réplica primaire ? Dois-je effectuer des manipulations sur les secondaires ? Dois-je repartir de zéro en supprimant cette base de données du groupe de disponibilité, chiffrer la base de données et refaire tout le processus d’ajout à un groupe de disponibilité (backup, restauration sur les secondaires et ajout au groupe de disponibilité) ?

Soyons un peu feignant et partons du principe que tout est à faire depuis le réplica primaire et que tout sera répliqué par la suite par AlwaysOn .. après tout ca sert à cela la réplication non ? :-) . Précédemment nous avons implémenté TDE sur la base de données AdventureWorks2012 qui faisait parti d’un groupe de disponibilité lst_adv. On pourrait conclure que tout est fini mais si l’on jette un rapide coup d’œil à l’état de synchronisation de notre groupe de disponibilité on s’aperçoit vite qu’un problème est survenu suite à la mise en place de TDE et que la base de données Adventureworks2012 est même dans un état suspect sur les serveurs secondaires sur SSMS.

alwayson_tde_1

 

Parfait … on active TDE et on se retrouve avec une base de données suspecte !! Bravo TDE !! :-) Ne paniquons pas et regardons un peu plus loin dans les logs d’erreurs SQL on peut voir ceci :

alwayson_tde_2

La cause du problème est la suivante : après avoir chiffré notre base de données sur le réplica primaire, les réplicas secondaires ne trouvent pas le certificat dont l’emprunte correspond à celle utilisé par le certificat créé initialement pour le chiffrement TDE. Dans ce cas la réplication du flux de données du réplica primaire vers réplicas secondaires est donc naturellement suspendue. Ce comportement est tout à fait normal puisque nous essayons de répliquer une base de données chiffrée vers une base de données qui ne peut pas être chiffrée. Ce fameux certificat est hébergé dans la base de données master et n’est pas répliqué automatiquement par AlwaysOn contrairement à la clé de chiffrement de bases de données située dans la base de données AdventureWorks2012. Il ne nous reste plus qu’à sauvegarder ce certificat, le restaurer sur les réplicas secondaires et relancer la réplication des données pour AdventureWorks2012 :

alwayson_tde_05

Après quelques secondes ou minutes (en fonction de la quantité de données à répliquer car l’état de synchronisation est suspendue) on peut voir sur le dashbord du groupe de disponibilité lst_adv que tout est rentré dans l’ordre :

alwayson_tde_3 

Pour résumé, il est possible d’implémenter TDE sur une topologie AlwaysOn existante à condition de bien penser à restaurer le certificat servant au chiffrement de la clé de chiffrement de la base de données pour TDE. Le mode feignant est donc à proscrire :-)

 

icon_arrow Quel avantage à avoir du chiffrement de sauvegardes de bases de données si celles-ci le sont déjà avec TDE ?

La documentation en ligne concernant TDE explique qu’une sauvegarde provenant d’une base chiffrée par TDE est également chiffrée par le même certificat de serveur. Quel intérêt à chiffrer de nouveau le média de backup par la nouvelle fonctionnalité de chiffrement proposée avec SQL Server 14 si celle-ci est déjà initialement chiffrée par TDE ? Je me suis posé la question en pensant qu’une fois qu’une base était chiffrée par TDE, le chiffrement des sauvegardes n’avaient aucun effet. Mais je me trompais bien sûr : le chiffrement des sauvegardes avec SQL14 agit indépendamment de TDE et chiffrera une seconde fois le média de sauvegarde. Cela nous permet d’ajouter une protection supplémentaire au média de sauvegarde à savoir que chaque chiffrement peut utiliser un algorithme différent. Voyons comment cela se passe.

On commence donc par créer un certificat dédié aux chiffrement des sauvegardes dans la base de données master :

image

On effectue une sauvegarde de notre base de données AdventureWorks2012 avec le nouveau mot clé ENCRYTION pour la commande BACKUP :

image

On a maintenant un média de sauvegarde doublement chiffré. Si maintenant on tente de lire le contenu de ce média depuis une nouvelle instance SQL on se retrouve avec le message d’erreur suivant :

alwayson_tde_6

La lecture de l’entête du média de sauvegarde est impossible si le serveur SQL ne possède pas un certain certificat avec l’emprunte décrite ci-dessus. Par déduction, on peut aussi affirmer que la restauration ne sera pas impossible pour le moment.

Restaurons le certificat utilisé pour le chiffrement des sauvegardes dans la base de données master de la nouvelle instance SQL et essayons à nouveau de lire le contenu du média de sauvegarde :

alwayson_tde_7

Cela ne fonctionne toujours pas … on peut remarquer que l’emprunte du certificat manquant est différent cette fois. Il correspond en réalité à celle du certificat créé initialement pour activer TDE sur AdventureWorks2012. Après avoir restauré ce certificat il est possible de lire le contenu du média de sauvegarde et donc de restaurer notre base de données :

alwayson_tde_4 

Pour un nouveau réplica AlwaysOn, il faudra donc bien penser à restaurer tous les certificats nécessaires à la restauration d’une base de données avant de la joindre au groupe de disponibilité concerné.

Bon chiffrement !!

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