SQLpro

Introduction

La haute disponibilité des données est un facteur fondamental dans l’élaboration d’une architecture de bases de données, notamment pour des données sensibles dont la disponibilité doit être continue. C’est le cas pour les applications du monde de l’hôpital, les services de secours ou encore la régulation des transports.

Cet article à pour but de comparer les solutions de deux systèmes de base de données relationnelles PostGreSQL et Microsoft SQL Server.

1 – Qu’est-ce que la haute disponibilité ?

La haute disponibilité en anglais high availability (HA) consiste à mettre en œuvre une architecture physique et logique pour redonder les données des bases de données (aspect physique) et faire en sorte que le système bascule d’une unité à l’autre (aspect logique) de la manière la plus rapide et la plus transparente au regard des besoins d’accès aux données.

2 – Qu’est-ce que le taux de disponibilité ?

C’est une mesure communément admise, calculée en pourcentage du temps où le système est indisponible par rapport au temps passé. Par exemple un système qui serait en moyenne indisponible quelques jours par an, se verrait alors attribuer un taux de disponibilité compris entre 95 % (18 jours / an) et 99 % (3 jours / an).

Aujourd’hui il est courant de viser un taux de disponibilité de l’ordre de 99,99 à 99,999 % du temps, soit une limite d’indisponibilité de 9,75 heures à 5 minutes et 30 secondes…

3 – Différence entre haute disponibilité, PRA et PCA

Un Plan de Reprise d’Activité, PRA, vise à rétablir le système d’information de l’entreprise au plus vite en cas de sinistre majeur (incendie, inondation, …).

Un Plan de Continuité des Activités PCA vise la poursuite des activités de l’entreprise face à un incident dont la gravité est circonscrite (panne machine, erreur logique, …).

Les mécanismes spécifiques introduits dans les bases de données permettent aussi bien d’assurer le PCA ou le PRA pour l’unique périmètre des bases de données, ce qui constitue, il ne faut pas l’oublier, le principal capital économique de l’entreprise.

En règle générale, les mécanismes intégrés de haute disponibilité permettent d’atteindre un PCA sans perte de données, grâce à un mode synchrone pour lequel la distance entre les données répliquées doit être relativement faible (LAN) ou bien à longue distance (WAN) via des réseaux à très haut débit extrêmement résilients.

Dans le cas du PRA une réplication asynchrone est préférable, car les machines se trouvent généralement à grande distance afin de ne pas être affecté par un sinistre de grande étendue (inondation, tremblement de terre, panne du réseau public de distribution d’électricité…), le mode asynchrone étant plus performant du point de vue du service des données, mais induisant une perte potentielle des données…

4 – Architecture technique

4.1 – Le principe

Une première instance du serveur de bases de données concentre les bases actives dites « primaires » et un mécanisme, généralement basé sur les transactions, propage aux nœuds passifs (généralement appelés esclaves), c’est-à-dire aux autres instances de serveurs de bases de données, les informations nécessaires à reconstruire les données qui ont été modifiées dans les différentes bases.

Une fonctionnalité complémentaire surveille la disponibilité des machines et en cas de problème alerte le DBA sur la défaillance ou bascule directement sur l’un des nœuds de secours.

4.2 – L’implémentation

Nous n’allons pas recopier bêtement les aides en lignes officielles et les exemples documentés sur Internet, mais en voici les liens :

• Pour PostGreSQL
• Pour Microsoft SQL Server

5 – Différences entre les deux approches

Parlons maintenant de ce qui différencie PostGreSQL de SQL Server en matière de haute disponibilité…

5.1 – Quelles bases sont concernées ?

Dans PostGreSQL du fait qu’il n’existe qu’un seul journal de transactions, toutes les bases, sans exception, doivent être répliquées de manière physique d’un nœud à l’autre…

Au contraire dans SQL Server, chaque base de données dispose de son propre journal de transactions. On peut alors répliquer certaines bases d’un nœud A vers un nœud B et d’autres du nœud A vers un nœud C et enfin d’autres peuvent ne pas être répliquée du tout. Pour simplifier la chose, SQL Server propose de regrouper les bases de données dans des groupes de disponibilité afin que les opérations mutuelles soient conjointes aux bases d’un même groupe…

5.2 – Toutes les opérations sont-elles répliquées ?

Dans PostGreSQL la réplication est arrêtée dès qu’une commande propage l’exécution d’instruction au niveau externe (par exemple la création d’un « storage » CREATE TABLESPACE… qui induit la création d’un répertoire). Il faut donc effectuer une reprise en reconstruisant l’ensemble de la réplication, car malheureusement et toujours à cause de l’unique journal de transactions, cette reconstruction doit concerner toutes les bases, même si une seule est en cause.

Ceci ne pose aucun problème à SQL Server, à condition que le chemin dans lequel sera créé le nouvel espace de stockage existe dans les différents nœuds et le nouveau répertoire sera créé par SQL Server.

5.3 – Synchrone ?

Par défaut asynchrone dans PostGreSQL, le mécanisme permet un mode « synchrone » qui peut s’avérer désastreux en production, car il tire les performances vers le bas : le nœud actif n’envoie la transaction qu’après quelle ait été finalisée sur le « master » et le système attend la confirmation de l'unique nœud passif pour continuer, ce qui peut prendre de nombreuses minutes pour des transactions longues.

Au contraire, SQL Server agit en amont au démarrage de la transaction : les différentes transactions sont propagées en parallèle, immédiatement sur tous les nœuds, y compris le primaire. Il en résulte que, si les machines sont équilibrées, le délai n’est que celui du transit réseau dont le seuil d’alerte est de l’ordre de 15 ms, au-delà duquel SQL Server passe transitoirement en mode asynchrone pour éviter les blocages (phase de rattrapage).

5.4 – Combien de nœuds ?

Le mode synchrone de PostGreSQL est toujours limité à deux nœuds, les autres étant asynchrones, car le mécanisme de réplication est en cascade dans PostGreSQL. Par exemple à 3 nœuds (A, B, C), les deux premiers (A et B, le premier A étant actif et le second B passif) pourront être synchrones, mais pas le 3e, car la réplication se fait d’abord du nœud A vers le B puis, lorsque le nœud B a finalisé ses transactions, le nœud C les reçoit de B…

Dans SQL Server, tous les nœuds reçoivent les transactions à répliquer en parallèle, qu’ils soient en mode synchrone (dans la limite de 5 nœuds synchrone) ou asynchrone (dans la limite de 8 nœuds)…

5.5 – Quid du quorum ?

Le quorum est un algorithme dans lequel un élément interne ou externe agit en tant que témoin pour assurer une majorité de votes afin de déclencher le basculement. Par exemple avec une solution de réplication à deux nœuds, il faut un mécanise tiers pour décider quel nœud défaillant isoler et faire en sorte que le nœud résistant prenne le rôle de master si ce n’est pas le cas. L’imparité des votes devant être la règle pour décider de comment traiter le problème.

PostGreSQL ne disposant pas d’un mécanisme de quorum indépendant et au niveau système, il faut impérativement au moins 3 nœuds, c’est-à-dire trois instances de PostGreSQL pour pouvoir prétendre à un basculement automatique en mode synchrone.

En comparaison SQL Server utilise le cluster Windows et un quorum (disque ou partage de fichier) pour assurer le vote majoritaire qui décide du basculement.

5.6 – En combien de temps le système bascule ?

Bien que PostgreSQL propose un basculement automatique, celui-ci met beaucoup de temps en mode synchrone (30 secondes environ).

SQL Server en comparaison ne met que quelques millisecondes…

5.7 – Les applications se reconnectent-elles sans problème ?

PostGreSQL ne dispose pas de manière interne de la notion de « listener » qui permet à toute application de ne jamais être coupé de la base opérationnelle, quel que soit le nœud actif (le listener étant constitué dans SQL Server d’une adresse IP de redirection vers le nœud actif). Compte tenu de ceci, il faudra donc modifier les chaines de connexion des applicatifs pour que le service des données fonctionne de nouveau.
On comprend donc que, si le rétablissement de la disponibilité des bases peut être très rapide en cas de sinistre dans PostGreSQL, il n’en est pas de même pour les applicatifs, car il faudra agir manuellement… !

Dans SQL Server, chaque groupe de disponibilité rassemblant différentes base, est généralement doté d’un listener qui redirige le flux des requêtes applicatives sur le serveur actif de manière totalement transparente du point de vue des applications. Il n’y a donc aucune action à entreprendre au niveau des applications pour que celles-ci continuent d’accéder aux données de la base active en cas de basculement automatique.

5.8 – Quel volume transite sur le réseau ?

PostGreSQL ne disposant que d’un seul journal de transactions commun à toutes les bases de données, si la réplication n’a d'intérêt que pour certaines bases, le volume des communications entre nœuds est pollué par des informations inutiles qui obèrent les ressources.

En comparaison, dans SQL Server chaque base de données possède son propre journal de transactions ce qui minimise le volume du transit. De plus SQL Server pratique la compression des données des tables et index ce qui permet de diminuer encore plus le volume du transit. La compression des données étant une fonctionnalité inconnue de PostGreSQL [1].

De surcroit, les informations transmises sont elles aussi compressées avant d'être envoyées sur le réseau, en particulier si elles ne le sont déjà pas au niveau des tables et des index...

5.9 – la réplication peut-elle assurer la répartition de charge ?

Là encore une fois, le mauvais choix d’une architecture avec un seul journal de transactions pour toutes les bases de données de PostGreSQL empêche de panacher les différents nœuds en ayant une partie des bases actives sur le nœud A et l’autre sur le nœud B…
Avec PostGreSQL vous aurez donc toujours un nœud dont toutes les bases sont actives et sur l’autre toutes passives avec l’étrange impression que le serveur accueillant toutes les bases passives dispose de ressources presque toutes totalement inexploitées.

Ceci n’est pas le cas dans SQL Server, car grâce au concept de Groupe de Disponibilité, vous pouvez par exemple, enrôler 50 % de vos bases dans un groupe et le reste dans l’autre, le groupe 1 étant actif sur le nœud A et le groupe 2 actif sur le nœud B. Ceci améliore grandement les performances globales du service des données, ou encore, permet de choisir des serveurs moins « costaud » au niveau des ressources afin d’économiser sur le matériel et les licences…

5.10 – Quel est le coût des licences ?

Nous savons tous que PostGreSQL est un outil gratuit… Mais dans une certaine mesure ! En effet plusieurs entreprises proposent des versions payantes de PostGreSQL (Enterprise DB, Fujitsu, Citus…) dont le coût est loin d’être négligeable et qui deviennent vite indispensable dès que la volumétrie augmente ou que l’on a besoin de telle ou telle fonctionnalité manquante dans la version « libre » de PostGreSQL… N’oublions pas que les développeurs de PostGreSQL ont eux aussi besoin de manger et que bon nombre d’entre eux sont salariés de la société Enterprise DB qui bride sciemment les fonctionnalités de PostGreSQL pour permettre de vendre leurs produits…

Cette avancée masquée n’est pas le mode de fonctionnement de Microsoft dont les coûts sont les plus bas des SGBDR d’entreprise. L’édition Standard limitée à 24 cœurs physiques (soit 48 logiques) et 192 Go de cache (RAM : 128 table et index relationnels + 32 tables in memory + 32 index columnstore… deux fonctionnalités qui n’existent pas dans PostGreSQL) coute un peu moins de 2000 € par cœur physique auquel il faut ajouter la Software Insurance (nécessaire pour la haute disponibilité AlwaysOn, 700 € par cœurs physiques et par an) ce qui donne droit à la version future gratuitement.
Autre gratuité, celle des instances passives… Microsoft ne fait jamais payer les licences SQL Server des machines passives. Vous n’aurez donc rien à payer en licence pour le second nœud par exemple.

Ainsi, pour une machine à 16 cœurs logiques, avec un amortissement sur 5 ans, pour lequel vous serez passé par deux à trois versions de SQL Server (2017, 2019, 2022… par exemple), le budget mensuel sera donc de moins de 1000 €, soit un peu moins que le TJM de 2 journées d’un développeur…
J’oubliais le prix des licences Windows… environ 1000 € pour 16 cœurs physiques. En amortissement sur 5 ans, cela représente donc 5 € par mois…
Cher non ?

5.11 – Quels sont les outils pour faciliter la mise en œuvre et l’exploitation ?

PostGreSQL ne dispose d’aucun assistant pour la mise en place de cette réplication (il faut tout scripter) et encore moins de tableaux de bord permettant la surveillance de l’état du système, ni, bien entendu d’alertes intégrées pour être informé des dysfonctionnements… Ceci oblige à rajouter, à un outil de monitoring externe, de nombreuses requêtes tant au niveau de PostGreSQL que de l’OS afin de capturer les informations essentielles de la surveillance…
Bref un travail complexe, couteux et casse-gueule, que seules quelques entreprises comme Dalibo, Enterprise DB, … maitrisent…

En comparaisons, SQL Server dispose d’un assistant de mise en œuvre et de nombreux tableaux de bord pour la surveillance…

L’assistant de mise en œuvre de la haute disponibilité dans Microsoft SQL Server


Un des tableaux de bord de la haute disponibilité SQL Server AlwaysOn

6 – En guide de conclusion

Certains des problèmes évoqués ci-avant au sujet de PostGreSQL peuvent être contournés en ajoutant des outils complémentaires comme Barman, repmgr, Slony I, Pgpool II, Patroni, Pacemaker…
Mais cela complexifie encore plus l’architecture et nécessite une administration complémentaire lourde, complexe et chère en exploitation et pour ce dernier point parce qu’il n’existe quasiment pas d’assistance de niveau professionnelle à contacter en cas de problème…

Bref, la soi-disant économie réalisée du fait de la gratuité des licences PostGreSQL en comparaison de l’explosion du coût d’exploitation et du hardware complémentaire montre vite que SQL Server est plus que compétitif, et il restera toujours de nombreuses lacunes à PostGreSQL par rapport aux fonctionnalités incluses dans Microsoft SQL Server en matière de haute disponibilité…

Pour information, les pompiers de Paris (BSPP) utilisent SQL Server pour la base de données des secours depuis 2005 d’abord avec le mirroring puis depuis la version 2012 avec AlwaysOn…

Pour information, le site web leboncoin avait mis en place une réplication pour ses 70 instances de PostGreSQL et avait perdu le mécanisme de haute disponibilité le 1er mars 2013 nécessitant 5 jours d’efforts pour le remettre en fonctionnement… Et Jean-Louis Bergamo (@JLB666 ça ne s’invente pas…) était fier de présenter ce désastre comme une réussite aux PGDays 2014…
Il est a noter que leboncoin utilise une seule instance MS SQL Server pour leur base analytique… !



[1] la compression des données dans SQL Server concerne les données des tables et des index et s’opère a différents niveaux plus qui permettent d’économiser plus ou moins d’octets, mais n’affecte pas les lectures dont les performances sont améliorées grâce au gain de place en cache liée à cette compression. Les techniques de compression étant spécifiques aux SGBDR. Dans SQL Server ces algorithmes consistent en deux familles : l’élimination des données non significatives d’une part (compression « ROW ») et la réalisation de dictionnaires de racines d’autre part (compression de type « PAGE ») dont on trouvera, pour cette dernière, quelques les détails techniques ici : Implémentation de la compression de page.

Ce contenu a été publié avec comme mot(s)-clé(s) AlwaysOn, base de données, continuité, haute disponibilité, hot standby, PCA, PostGreSQL, PostGreSQL vs SQL Server, PRA, Replication, sql server.