Configurer une échelle lecture pour un groupe de disponibilité Always On

Article
04/26/2023

S'applique à : SQL Server

Vous pouvez configurer un groupe de disponibilité SQL Server AlwaysOn pour les charges de travail d’échelle lecture sur Windows. Il existe deux types d’architecture pour les groupes de disponibilité :

Une architecture visant la haute disponibilité qui utilise un gestionnaire de cluster pour améliorer la continuité d’activité et qui peut inclure des réplicas secondaires accessibles en lecture. Pour créer cette architecture haute disponibilité, consultez Créer et configurer des groupes de disponibilité sur Windows.
Une architecture qui prend en charge seulement des charges de travail avec échelle lecture.

Cet article explique comment créer un groupe de disponibilité sans gestionnaire de cluster pour les charges de travail avec échelle lecture. Cette architecture fournit uniquement une échelle lecture. Elle n’assure pas la haute disponibilité.

Notes

Un groupe de disponibilité avec CLUSTER_TYPE = NONE peut inclure des réplicas hébergés sur différentes plateformes de système d’exploitation. Il ne peut pas prendre en charge la haute disponibilité. Pour le système d’exploitation Linux, consultez Configurer un groupe de disponibilité SQL Server pour l’échelle lecture sur Linux.

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. Pour plus d’informations, consultez Installer SQL Server.

Activer les groupes de disponibilité AlwaysOn et redémarrer mssql-server

Notes

La commande suivante utilise des applets de commande du module sqlserver publié dans PowerShell Gallery. Vous pouvez installer ce module avec la commande Install-Module.

Activez les groupes de disponibilité AlwaysOn sur chaque réplica qui héberge une instance de SQL Server. Ensuite, redémarrez le service SQL Server. Exécutez la commande suivante pour activer et redémarrer les services SQL Server :

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

Activer une session d’événements AlwaysOn_health

Pour faciliter le diagnostic de la cause principale quand vous résolvez les problèmes d’un groupe de disponibilité, vous pouvez activer une session d’événements étendus (XEvents) des groupes de disponibilité AlwaysOn. Pour cela, 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 XEvents, consultez Événements étendus des groupes de disponibilité AlwaysOn.

Authentification du point de terminaison de mise en miroir de bases de données

Pour que la synchronisation fonctionne correctement, les réplicas impliqués dans le groupe de disponibilité avec échelle lecture doivent s’authentifier sur le point de terminaison. Les deux scénarios principaux que vous pouvez utiliser pour ce type d’authentification sont traités dans les sections suivantes.

Compte de service

Dans un environnement Active Directory où tous les réplicas secondaires sont joints au même domaine, SQL Server peut utiliser le compte de service pour l’authentification. Vous devez créer explicitement une connexion pour le compte de service sur chaque instance de SQL Server :

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

Dans les environnements où les réplicas secondaires ne sont peut-être pas joints à un domaine Active Directory, vous devez utiliser l’authentification SQL. Le script Transact-SQL suivant crée une connexion nommée dbm_login et un utilisateur nommé dbm_user. Mettez à jour le script avec un mot de passe fort. Pour créer l’utilisateur de point de terminaison de mise en miroir de bases de données, exécutez la commande suivante sur toutes les instances de SQL Server :

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Authentification par certificat

Si vous utilisez un réplica secondaire qui nécessite l’authentification SQL, utilisez un certificat pour l’authentification 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. Exécutez le script suivant sur l’instance principale de SQL Server pour créer le certificat :

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

À ce stade, votre réplica SQL Server principal a un certificat à l’emplacement c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer et une clé privée à l’emplacement c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\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é.

Sur chaque réplica secondaire, vérifiez que le compte de service pour l’instance de SQL Server dispose des autorisations d’accès au certificat.

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. La commande autorise également les utilisateurs à accéder au certificat. 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. Pour créer le certificat, exécutez le script suivant sur tous les réplicas secondaires :

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

Créer des 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ées ou hébergeant des réplicas de disponibilité. Le point de terminaison de mise en miroir de bases de données écoute sur un seul numéro de port TCP.

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 une autorisation de connexion au compte de service ou à la connexion SQL que vous avez créé au cours d’une étape précédente. Avant d’exécuter le script, remplacez les valeurs entre **< ... >**. Vous pouvez aussi 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;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

Le port TCP sur le pare-feu doit être ouvert pour le port de l’écouteur.

Pour plus d’informations, consultez Point de terminaison de mise en miroir de bases de données (SQL Server).

Créer un groupe de disponibilité

Créez un groupe de disponibilité. Définissez CLUSTER_TYPE = NONE. Pour chaque réplica, définissez également FAILOVER_MODE = NONE. Les applications clientes qui exécutent des charges de travail d’analytique et 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 du groupe de disponibilité avec SEEDING_MODE = AUTOMATIC. Ce paramètre fait que SQL Server crée 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. Pour joindre le groupe de disponibilité, exécutez le script Transact-SQL suivant sur chaque réplica SQL Server secondaire :

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. Pour créer et sauvegarder une base de données nommée db1, exécutez le script Transact-SQL suivant sur le serveur SQL Server principal :

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

Pour ajouter une base de données nommée db1 à un groupe de disponibilité nommé ag1, exécutez le script Transact-SQL suivant sur le réplica SQL Server principal :

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

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

Pour déterminer si la base de données db1 a été créée et si elle est synchronisée, exécutez la requête suivante sur chaque réplica SQL Server secondaire :

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é AlwaysOn pour SQL Server sur Linux ou dans Création et configuration de groupes de disponibilité sur Windows.

Se connecter à des réplicas secondaires en lecture seule

Vous pouvez vous connecter à des réplicas secondaires en lecture seule de deux façons :

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. Pour plus d’informations, consultez Accès en lecture aux réplicas secondaires.
Les applications peuvent aussi utiliser le routage en lecture seule, qui nécessite un écouteur. Si vous déployez un scénario d’échelle lecture sans gestionnaire de cluster, vous pouvez quand même créer un écouteur qui pointe vers l’adresse IP du réplica principal actuel et le même port que celui sur lequel écoute SQL Server. Vous devrez recréer l’écouteur pour qu’il pointe vers la nouvelle adresse IP principale après un basculement. Pour plus d’informations, consultez Routage en lecture seule.

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.

Notez que si vous utilisez un écouteur pour vous connecter, vous devrez recréer l’écouteur après avoir effectué le basculement.

Partager via

Configurer une échelle lecture pour un groupe de disponibilité Always On

Prérequis

Activer les groupes de disponibilité AlwaysOn et redémarrer mssql-server

Activer une session d’événements AlwaysOn_health

Authentification du point de terminaison de mise en miroir de bases de données

Compte de service

Authentification par certificat

Créer le certificat sur les serveurs secondaires

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

Créer un 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

Étapes suivantes

Commentaires

Ressources supplémentaires

Partager via

Configurer une échelle lecture pour un groupe de disponibilité Always On

Prérequis

Activer les groupes de disponibilité AlwaysOn et redémarrer mssql-server

Activer une session d’événements AlwaysOn_health

Authentification du point de terminaison de mise en miroir de bases de données

Compte de service

Authentification de la connexion SQL

Authentification par certificat

Créer le certificat sur les serveurs secondaires

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

Créer un 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

Étapes suivantes

Commentaires

Ressources supplémentaires