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 2012 sur lesquelles la fonctionnalité Groupes de disponibilité AlwaysOn 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.

[!REMARQUE]

Pour une introduction aux groupes de disponibilité, consultez Vue d'ensemble des groupes de disponibilité AlwaysOn (SQL Server).

• Avant de commencer :  

  Configuration requise

  Sécurité

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

• Pour créer et configurer un groupe de disponibilité en utilisant :  Transact-SQL

• **Exemple : ** configuration d'un groupe de disponibilité qui utilise l'authentification Windows

• Tâches associées

• Contenu associé

[!REMARQUE]

En guise d'alternative à Transact-SQL, vous pouvez utiliser l'Assistant Création d'un groupe de disponibilité ou les applets de commande PowerShell SQL Server. Pour plus d'informations, consultez Utiliser l'Assistant Nouveau 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).

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 du serveur répond à toutes les autres conditions requises relatives à Groupes de disponibilité AlwaysOn. Pour plus d'informations, nous vous recommandons fortement de lire Conditions préalables requises, restrictions et recommandations pour les groupes de disponibilité AlwaysOn (SQL Server).

Sécurité

Autorisations

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

[Haut de la page]

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é AlwaysOn 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 aux instances de serveur indiquées.

[Haut de la page]

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

[!REMARQUE]

Pour obtenir un exemple de procédure de configuration qui contient 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 CREATE AVAILABILITY GROUP Transact-SQL.

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 Créer un groupe de disponibilité (Transact-SQL), en démarrant avec l'étape qui permet de restaurer la sauvegarde de la 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).

[Haut de la page]

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

• 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 Groupes de disponibilité AlwaysOn. Pour plus d'informations, consultez Conditions préalables requises, 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
     

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

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. [!REMARQUE]

   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 comme domain_name\user_name.

     -- 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 des exemples de code Transact-SQL supplémentaires pour la création d'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
   

[Débutdel'exemple]

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.

[!REMARQUE]

Pour obtenir des exemples de code Transact-SQL supplémentaires pour la création d'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' 
    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

[Débutdel'exemple]

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 du 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 Nouveau 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 les 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 des problèmes de configuration des groupes de disponibilité AlwaysOn (SQL Server)

• Résoudre une opération d'ajout de fichier ayant échoué (groupes de disponibilité AlwaysOn)

[Haut de la page]

Contenu connexe

• **Blogs : **

  AlwaysON - HADRON Learning Series : Worker Pool Usage for HADRON Enabled Databases (en anglais)

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

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

• **Vidéos : **

  Microsoft SQL Server, nom de code « Denali », série AlwaysOn, Partie 1 : Présentation de la solution haute disponibilité de la prochaine génération

  Microsoft SQL Server, nom de code « Denali », série AlwaysOn, Partie 2 : Génération d'une solution haute disponibilité critique à l'aide d'AlwaysOn

• **Livres blancs : **

  Guide de solutions Microsoft SQL Server AlwaysOn 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

[Haut de la page]

Voir aussi

Concepts

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)

Conditions préalables requises, restrictions et recommandations pour les groupes de disponibilité AlwaysOn (SQL Server)