Créer un groupe de disponibilité (Transact-SQL)

Cette rubrique explique comment utiliser Transact-SQL pour créer et configurer un groupe de disponibilité sur des instances de SQL Server 2014 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.

Avant de commencer

Nous vous recommandons fortement de lire cette section avant d'essayer de créer votre premier groupe de disponibilité.

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 de serveur répond à toutes les autres conditions préalables requises pour les groupes de disponibilité Always On. Pour plus d’informations, nous vous recommandons vivement de lire les prérequis, restrictions et recommandations pour les groupes de disponibilité AlwaysOn (SQL Server).

Sécurité

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.

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 des groupes de disponibilité Always On doivent être effectuées dans la séquence dans laquelle elles sont présentées dans le tableau.

^* Pour effectuer une tâche donnée, connectez-vous à l’instance ou aux instances de serveur indiquées.

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

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 des groupes de disponibilité Always On qui utilise Transact-SQL pour configurer des points de terminaison de mise en miroir de bases de données qui utilisent l’authentification Windows et pour 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

• Exemple de procédure de configuration

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

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 les groupes de disponibilité Always On. Pour plus d’informations, consultez Conditions préalables, restrictions et recommandations pour les groupes de disponibilité AlwaysOn (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
     

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.

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'   
       WITHNOFORMAT  
   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.

-- 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'   
    WITHNOFORMAT  
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

• Modifier le mode de disponibilité d'un réplica de disponibilité (SQL Server)

• Modifier le mode de basculement d'un réplica de disponibilité (SQL Server)

• Créer ou configurer un écouteur de groupe de disponibilité (SQL Server)

• Configurer la stratégie de basculement flexible pour contrôler les conditions de basculement automatique (groupes de disponibilité AlwaysOn)

• Spécifier l'URL de point de terminaison lors de l'ajout ou lors de la modification d'un réplica de disponibilité (SQL Server)

• Configurer la sauvegarde sur des réplicas de disponibilité (SQL Server)

• Configurer l'accès en lecture seule sur un réplica de disponibilité (SQL Server)

• Configurer le routage en lecture seule pour un groupe de disponibilité (SQL Server)

• Modifier le délai d'expiration de session pour un réplica de disponibilité (SQL Server)

Pour terminer la configuration du groupe de disponibilité

• Joindre un réplica secondaire à un groupe de disponibilité (SQL Server)

• Préparer manuellement une base de données secondaire pour un groupe de disponibilité (SQL Server)

• Joindre une base de données secondaire à un groupe de disponibilité (SQL Server)

• Créer ou configurer un écouteur de groupe de disponibilité (SQL Server)

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

• Utiliser l’Assistant Groupe de disponibilité (SQL Server Management Studio)

• Utiliser la boîte de dialogue Nouveau groupe de disponibilité (SQL Server Management Studio)

• Créer un groupe de disponibilité (SQL Server PowerShell)

Pour activer les groupes de disponibilité AlwaysOn

• Activer et désactiver des groupes de disponibilité AlwaysOn (SQL Server)

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

• Créer un point de terminaison de mise en miroir de bases de données pour les groupes de disponibilité AlwaysOn (SQL Server PowerShell)

• Créer un point de terminaison de mise en miroir de bases de données pour l'authentification Windows (Transact-SQL)

• Utiliser des certificats pour un point de terminaison de mise en miroir de bases de données (Transact-SQL)

• Spécifier l'URL de point de terminaison lors de l'ajout ou lors de la modification d'un réplica de disponibilité (SQL Server)

Pour résoudre les problèmes de configuration des groupes de disponibilité AlwaysOn

• Résoudre les problèmes de configuration des groupes de disponibilité AlwaysOn (SQL Server)supprimés

• Résoudre les problèmes d’une opération d’ajout de fichier ayant échoué (groupes de disponibilité AlwaysOn)

Contenu associé

• Blogs :

  AlwaysON - Série d’apprentissage HADRON : Utilisation du pool de workers pour les bases de données compatibles HADRON

  Blogs de l’équipe SQL Server AlwaysOn : Blog officiel de l’équipe SQL Server AlwaysOn

  Blogs des ingénieurs du Service clientèle et du Support technique de SQL Server

• Vidéos :

  Microsoft SQL Server Code nommé « Denali » AlwaysOn Series, partie 1 : Présentation de la solution de haute disponibilité de nouvelle génération

  Microsoft SQL Server Code nommé « Denali » AlwaysOn Series, partie 2 : Création d’une solution de haute disponibilité stratégique à l’aide d’AlwaysOn

• Livres blancs :

  Guide des solutions AlwaysOn de Microsoft SQL Server pour la haute disponibilité et la récupération d’urgence

  Livres blancs de Microsoft pour SQL Server 2012

  Livres blancs de l'équipe de consultants clients de SQL Server

Voir aussi

Point de terminaison de mise en miroir de bases de données (SQL Server)
Vue d’ensemble des groupes de disponibilité AlwaysOn (SQL Server)
Écouteurs de groupe de disponibilité, connectivité client et basculement d’application (SQL Server)