Créer un groupe de disponibilité Always On à l’aide de Transact-SQL (T-SQL)

  • Article

S'applique à : SQL Server

Cette rubrique explique comment utiliser Transact-SQL pour créer et configurer un groupe de disponibilité sur des instances SQL Server sur lesquelles la fonctionnalité Groupes de disponibilité Always On est activée. Un groupe de disponibilité définit un jeu de bases de données utilisateur qui basculent en tant qu'unité unique et un jeu de partenaires de basculement, appelés réplicas de disponibilité, qui prennent en charge le basculement.

Notes

Pour obtenir une présentation des groupes de disponibilité, consultez Vue d’ensemble des groupes de disponibilité Always On (SQL Server).

Notes

Au lieu d’utiliser Transact-SQL, vous pouvez utiliser l’Assistant Création d’un groupe de disponibilité ou les cmdlets PowerShell SQL Server. Pour plus d’informations, consultez Utiliser l’Assistant Groupe de disponibilité (SQL Server Management Studio), Utiliser la boîte de dialogue Nouveau groupe de disponibilité (SQL Server Management Studio)ou Créer un groupe de disponibilité (SQL Server PowerShell).

Conditions préalables requises, restrictions et recommandations

  • Avant de créer un groupe de disponibilité, vérifiez que les instances de SQL Server qui hébergent les réplicas de disponibilité résident sur des nœuds WSFC (Windows Server Failover Clustering) différents au sein du même clustering de basculement WSFC. Vérifiez également que chaque instance du serveur répond à toutes les autres conditions requises relatives à Groupes de disponibilité Always On . Pour plus d’informations, nous vous recommandons vivement de consulter la rubrique Conditions préalables requises, restrictions et recommandations pour les groupes de disponibilité Always On (SQL Server).

Autorisations

Requiert l’appartenance au rôle serveur fixe sysadmin et l’autorisation de serveur CREATE AVAILABILITY GROUP, l’autorisation ALTER ANY AVAILABILITY GROUP ou l’autorisation CONTROL SERVER.

Utilisation de Transact-SQL pour créer et configurer un groupe de disponibilité

Résumé des tâches et instructions Transact-SQL correspondantes

Le tableau suivant répertorie les tâches de base impliquées dans la création et la configuration d’un groupe de disponibilité et indique les instructions Transact-SQL à utiliser pour ces tâches. Les tâches Groupes de disponibilité Always On doivent être effectuées dans la séquence dans laquelle elles sont présentées dans le tableau.

Tâche Instruction(s) Transact-SQL Où effectuer la tâche*****
Créer le point de terminaison de mise en miroir de bases de données (une fois par instance SQL Server ) CREATE ENDPOINT endpointName ... FOR DATABASE_MIRRORING Exécutez sur chaque instance de serveur dans laquelle le point de terminaison de mise en miroir de bases de données est manquant.
Créer un groupe de disponibilité CREATE AVAILABILITY GROUP Exécutez sur l'instance de serveur qui hébergera le réplica principal initial.
Joindre le réplica secondaire au groupe de disponibilité ALTER AVAILABILITY GROUP nom_groupe JOIN Exécutez sur chaque instance de serveur qui héberge un réplica secondaire.
Préparer la base de données secondaire BACKUP et RESTORE. Créez des sauvegardes sur l'instance de serveur qui héberge le réplica principal.

Restaurez les sauvegardes sur chaque instance de serveur qui héberge un réplica secondaire, à l'aide de RESTORE WITH NORECOVERY.
Démarrer la synchronisation des données en joignant chaque base de données secondaire au groupe de disponibilité ALTER DATABASE nom_base_de_données SET HADR AVAILABILITY GROUP = nom_groupe Exécutez sur chaque instance de serveur qui héberge un réplica secondaire.

*Pour effectuer une tâche donnée, connectez-vous aux instances de serveur indiquées.

Utilisation de Transact-SQL

Notes

Pour avoir un exemple de procédure de configuration contenant des exemples de code de chacune de ces instructions Transact-SQL, consultez Exemple : configuration d’un groupe de disponibilité qui utilise l’authentification Windows.

  1. Connectez-vous à l'instance de serveur qui hébergera le réplica principal.

  2. Créez le groupe de disponibilité en utilisant l’instruction Transact-SQL CREATE AVAILABILITY GROUP.

  3. Joignez le nouveau réplica secondaire au groupe de disponibilité. Pour plus d’informations, consultez Joindre un réplica secondaire à un groupe de disponibilité (SQL Server).

  4. Pour chaque base de données dans le groupe de disponibilité, créez une base de données secondaire en restaurant des sauvegardes récentes de la base de données primaire, à l'aide de RESTORE WITH NORECOVERY. Pour plus d’informations, consultez Exemple : Configuration d’un groupe de disponibilité à l’aide de l’authentification Windows (Transact-SQL), en commençant par l’étape de restauration de la sauvegarde de base de données.

  5. Joignez chaque nouvelle base de données secondaire au groupe de disponibilité. Pour plus d’informations, consultez Joindre un réplica secondaire à un groupe de disponibilité (SQL Server).

Exemple : configuration d’un groupe de disponibilité qui utilise l’authentification Windows

Cet exemple crée un exemple de procédure de configuration Groupes de disponibilité Always On qui utilise Transact-SQL pour installer des points de terminaison de mise en miroir de bases de données qui utilisent l’authentification Windows, et créer et configurer un groupe de disponibilité et ses bases de données secondaires.

Cet exemple contient les sections suivantes :

Conditions préalables requises pour l'utilisation de l'exemple de procédure de configuration

Cet exemple de procédure présente les conditions suivantes :

  • Les instances de serveur doivent prendre en charge Groupes de disponibilité Always On. Pour plus d’informations, consultez Prérequis, restrictions et suggestions pour les groupes de disponibilité Always On (SQL Server).

  • Deux exemples de bases de données, MyDb1 et MyDb2, doivent exister sur l'instance de serveur qui hébergera le réplica principal. Les exemples de code suivants créent et configurent ces deux bases de données et créent une sauvegarde complète de chacune d'elles. Exécutez ces exemples de code sur l'instance de serveur sur laquelle vous envisagez de créer l'exemple de groupe de disponibilité. Cette instance de serveur hébergera le réplica principal initial de l'exemple de groupe de disponibilité.

    1. L’exemple Transact-SQL suivant crée ces bases de données et les modifie pour utiliser le mode de récupération complète :

      -- Create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  

CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO

    2. L'exemple de code suivant crée une sauvegarde complète des bases de données MyDb1 et MyDb2. Cet exemple de code utilise un partage de sauvegarde fictif, \\FILESERVER\SQLbackups.

      -- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  

BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO

[Débutdel'exemple]

Exemple de procédure de configuration

Dans cet exemple de configuration, le réplica de disponibilité sera créé sur deux instances de serveur autonomes dont les comptes de service s'exécutent sous des domaines différents, mais approuvés (DOMAIN1 et DOMAIN2).

Le tableau ci-dessous récapitule les valeurs utilisées dans cet exemple de configuration.

Rôle initial Système Instance SQL Server hôte
Principal COMPUTER01 AgHostInstance
Secondary COMPUTER02 Instance par défaut.

  1. Créez un point de terminaison de mise en miroir de bases de données nommé dbm_endpoint sur l’instance de serveur sur laquelle vous envisagez de créer le groupe de disponibilité (il s’agit d’une instance nommée AgHostInstance sur COMPUTER01). Ce point de terminaison utilise le port 7022. Notez que l'instance de serveur sur laquelle vous créez le groupe de disponibilité hébergera le réplica principal.

    -- Create endpoint on server instance that hosts the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

  2. Créez un point de terminaison dbm_endpoint sur l’instance de serveur qui hébergera le réplica secondaire (il s’agit de l’instance de serveur par défaut sur COMPUTER02). Ce point de terminaison utilise le port 5022.

    -- Create endpoint on server instance that hosts the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

  3. Notes

    Si les comptes de service des instances de serveur qui hébergeront vos réplicas de disponibilité s'exécutent sous le même compte de domaine, cette étape est inutile. Ignorez-la et passez directement à l'étape suivante.

    Si les comptes de service des instances de serveur s'exécutent sous des comptes utilisateur de domaine différents, sur chaque instance de serveur, créez une connexion pour l'autre instance de serveur et accordez cette autorisation de connexion pour accéder au point de terminaison de mise en miroir de bases de données local.

    L’exemple de code suivant affiche les instructions Transact-SQL pour créer une connexion et lui accorder l’autorisation sur un point de terminaison. Le compte de domaine de l’instance de serveur distant est représenté ici en tant que nom_domaine\nom_utilisateur.

    -- If necessary, create a login for the service account, domain_name\user_name  
-- of the server instance that will host the other replica:  
USE master;  
GO  
CREATE LOGIN [domain_name\user_name] FROM WINDOWS;  
GO  
-- And Grant this login connect permissions on the endpoint:  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [domain_name\user_name];  
GO

  4. Sur l'instance de serveur où résident les bases de données utilisateur, créez le groupe de disponibilité.

    L'exemple de code suivant crée un groupe de disponibilité nommé MyAG sur l'instance de serveur sur laquelle les exemples de bases de données, MyDb1 et MyDb2, ont été créés. L'instance de serveur local, AgHostInstance, sur COMPUTER01 est spécifiée en premier. Cette instance hébergera le réplica principal initial. Une instance de serveur distant, l'instance de serveur par défaut sur COMPUTER02, est spécifiée pour héberger un réplica secondaire. Les deux réplicas de disponibilité sont configurés de manière à utiliser le mode de validation asynchrone avec basculement manuel (pour les réplicas à validation asynchrone, le basculement manuel correspond à un basculement forcé entraînant une éventuelle perte de données).

    -- Create the availability group, MyAG:   
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',  
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         );   
GO

    Pour obtenir d’autres exemples de code Transact-SQL permettant de créer un groupe de disponibilité, consultez CREATE AVAILABILITY GROUP (Transact-SQL).

  5. Sur l'instance de serveur qui héberge le réplica secondaire, joignez le réplica secondaire au groupe de disponibilité.

    L'exemple de code suivant joint le réplica secondaire sur COMPUTER02 au groupe de disponibilité MyAG .

    -- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO

  6. Sur l'instance de serveur qui héberge le réplica secondaire, créez les bases de données secondaires.

    L'exemple de code suivant crée les bases de données secondaires MyDb1 et MyDb2 en restaurant des sauvegardes de base de données à l'aide de l'option RESTORE WITH NORECOVERY.

    -- On the server instance that hosts the secondary replica,   
-- Restore database backups using the WITH NORECOVERY option:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  

RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO

  7. Sur l'instance de serveur qui héberge le réplica principal, sauvegardez le journal des transactions sur chacune des bases de données primaires.

    Important

    Lorsque vous configurez un vrai groupe de disponibilité, nous vous recommandons, avant d'effectuer cette sauvegarde de fichier journal, d'interrompre les tâches de sauvegarde de fichier journal pour vos bases de données primaires jusqu'à ce que vous ayez joint les bases de données secondaires correspondantes au groupe de disponibilité.

    L'exemple de code suivant crée une sauvegarde du journal des transactions sur MyDb1 et MyDb2.

    -- On the server instance that hosts the primary replica,   
-- Backup the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  

BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT;  
GO

    Conseil

    En général, une sauvegarde de fichier journal doit être effectuée sur chaque base de données primaire, puis doit être restaurée sur la base de données secondaire correspondante (en utilisant l'option WITH NORECOVERY). Toutefois, cette sauvegarde du fichier journal peut s'avérer superflue, si la base de données vient d'être créée et qu'aucune sauvegarde du fichier journal n'a été encore réalisée ou si le mode de récupération vient d'être modifié de SIMPLE à FULL.

  8. Sur l'instance de serveur qui héberge le réplica secondaire, appliquez des sauvegardes de fichier journal aux bases de données secondaires.

    L'exemple de code suivant applique les sauvegardes aux bases de données secondaires MyDb1 et MyDb2 en restaurant des sauvegardes de base de données à l'aide de l'option RESTORE WITH NORECOVERY.

    Important

    Lorsque vous préparez une base de données secondaire réelle, vous devez appliquer chaque sauvegarde de fichier journal effectuée depuis la sauvegarde de la base de données à partir de laquelle vous avez créé la base de données secondaire, en démarrant avec la plus ancienne et en utilisant toujours l'option RESTORE WITH NORECOVERY. Bien sûr, si vous restaurez à la fois des sauvegardes de bases de données complètes et différentielles, vous devez uniquement appliquer les sauvegardes de fichier journal effectuées après la sauvegarde différentielle.

    -- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO

  9. Sur l'instance de serveur qui héberge le réplica secondaire, joignez les nouvelles bases de données secondaires au groupe de disponibilité.

    L'exemple de code suivant, joint la base de données secondaire MyDb1 , puis les bases de données secondaires MyDb2 au groupe de disponibilité MyAG .

    -- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Exemple de code complet pour l'exemple de procédure de configuration

L'exemple suivant fusionne les exemples de code de toutes les étapes de l'exemple de procédure configuration. Le tableau suivant répertorie les valeurs d'espace réservé utilisées dans cet exemple de code. Pour plus d'informations sur les étapes de cet exemple de code, consultez Conditions préalables requises pour l'utilisation de l'exemple de procédure de configuration et Exemple de procédure de configuration, plus avant dans cette rubrique.

Espace réservé Description
\\FILESERVER\SQLbackups Partage de sauvegarde fictif.
\\FILESERVER\SQLbackups\MyDb1.bak Fichier de sauvegarde pour MyDb1.
\\FILESERVER\SQLbackups\MyDb2.bak Fichier de sauvegarde pour MyDb2.
7022 Numéro de port affecté à chaque point de terminaison de mise en miroir de bases de données.
COMPUTER01\AgHostInstance Instance de serveur qui héberge le réplica principal initial.
COMPUTER02 Instance de serveur qui héberge le réplica secondaire initial. Il s'agit de l'instance de serveur par défaut sur COMPUTER02.
dbm_endpoint Nom spécifié pour chaque point de terminaison de mise en miroir de bases de données.
MyAG Nom de l'exemple de groupe de disponibilité.
MyDb1 Nom du premier exemple de base de données.
MyDb2 Nom du deuxième exemple de base de données.
DOMAINE1\utilisateur1 Compte de service de l'instance de serveur qui hébergera le réplica principal initial.
DOMAINE2\utilisateur2 Compte de service de l'instance de serveur qui hébergera le réplica secondaire initial.
TCP://COMPUTER01.Adventure-Works.com:7022 URL du point de terminaison de l'instance AgHostInstance de SQL Server sur COMPUTER01.
TCP://COMPUTER02.Adventure-Works.com:5022 URL de point de terminaison de l'instance par défaut de SQL Server sur COMPUTER02.

Notes

Pour obtenir d’autres exemples de code Transact-SQL permettant de créer un groupe de disponibilité, consultez CREATE AVAILABILITY GROUP (Transact-SQL).

-- on the server instance that will host the primary replica,   
-- create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  
  
CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO  
  
-- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  
  
BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO  
  
-- Create the endpoint on the server instance that will host the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- Create the endpoint on the server instance that will host the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the primary replica,   
-- create a login for the service account   
-- of the server instance that will host the secondary replica, DOMAIN2\user2,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN2\user2];  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the secondary replica,  
-- create a login for the service account   
-- of the server instance that will host the primary replica, DOMAIN1\user1,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
  
CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN1\user1];  
GO  
  
-- On the server instance that will host the primary replica,   
-- create the availability group, MyAG:  
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         );   
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO  
  
-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  
  
RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO  
  
-- Back up the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  
  
BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT  
GO  
  
-- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  
  
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Tâches associées

Pour configurer les propriétés du groupe de disponibilité et du réplica

Pour terminer la configuration du groupe de disponibilité

Autres méthodes de création d'un groupe de disponibilité

Pour activer les groupes de disponibilité Always On

Pour configurer un point de terminaison pour la mise en miroir de bases de données

Pour résoudre des problèmes de configuration des groupes de disponibilité Always On

Contenu associé

Voir aussi

Point de terminaison de mise en miroir de bases de données (SQL Server)
Vue d’ensemble des groupes de disponibilité Always On (SQL Server)
Écouteurs de groupe de disponibilité, connectivité client et basculement d’application (SQL Server)
Prérequis, restrictions et recommandations pour les groupes de disponibilité Always On (SQL Server)