SQL Server 2016 AlwaysOn: direct seeding et sauvegardes

Mis en avant

Comme promis dans mon blog précédent, nous allons parler maintenant de direct seeding et des sauvegardes. Nous avons trouvé une valeur intéressante d’état courant (LIMIT_CONCURRENT_BACKUPS) depuis un événement étendu nommé hadr_automatic_seeding_state_transition. Comme expliqué la dernière fois, cette valeur parle d’elle même et c’est pourquoi nous allons essayer d’effectuer une sauvegarde concurrente lorsque la base de données est concernée par une session de seeding active.

> Lire la suite (en anglais)

David Barbarin
MVP & MCM SQL Server

Problème étrange avec la fonction sys.fn_hadr_backup_is_preferred_replica()

Mis en avant

Il y a quelque temps, j’ai eu à faire face à des problèmes de sauvegardes dans un contexte AlwaysOn et groupe de disponibilités chez l’un de mes clients (merci à lui d’avoir souligner le problème). Après avoir installé notre outil de gestion de bases de données pour la maintenance des environnements AlwaysOn (Database Management Kit), mon client a remarqué que certaines de ses bases n’étaient pas sauvegardées correctement. Humm … que se passe-t-il? Première fois que ce problème se produit .. Est-ce que cela vient de notre outil ?

> Lire la suite (en anglais)

David Barbarin
MVP & 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