Configurer un groupe de disponibilité SQL Server pour l’échelle lecture sur Linux

Article
06/28/2024

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.

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

Mettre à jour le nom de l’ordinateur pour chaque hôte.

Chaque nom d’instance SQL Server doit être :
- 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/hostname avec vi :
```
sudo vi /etc/hostname
```
Configurer 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/hosts ne 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 show
```
Mettez à jour /etc/hosts. Le script suivant vous permet de modifier /etc/hosts avec vi :
```
sudo vi /etc/hosts
```
L’exemple suivant montre /etc/hosts sur node1 avec des ajouts pour node1, node2 et node3. Dans cet exemple, node1 fait référence au serveur qui héberge le réplica principal, et node2 et node3 aux 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 (XE) pour mieux diagnostiquer les causes racines lorsque 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é.

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 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 SQL Server 2017 (14.x), 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 n’est pas disponible.

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. Si votre base de données est une base de données de test ou une base de données nouvellement créée, faites une sauvegarde de la base de données. Sur le serveur SQL Server principal, exécutez le script Transact-SQL (T-SQL) suivant pour créer et sauvegarder une base de données appelée db1 :

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/data/db1.bak';

Sur le réplica SQL Server principal, exécutez le script T-SQL suivant pour ajouter une base de données appelée db1 à un groupe de disponibilité appelé 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;
GO

Ce groupe de disponibilité n’est pas une configuration à haute disponibilité. Si vous avez besoin d’une haute disponibilité, suivez les instructions de la section Configurer le groupe de disponibilité Always On SQL Server afin d’obtenir une haute disponibilité sous 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_desc a pour valeur SYNCHRONIZED.
Affectez la valeur 1 à REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT.

Le script suivant définit REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT sur 1 sur un groupe de disponibilité nommé ag1. Avant d’exécuter le script suivant, remplacez ag1 par 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] OFFLINE
```

Promouvez 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 SECONDARY et 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 RESUME
```
Recré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] OFFLINE
```
S’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.

Partage via

Configurer un groupe de disponibilité SQL Server pour l’échelle lecture sur Linux

Prérequis

Installer SQL Server

Activer les groupes de disponibilité Always On

Activer une session d’événements AlwaysOn_health

Créer un certificat

Créer le certificat sur les serveurs secondaires

Créer les points de terminaison de mise en miroir de bases de données sur tous les réplicas

Créez le groupe de disponibilité

Joindre des instances SQL Server secondaires au groupe de disponibilité

Ajouter une base de données au groupe de disponibilité

Vérifier que la base de données est créée sur les serveurs secondaires

Se connecter à des réplicas secondaires en lecture seule

Basculer le réplica principal sur un groupe de disponibilité avec échelle lecture

Basculement manuel sans perte de données

Basculement manuel forcé avec perte de données

Commentaires

Ressources supplémentaires

Partage via

Configurer un groupe de disponibilité SQL Server pour l’échelle lecture sur Linux

Prérequis

Installer SQL Server

Activer les groupes de disponibilité Always On

Activer une session d’événements AlwaysOn_health

Créer un certificat

Créer le certificat sur les serveurs secondaires

Créer les points de terminaison de mise en miroir de bases de données sur tous les réplicas

Créez le groupe de disponibilité

Joindre des instances SQL Server secondaires au groupe de disponibilité

Ajouter une base de données au groupe de disponibilité

Vérifier que la base de données est créée sur les serveurs secondaires

Se connecter à des réplicas secondaires en lecture seule

Basculer le réplica principal sur un groupe de disponibilité avec échelle lecture

Basculement manuel sans perte de données

Basculement manuel forcé avec perte de données

Contenu connexe

Commentaires

Ressources supplémentaires