Configurer un groupe de disponibilité SQL Server pour l’échelle lecture sur Linux
S’applique à :
SQL Server - Linux
Cet article explique comment créer un groupe de disponibilité Always On SQL Server sur Linux sans gestionnaire de clusters. Cette architecture fournit uniquement une échelle lecture. Elle n’assure pas la haute disponibilité.
L’architecture des groupes de disponibilité se présente sous deux formes. Une architecture visant la haute disponibilité utilise un gestionnaire de clusters pour améliorer la continuité d’activité. Pour créer l’architecture à haute disponibilité, consultez Configurer le groupe de disponibilité Always On SQL Server pour la haute disponibilité sur Linux.
Un groupe de disponibilité avec CLUSTER_TYPE = NONE peut inclure des réplicas hébergés sur des plateformes de système d’exploitation différentes. Il ne peut pas prendre en charge la haute disponibilité.
Prérequis
Avant de créer le groupe de disponibilité, vous devez :
- Définir votre environnement de sorte que tous les serveurs qui hébergeront des réplicas de disponibilité puissent communiquer.
- Installez SQL Server.
Notes
Sur Linux, vous devez créer un groupe de disponibilité avant de l’ajouter en tant que ressource de cluster à manager par le cluster. Ce document fournit un exemple qui crée le groupe de disponibilité. Pour des instructions spécifiques à la distribution pour créer le cluster et ajouter le groupe de disponibilité en tant que ressource de cluster, consultez les liens sous « Étapes suivantes ».
Mettre à jour le nom de l’ordinateur pour chaque hôte.
Chaque nom de serveur SQL Server doit :
- comporter 15 caractères ou moins
- être unique dans le réseau.
Pour définir le nom de l’ordinateur, modifiez
/etc/hostname. Le script suivant vous permet de modifier/etc/hostnameavec vi :sudo vi /etc/hostnameConfigurer le fichier hôtes.
Notes
Si les noms d’hôte sont inscrits avec leur adresse IP dans le serveur DNS, il n’est pas nécessaire d’effectuer les étapes ci-dessous. Vérifiez que tous les nœuds destinés à faire partie de la configuration du groupe de disponibilité peuvent communiquer entre eux. (Un test Ping vers le nom d’hôte doit répondre avec l’adresse IP correspondante.) Vérifiez aussi que le fichier
/etc/hostsne contient pas d’enregistrement qui mappe l’adresse IP 127.0.0.1 de localhost au nom d’hôte du nœud.Le fichier hosts sur chaque serveur contient les adresses IP et les noms de tous les serveurs qui seront inclus dans le groupe de disponibilité.
La commande suivante retourne l’adresse IP du serveur actif :
sudo ip addr showMettez à jour
/etc/hosts. Le script suivant vous permet de modifier/etc/hostsavec vi :sudo vi /etc/hostsL’exemple suivant montre
/etc/hostssurnode1avec des ajouts pournode1,node2etnode3. Dans cet exemple,node1fait référence au serveur qui héberge le réplica principal, etnode2etnode3aux serveurs qui hébergent les réplicas secondaires.127.0.0.1 localhost localhost4 localhost4.localdomain4 ::1 localhost localhost6 localhost6.localdomain6 10.128.18.12 node1 10.128.16.77 node2 10.128.15.33 node3
Installer SQL Server
Installez SQL Server. Les liens suivants pointent vers les instructions d’installation de SQL Server pour différentes distributions :
Activer les groupes de disponibilité Always On
Activez les groupes de disponibilité Always On pour chaque nœud qui héberge une instance SQL Server, puis redémarrez mssql-server. Exécutez le script suivant :
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server
Activer une session d’événements AlwaysOn_health
Vous pouvez éventuellement activer les événements étendus pour mieux diagnostiquer la cause racine quand vous résolvez les problèmes d’un groupe de disponibilité. Exécutez la commande suivante sur chaque instance de SQL Server :
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO
Pour plus d’informations sur cette session XE, consultez Configurer les événements étendus pour des groupes de disponibilité Always On.
Créer un certificat
Le service SQL Server sur Linux utilise des certificats pour authentifier les communications entre les points de terminaison de mise en miroir.
Le script Transact-SQL suivant crée une clé principale et un certificat. Il sauvegarde ensuite le certificat et sécurise le fichier avec une clé privée. Mettez à jour le script avec des mots de passe forts. Se connecter à l'instance principale. Pour créer le certificat, exécutez le script suivant Transact-SQL suivant :
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);
À ce stade, votre réplica SQL Server principal a un certificat à l’emplacement /var/opt/mssql/data/dbm_certificate.cer et une clé privée à l’emplacement var/opt/mssql/data/dbm_certificate.pvk. Copiez ces deux fichiers au même emplacement sur tous les serveurs qui hébergeront les réplicas de disponibilité. Utilisez l’utilisateur mssql ou accordez à l’utilisateur mssql l’autorisation d’accéder à ces fichiers.
Par exemple, sur le serveur source, la commande suivante copie les fichiers sur la machine cible. Remplacez les valeurs **<node2>** par les noms des instances qui hébergeront les réplicas.
cd /var/opt/mssql/data
scp dbm_certificate.* root@**<node2>**:/var/opt/mssql/data/
Sur chaque serveur cible, accordez à l’utilisateur mssql l’autorisation d’accéder au certificat.
cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*
Créer le certificat sur les serveurs secondaires
Le script Transact-SQL suivant crée une clé principale et un certificat à partir de la sauvegarde que vous avez créée sur le réplica SQL Server principal. Mettez à jour le script avec des mots de passe forts. Le mot de passe de déchiffrement est le même mot de passe que celui que vous avez utilisé pour créer le fichier .pvk à une étape précédente. Popur créer le certificat, exécutez le script suivant sur tous les serveurs secondaires :
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);
Créer les points de terminaison de mise en miroir de bases de données sur tous les réplicas
Les points de terminaison de mise en miroir de bases de données utilisent le protocole TCP (Transmission Control Protocol) pour l’envoi et la réception de messages entre les instances de serveur participant à des sessions de mise en miroir de bases de donnée ou hébergeant des réplicas de disponibilité. Le point de terminaison de mise en miroir de bases de données écoute sur un numéro de port TCP unique.
Le script Transact-SQL suivant crée un point de terminaison d’écoute nommé Hadr_endpoint pour le groupe de disponibilité. Il démarre le point de terminaison et donne l’autorisation de connexion au certificat que vous avez créé. Avant d’exécuter le script, remplacez les valeurs entre **< ... >**. Si vous le souhaitez, vous pouvez inclure une adresse IP LISTENER_IP = (0.0.0.0). L’adresse IP de l’écouteur doit être une adresse IPv4. Vous pouvez également utiliser 0.0.0.0.
Mettez à jour le script Transact-SQL suivant pour votre environnement sur toutes les instances de SQL Server :
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = **<5022>**)
FOR DATABASE_MIRRORING (
ROLE = ALL,
AUTHENTICATION = CERTIFICATE dbm_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
Notes
Si vous utilisez l’édition SQL Server Express sur un nœud pour héberger un réplica de configuration uniquement, la seule valeur valide pour ROLE est WITNESS. Exécutez le script suivant sur l’édition de SQL Server Express :
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = **<5022>**)
FOR DATABASE_MIRRORING (
ROLE = WITNESS,
AUTHENTICATION = CERTIFICATE dbm_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
Le port TCP sur le pare-feu doit être ouvert pour le port de l’écouteur.
Important
Pour la mise en production SQL Server 2017, la seule méthode d’authentification prise en charge pour le point de terminaison de mise en miroir de bases de données est CERTIFICATE. L’option WINDOWS sera activée dans une mise en production ultérieure.
Pour plus d’informations, consultez Point de terminaison de mise en miroir de bases de données (SQL Server).
Créez le groupe de disponibilité
Créez le groupe de disponibilité. Définissez CLUSTER_TYPE = NONE. Pour chaque réplica, définissez également FAILOVER_MODE = MANUAL. Les applications clientes qui exécutent des charges de travail analytiques ou de création de rapports peuvent se connecter directement aux bases de données secondaires. Vous pouvez également créer une liste de routage en lecture seule. Les connexions au réplica principal transfèrent les demandes de connexion à chacun des réplicas secondaires de la liste de routage en mode tourniquet (round-robin).
Le script Transact-SQL suivant crée un groupe de disponibilité nommé ag1. Le script configure les réplicas de groupe de disponibilité avec SEEDING_MODE = AUTOMATIC. Ce paramètre permet à SQL Server de créer automatiquement la base de données sur chaque serveur secondaire après son ajout au groupe de disponibilité. Mettez à jour le script suivant en fonction de votre environnement. Remplacez les valeurs <node1> et <node2> par les noms des instances de SQL Server qui hébergent les réplicas. Remplacez la valeur <5022> par le port que vous définissez pour le point de terminaison. Exécutez le script Transact-SQL suivant sur le réplica SQL Server principal :
CREATE AVAILABILITY GROUP [ag1]
WITH (CLUSTER_TYPE = NONE)
FOR REPLICA ON
N'<node1>' WITH (
ENDPOINT_URL = N'tcp://<node1>:<5022>',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
),
N'<node2>' WITH (
ENDPOINT_URL = N'tcp://<node2>:<5022>',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC,
SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
Joindre des instances SQL Server secondaires au groupe de disponibilité
Le script Transact-SQL suivant joint un serveur à un groupe de disponibilité nommé ag1. Mettez à jour le script en fonction de votre environnement. Sur chaque réplica SQL Server secondaire, exécutez le script Transact-SQL suivant pour joindre le groupe de disponibilité :
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
Ajouter une base de données au groupe de disponibilité
Vérifiez que la base de données ajoutée au groupe de disponibilité est dans le mode de récupération complète et qu’elle dispose d’un journal de sauvegarde valide. S’il s’agit d’une base de données de test ou d’une base de données nouvellement créée, faites-en une sauvegarde. Sur le serveur SQL Server principal, exécutez le script Transact-SQL suivant pour créer et sauvegarder une base de données nommée db1 :
CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';
Sur le réplica SQL Server principal, exécutez le script Transact-SQL suivant pour ajouter une base de données nommée db1 à un groupe de disponibilité nommé ag1 :
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];
Vérifier que la base de données est créée sur les serveurs secondaires
Sur chaque réplica SQL Server secondaire, exécutez la requête suivante pour déterminer si la base de données db1 a été créée et si elle est synchronisée :
SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;
Ce groupe de disponibilité n’est pas une configuration à haute disponibilité. Si vous avez besoin de la haute disponibilité, suivez les instructions dans Configurer un groupe de disponibilité Always On pour SQL Server sur Linux. En particulier, créez le groupe de disponibilité avec CLUSTER_TYPE=WSFC (dans Windows) ou CLUSTER_TYPE=EXTERNAL (dans Linux). Vous pouvez ensuite l’intégrer à un gestionnaire de clusters en utilisant un clustering de basculement Windows Server sur Windows ou Pacemaker sur Linux.
Se connecter à des réplicas secondaires en lecture seule
Il existe deux façons de se connecter à des réplicas secondaires en lecture seule. Les applications peuvent se connecter directement à l’instance de SQL Server qui héberge le réplica secondaire et interroger les bases de données. Elles peuvent aussi utiliser le routage en lecture seule, ce qui nécessite un écouteur.
Basculer le réplica principal sur un groupe de disponibilité avec échelle lecture
Chaque groupe de disponibilité contient un seul réplica principal. Le réplica principal autorise les opérations de lecture et d’écriture. Pour changer de réplica principal, vous pouvez effectuer un basculement. Dans un groupe de disponibilité standard, le gestionnaire de cluster automatise le processus de basculement. Dans un groupe de disponibilité avec le type de cluster AUCUN, le processus de basculement est manuel.
Il existe deux façons de basculer le réplica principal dans un groupe de disponibilité avec le type de cluster AUCUN :
- Basculement manuel sans perte de données
- Basculement manuel forcé avec perte de données
Basculement manuel sans perte de données
Utilisez cette méthode quand le réplica principal est disponible, mais que vous devez temporairement ou définitivement changer l’instance qui héberge le réplica principal. Pour éviter toute perte de données, avant d’effectuer le basculement manuel, vérifiez que le réplica secondaire cible est à jour.
Pour effectuer un basculement manuel sans perte de données :
Faites en sorte que le réplica principal actuel et le réplica secondaire cible soient
SYNCHRONOUS_COMMIT.ALTER AVAILABILITY GROUP [AGRScale] MODIFY REPLICA ON N'<node2>' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);Pour vérifier que les transactions actives sont validées sur le réplica principal et sur au moins un réplica secondaire synchrone, exécutez la requête suivante :
SELECT ag.name, drs.database_id, drs.group_id, drs.replica_id, drs.synchronization_state_desc, ag.sequence_number FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag WHERE drs.group_id = ag.group_id;Le réplica secondaire est synchronisé quand
synchronization_state_desca pour valeurSYNCHRONIZED.Affectez la valeur 1 à
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT.Le script suivant définit
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMITsur 1 sur un groupe de disponibilité nomméag1. Avant d’exécuter le script suivant, remplacezag1par le nom de votre groupe de disponibilité :ALTER AVAILABILITY GROUP [AGRScale] SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);Ce paramétrage garantit que chaque transaction active est validée sur le réplica principal et sur au moins un réplica secondaire synchrone.
Notes
Ce paramètre n’est pas propre au basculement et doit être défini en fonction des exigences de l’environnement.
Définissez le réplica principal et le ou les réplicas secondaires qui ne participent pas au basculement hors connexion pour préparer le changement de rôle :
ALTER AVAILABILITY GROUP [AGRScale] OFFLINEPromouvez le réplica secondaire en réplica principal.
ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;Changez le rôle de l’ancien réplica principal en des secondaires par
SECONDARYet exécutez la commande suivante sur l’instance SQL qui héberge l’ancien réplica principal :ALTER AVAILABILITY GROUP [AGRScale] SET (ROLE = SECONDARY);Notes
Pour supprimer un groupe de disponibilité, utilisez DROP AVAILABILITY GROUP. Pour un groupe de disponibilité créé avec le type de cluster NONE ou EXTERNAL, exécutez la commande sur tous les réplicas faisant partie du groupe de disponibilité.
Pour reprendre le déplacement des données, exécutez la commande suivante pour chaque base de données du groupe de disponibilité sur l’instance de SQL Server qui héberge le réplica principal :
ALTER DATABASE [db1] SET HADR RESUMERecréez l’écouteur que vous avez créé à des fins d’échelle lecture et qui n’est pas géré par un gestionnaire de cluster. Si l’écouteur d’origine pointe vers l’ancien réplica principal, supprimez-le et recréez-le pour qu’il pointe vers le nouveau réplica principal.
Basculement manuel forcé avec perte de données
Si le réplica principal n’est pas disponible et ne peut pas être récupéré immédiatement, vous devez forcer un basculement vers le réplica secondaire avec perte de données. Cependant, si le réplica principal d’origine récupère après le basculement, il va assumer le rôle principal. Pour éviter que chaque réplica soit dans un état différent, supprimez le réplica principal d’origine du groupe de disponibilité après un basculement forcé avec perte de données. Une fois que le serveur principal d’origine revient en ligne, supprimez-y entièrement le groupe de disponibilité.
Pour forcer un basculement manuel avec perte de données du réplica principal N1 vers le réplica secondaire N2, effectuez les étapes suivantes :
Sur le réplica secondaire (N2), lancez le basculement forcé :
ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;Sur le nouveau réplica principal (N2), supprimez le réplica principal d’origine (N1) :
ALTER AVAILABILITY GROUP [AGRScale] REMOVE REPLICA ON N'N1';Vérifiez que tout le trafic d’application pointe vers l’écouteur et/ou le nouveau réplica principal.
Si le réplica principal d’origine (N1) est mis en ligne, placez immédiatement le groupe de disponibilité AGRScale hors connexion sur le réplica principal d’origine (N1) :
ALTER AVAILABILITY GROUP [AGRScale] OFFLINES’il existe des données ou des modifications non synchronisées, conservez ces données via des sauvegardes ou d’autres options de réplication des données qui répondent aux besoins de votre entreprise.
Ensuite, supprimez le groupe de disponibilité du réplica principal d’origine (N1) :
DROP AVAILABILITY GROUP [AGRScale];Supprimez la base de données du groupe de disponibilité sur le réplica principal d’origine (N1) :
USE [master] GO DROP DATABASE [AGDBRScale] GO(Facultatif) Si vous le souhaitez, vous pouvez maintenant ajouter N1 comme nouveau réplica secondaire au groupe de disponibilité AGRScale.
Contenu connexe
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour