Mettre en place une replication sur au moins 3 noeuds avec un serveur SQL. Après avoir tenté pendant un moment avec mysql que je n'ai jamais réussi à faire fonctionner dans un conteneur docker, je me suis intéressé à SQLServer de Microsoft.
Un peu moins facile d'utilisation que la version MySQL en terme de développement par la suite, il a l'avantage de proposer ce dont j'avais besoin pour mes tests, c'est à dire de la réplication de données sur au moins 3 noeuds avec la possibilité de failover et tout ça dans du docker :-)
Première étape : configurer le container docker
Par défault le container docker de SQLServer n'a pas les options de haute disponibilité activée. En soi, ce n'est pas un problème, il nous suffit de faire notre propre image docker à partir de l'original pour l'activer. Voici donc le Dockerfile:
FROM mcr.microsoft.com/mssql/server:2019-latest
USER root
RUN /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
RUN /opt/mssql/bin/mssql-conf set sqlagent.enabled true
USER mssql
Seule subtilité ici si vous êtes habitué à docker c'est le changement en user root avant de lancer les options pour ensuite reswitcher sur l'utilisateur normal du container, à savoir, mssql. On le build, chez moi l'image sera nommée `sqlserver-ha`
Deuxième étape : déploiement de 3 noeuds
Première chose, créé les dossiers de données dont on va avoir besoin et donner les droit aux user mssql qui va en avoir besoin :
mkdir data1 data2 data3 shared1 shared2 shared3
sudo chown 10001 data1 data2 data3 shared1 shared2 shared3
On déploie ensuite avec le docker compose suivant
version: '3.6'
services:
sqlserver1:
container_name: sqlserver1
image: sqlserver-ha:latest
environment:
SA_PASSWORD: "PB6P88z6b44QCdkt"
ACCEPT_EULA: "Y"
volumes:
- ./data1:/var/opt/mssql/data
- ./shared1:/var/shared_data
ports:
- "14331:1433"
- "50221:5022"
hostname: "sqlserver1"
sqlserver2:
container_name: sqlserver2
image: sqlserver-ha:latest
environment:
SA_PASSWORD: "PB6P88z6b44QCdkt"
ACCEPT_EULA: "Y"
volumes:
- ./data2:/var/opt/mssql/data
- ./shared2:/var/shared_data
ports:
- "14332:1433"
- "50222:5022"
hostname: "sqlserver2"
sqlserver3:
container_name: sqlserver3
image: sqlserver-ha:latest
environment:
SA_PASSWORD: "PB6P88z6b44QCdkt"
ACCEPT_EULA: "Y"
volumes:
- ./data3:/var/opt/mssql/data
- ./shared3:/var/shared_data
ports:
- "14333:1433"
- "50223:5022"
hostname: "sqlserver3"
On notera le fait de rediriger le port 1433 (port standard pour la connexion à la base SQLServer) mais aussi le port 5022 qui servira à communiquer entre nos noeuds. Bien évidemment si vous êtes sur une seule machine avec vos 3 noeuds, vous n'avez pas forcément besoin de rediriger le port 5022. Mais ici on a lancé nos 3 noeuds sur 3 serveurs différents.
On lance le `docker-compose up -d`, on vérifie dans les logs que les serveurs ce sont bien initialisé et on peut passer à la suite, la configuration du groupe de disponibilité.
Troisième étape : configurer le groupe HA
On définit que notre noeud sqlserver1 va être le noeud primaire de notre groupe, on va donc commencer par le configurer en exécutant ces quelques lignes SQL.
USE master
GO
-- Create user for replication
CREATE LOGIN dbm_login WITH PASSWORD = 'KuDzgdXGR7dD79Uv';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
-- Create encryption key to share with other nodes
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'KuDzgdXGR7dD79Uv';
go
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/var/shared_data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/shared_data/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = 'KuDzgdXGR7dD79Uv'
);
GO
-- Create replication endpoint to communicate with others
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)
FOR DATA_MIRRORING (
ROLE = ALL,
AUTHENTICATION = CERTIFICATE dbm_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
GO
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO
Lors de la configuration, le noeud primaire a exporté un certificat dans le dossier shared1, il vous faut maintenant copier le certificat (les deux fichiers) dans les dossiers shared2 et shared3 pour pouvoir ensuite les importer dans les noeuds correspondant avec le script SQL suivant (à exécuter sur node2 et node3) :
USE master
GO
-- create user for replication
CREATE LOGIN dbm_login WITH PASSWORD = 'KuDzgdXGR7dD79Uv';
CREATE USER dbm_user FOR LOGIN dbm_login;
GO
-- import certificate from primary node
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'KuDzgdXGR7dD79Uv';
go
CREATE CERTIFICATE dbm_certificate
AUTHORIZATION dbm_user
FROM FILE = '/var/shared_data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/shared_data/dbm_certificate.pvk',
DECRYPTION BY PASSWORD = 'KuDzgdXGR7dD79Uv'
);
GO
-- create endpoint for replication
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = 5022)
FOR DATA_MIRRORING (
ROLE = ALL,
AUTHENTICATION = CERTIFICATE dbm_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];
GO
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO
Une fois que tous nos serveurs sont configuré, il ne nous reste plus qu'à créer le groupe via cette commande SQL, a exécuter uniquement sur le noeud primaire
CREATE AVAILABILITY GROUP [AG1]
WITH (CLUSTER_TYPE = NONE)
FOR REPLICA ON
N'sqlserver1' WITH (
ENDPOINT_URL = N'tcp://sql1.moi.com:50221',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SEEDING_MODE = AUTOMATIC,
FAILOVER_MODE = MANUAL ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'sqlserver2' WITH (
ENDPOINT_URL = N'tcp://sql2.moi.com:50222',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SEEDING_MODE = AUTOMATIC,
FAILOVER_MODE = MANUAL ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'sqlserver3' WITH (
ENDPOINT_URL = N'tcp://sql3.moi.com:50223',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SEEDING_MODE = AUTOMATIC,
FAILOVER_MODE = MANUAL ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO
Vous prendrez bien évidemment soin de remplacer les ENDPOINT_URL avec l'adresse de vos serveurs.
Il ne nous reste plus qu'à indiquer à nos deux autres noeuds de rejoindre le groupe avec les commandes suivantes :
ALTER AVAILABILITY GROUP [AG1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;
GO
Quatrième étape : créer la base de données
Étape finale, créer la base de données et indiquer quelle doit être répliquée. À partir de maintenant, les deux noeuds secondaires sont en lecture seule et vous ne pouvez écrire que sur le noeud primaire. On exécute donc la création de la base sur le noeud primaire
CREATE DATABASE agtestdb;
GO
-- backup de la base
ALTER DATABASE agtestdb SET RECOVERY FULL;
GO
BACKUP DATABASE agtestdb TO DISK = '/var/opt/mssql/data/agtestdb.bak';
GO
-- ajout de la base au groupe
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [agtestdb];
GO
Après quelques millisecondes, vous pouvez voir sur le dashboard de votre AG sur SSMS que la base est répliquée correctement sur les 3 instances
Étape bonus : failover
La grande question, que faire quand votre noeud primaire tombe et que vous voulez promouvoir l'un des deux autres le temps que le premier soir réparé ou que vous souhaitez simplement mettre à jour une machine et donc l'éteindre pendant quelques minutes.
Si vous avez toujours l'accès au noeud primaire, afin de l'éteindre "proprement", il est nécessaire d'exécuter la commande suivante dessus :
ALTER AVAILABILITY GROUP [ag1] OFFLINE
Une fois cette commande passée, vous pouvez vérifier sur votre dashboard, votre noeud primaire est indisponible.
Dans tous les cas, on veut maintenant promouvoir un autre noeud en tant que noeud primaire. On va dans ce cas exécuter la commande suivante sur le noeud2 :
-- on passe en noeud primaire
ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS;
-- on réactive la synchronisation de la base
alter database agtestdb set hadr resume
Le dashboard vous indique maintenant que votre noeud2 est le noeud primaire de votre groupe. Mais votre noeud3 ne s'est pas reconnecté. On va indiquer au noeud3 de se reconnecter au groupe.
ALTER AVAILABILITY GROUP [ag1] offline
ALTER AVAILABILITY GROUP [ag1] SET (Role = secondary);
alter database agtestdb set hadr resume
Et voilà, notre noeud3 est de retour dans notre groupe. Quand le noeud1 sera de nouveau disponible, il nous suffira d'exécuter les commandes suivantes pour qu'il fasse de même.
ALTER AVAILABILITY GROUP [ag1] SET (Role = secondary);
alter database agtestdb set hadr resume
Et si on souhaite remettre le noeud1 en noeud primaire, il suffira de refaire la procédure que l'on vient de voir.
Problèmes que l'on peut rencontrer
Si la création du groupe échoue avec une erreur indiquant qu'il n'y a pas le noeud courant dans le groupe, c'est que vous n'avez pas indiqué le hostname correctement dans le fichier docker-compose ou que le hostname que vous avez choisi fait plus de 15 caractères. Pour vérifier le hostname du serveur :
Depuis un terminal pour vérifier celui du container docker
docker exec -it sqlserver1 /bin/hostname
Sur le serveur SQL pour vérifier celui qu'il a chargé
select @@servername
Ajouter un noeud
Sur le nouveau noeud, on va créer le certificat et le endpoint de la même manière qu'on l'avait fait pour les noeuds initiaux.
On va ensuite faire une sauvegarde de la base de données du noeud primaire et l'importer sur notre nouveau noeud
Sur le noeud primaire
backup database agtest to disk = '/var/shared_data/agtest.bak' with format
backup log agtest TO DISK = '/var/shared_data/agtest.bak.log'
On copie les deux fichiers ainsi récupéré sur notre nouveau noeud avant d'y exécuter les commandes suivantes :
restore database agtest from disk = '/var/shared_data/agtest.bak' with norecovery
restore log agtest from disk = '/var/shared_data/agtest.bak.log' with FILE=1, norecovery
À partir du noeud primaire, on ajoute le noeud au groupe.
alter availability group AG1
add replica on 'sqlserver4' with (
ENDPOINT_URL = N'tcp://sql4.moi.com:50224',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
SEEDING_MODE = AUTOMATIC,
FAILOVER_MODE = MANUAL ,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
)
Enfin, sur le noeud secondaire, on rejoint le groupe
ALTER AVAILABILITY GROUP [AG1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;