Partager via

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

  • Article

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

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.

Icône de flèche utilisée avec le lien Retour en haut[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.

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 group_name 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 database_name SET HADR AVAILABILITY GROUP = group_name

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.

Icône de flèche utilisée avec le lien Retour en haut[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).

Icône de flèche utilisée avec le lien Retour en haut[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.

Rôle initial

Système

Instance SQL Server hôte

Principal

COMPUTER01

AgHostInstance

Secondaire

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

    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.

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.

DOMAIN1\user1

Compte de service de l'instance de serveur qui hébergera le réplica principal initial.

DOMAIN2\user2

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.

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

Icône de flèche utilisée avec le lien Retour en haut[Débutdel'exemple]

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

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

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

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Contenu connexe

Icône de flèche utilisée avec le lien Retour en haut[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)