Creare un gruppo di disponibilità Always On con Transact-SQL (T-SQL)

Si applica a: SQL Server

Questo argomento descrive come usare Transact-SQL per creare e configurare un gruppo di disponibilità nelle istanze di SQL Server in cui è abilitata la funzionalità dei gruppi di disponibilità Always On. Tramite un gruppo di disponibilità vengono definiti un set di database utente di cui verrà eseguito il failover come unità singola e un set di partner di failover, noti come repliche di disponibilità, che supportano il failover.

Prerequisiti, restrizioni e raccomandazioni

• Prima di creare un gruppo di disponibilità, verificare che le istanze di SQL Server che ospitano repliche di disponibilità si trovino in un nodo del Clustering di failover di Windows Server (Windows Server Failover Clustering, WSFC) diverso all'interno dello stesso cluster di failover WSFC. Inoltre, verificare che ciascuna delle istanze del server soddisfi tutti gli altri prerequisiti dei gruppi di disponibilità Always On. Per altre informazioni, si consiglia di leggere Prerequisiti, restrizioni e raccomandazioni per i gruppi di disponibilità Always On (SQL Server).

Autorizzazioni

Sono necessarie l'appartenenza al ruolo predefinito del server sysadmin e l'autorizzazione server CREATE AVAILABILITY GROUP oppure l'autorizzazione ALTER ANY AVAILABILITY GROUP o CONTROL SERVER.

Utilizzo di Transact-SQL per creare e configurare un gruppo di disponibilità

Riepilogo delle attività e istruzioni Transact-SQL corrispondenti

Nella tabella seguente sono elencate le attività di base necessarie per la creazione e la configurazione di un gruppo di disponibilità e vengono indicate le istruzioni Transact-SQL da usare per queste attività. È necessario eseguire le attività dei gruppi di disponibilità Always On nell'ordine con cui sono elencate nella tabella.

* Per eseguire un'attività specifica, connettersi alle istanze del server indicate.

Utilizzo di Transact-SQL

1. Connettersi all'istanza del server che dovrà ospitare la replica primaria.

2. Creare il gruppo di disponibilità usando l'istruzione Transact-SQL CREATE AVAILABILITY GROUP.

3. Creare un join della nuova replica secondaria al gruppo di disponibilità. Per altre informazioni, vedere Unire una replica secondaria a un gruppo di disponibilità (SQL Server).

4. Per ogni database nel gruppo di disponibilità, creare un database secondario ripristinando i backup recenti del database primario, usando RESTORE WITH NORECOVERY. Per altre informazioni, vedere Esempio: Configurazione di un gruppo di disponibilità in cui viene usata l'autenticazione di Windows (Transact-SQL), a partire dal passaggio per il ripristino del backup di database.

5. Creare un join di ogni nuovo database secondario al gruppo di disponibilità. Per altre informazioni, vedere Unire una replica secondaria a un gruppo di disponibilità (SQL Server).

Esempio: Configurazione di un gruppo di disponibilità in cui viene usata l'autenticazione di Windows

In questo esempio viene creata una procedura di configurazione dei gruppi di disponibilità Always On di esempio in cui si usa Transact-SQL per configurare endpoint del mirroring del database in cui si usa usata l'autenticazione di Windows, nonché per creare e configurare un gruppo di disponibilità e i relativi database secondari.

In questo esempio sono incluse le sezioni seguenti:

• Prerequisiti per l'utilizzo della procedura di configurazione di esempio

• Procedura di configurazione di esempio

• Esempio di codice completo per la procedura di configurazione di esempio

Prerequisiti per l'utilizzo della procedura di configurazione di esempio

Questa procedura di esempio prevede i requisiti seguenti:

• Le istanze del server devono supportare i gruppi di disponibilità Always On. Per altre informazioni, vedere Prerequisiti, restrizioni e consigli per i gruppi di disponibilità Always On (SQL Server).

• Devono essere presenti due database di esempio, MyDb1 e MyDb2, nell'istanza del server che ospiterà la replica primaria. Gli esempi di codice seguenti consentono di creare e configurare questi due database, nonché di creare un backup completo di ognuno di essi. Eseguire questi esempi di codice nell'istanza del server in cui si desidera creare il gruppo di disponibilità di esempio. Questa istanza del server ospiterà la replica primaria iniziale del gruppo di disponibilità di esempio.

  1. L'esempio Transact-SQL seguente consente di creare questi database e di modificarli in modo da usare il modello di recupero con registrazione completa:

     -- 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. Nell'esempio di codice seguente viene creato un backup completo del database di MyDb1 e MyDb2. In questo esempio di codice si usa una condivisione di backup fittizia, \\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  
     

[TopOfExample]

Procedura di configurazione di esempio

In questa configurazione di esempio sarà creata la replica di disponibilità in due istanze del server autonome i cui account del servizio vengono eseguiti in domini differenti, ma trusted,DOMAIN1 e DOMAIN2.

Nella tabella seguente sono riepilogati i valori utilizzati in questa configurazione di esempio.

1. Creare un endpoint del mirroring del database denominato dbm_endpoint nell'istanza del server in cui si intende creare il gruppo di disponibilità. Si tratta di un'istanza denominata AgHostInstance in COMPUTER01. In questo endpoint si usa la porta 7022. Si noti che la replica primaria sarà ospitata nell'istanza del server in cui si crea il gruppo di disponibilità.

   -- 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. Creare un endpoint dbm_endpoint nell'istanza del server in cui sarà ospitata la replica secondaria. Si tratta dell'istanza del server predefinita in COMPUTER02. In questo endpoint si utilizza la porta 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. Nota

   Se gli account del servizio delle istanze del server in cui dovranno essere ospitate le repliche di disponibilità sono eseguiti con lo stesso account di dominio, questo passaggio non è necessario. Ignorarlo e passare direttamente al successivo.

   Se gli account del servizio delle istanze del server vengono eseguiti con utenti di dominio diversi, in ogni istanza del server creare un account di accesso per l'altra istanza del server e concedere a questo account l'autorizzazione per l'accesso all'endpoint del mirroring del database locale.

   Nell'esempio di codice seguente vengono illustrate le istruzioni Transact-SQL per la creazione di un account di accesso e la concessione dell'autorizzazione in un endpoint. L'account di dominio dell'istanza del server remoto è rappresentato come nome_dominio\nome_utente.

   -- 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. Nell'istanza del server in cui si trovano i database utente creare il gruppo di disponibilità.

   Nell'esempio di codice seguente si crea un gruppo di disponibilità denominato MyAG nell'istanza del server in cui sono stati creati i database di esempio, MyDb1 e MyDb2. Si specifica innanzitutto l'istanza del server locale, AgHostInstance, su COMPUTER01 . Questa istanza ospiterà la replica primaria iniziale. Si specifica un'istanza del server remota, l'istanza del server predefinita in COMPUTER02, in cui viene ospitata una replica secondaria. Entrambe le repliche di disponibilità sono configurate per usare la modalità con commit asincrono con failover manuale. Per le repliche con commit asincrono il failover manuale indica un failover forzato con possibile perdita di dati.

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

   Per altri esempi di codice Transact-SQL per la creazione di un gruppo di disponibilità, vedere CREATE AVAILABILITY GROUP (Transact-SQL).

5. Nell'istanza del server in cui viene ospitata la replica secondaria creare un join della replica secondaria al gruppo di disponibilità.

   Nell'esempio di codice seguente viene creato un join della replica secondaria in COMPUTER02 al gruppo di 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. Nell'istanza del server che ospita la replica secondaria creare i database secondari.

   L'esempio di codice seguente crea i database secondari MyDb1 e MyDb2 ripristinando i backup dei database tramite 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. Nell'istanza del server in cui viene ospitata la replica primaria eseguire il backup del log delle transazioni in ognuno dei database primari.

   Importante

   Quando si configura un gruppo di disponibilità reale, prima di eseguire questo backup del log è consigliabile sospendere le attività di backup del log per i database primari fino a quando non è stato creato un join dei database secondari corrispondenti al gruppo di disponibilità.

   Nell'esempio di codice seguente viene creato un backup del log delle transazioni in MyDb1 e 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
   

   Suggerimento

   In genere, è necessario eseguire un backup del log in ogni database primario, quindi ripristinare tale backup nel database secondario corrispondente utilizzando WITH NORECOVERY. Questo backup del log potrebbe tuttavia non essere necessario se il database è stato appena creato e non è ancora stato eseguito alcun backup del log oppure se il modello di recupero è stato appena modificato da SIMPLE a FULL.

8. Nell'istanza del server che ospita la replica secondaria applicare i backup del log ai database secondari.

   L'esempio di codice seguente applica backup ai database secondari MyDb1 e MyDb2 ripristinando i backup dei database tramite RESTORE WITH NORECOVERY.

   Importante

   Quando si prepara un database secondario reale, è necessario applicare ogni backup del log eseguito dopo il backup del database da cui è stato creato il database secondario, a partire da quello meno recente e utilizzando sempre RESTORE WITH NORECOVERY. Naturalmente, se si ripristinano sia il backup completo del database che il backup differenziale, è necessario applicare solo i backup del log eseguiti dopo il backup differenziale.

   -- 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. Nell'istanza del server che ospita la replica secondaria creare un join dei nuovi database secondari al gruppo di disponibilità.

   L'esempio di codice seguente crea i join dei database secondari MyDb1 e MyDb2 al gruppo di 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  
   

Esempio di codice completo per la procedura di configurazione di esempio

Nell'esempio seguente vengono uniti gli esempi di codice di tutti i passaggi della procedura di configurazione di esempio. Nella tabella seguente sono riepilogati i valori segnaposto utilizzati nell'esempio di codice. Per ulteriori informazioni sui passaggi di questo esempio di codice, vedere Prerequisiti per l'utilizzo della procedura di configurazione di esempio e Procedura di configurazione di esempio, precedentemente in questo argomento.

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

Attività correlate

Per configurare le proprietà della replica e del gruppo di disponibilità

• Modificare la modalità di disponibilità di una replica di disponibilità (SQL Server)

• Modificare la modalità di failover di una replica di disponibilità (SQL Server)

• Creare o configurare un listener del gruppo di disponibilità (SQL Server)

• Configurare i criteri di failover flessibili per controllare le condizioni per il failover automatico (Gruppi di disponibilità Always On)

• Specifica dell'URL dell'endpoint quando si aggiunge o si modifica una replica di disponibilità (SQL Server)

• Configurare il backup su repliche di disponibilità (SQL Server)

• Configurare l'accesso in sola lettura in una replica di disponibilità (SQL Server)

• Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server)

• Modificare il periodo di timeout della sessione per una replica di disponibilità (SQL Server)

Per completare la configurazione del gruppo di disponibilità

• Creare un join di una replica secondaria in un gruppo di disponibilità (SQL Server)

• Preparare manualmente un database secondario per un gruppo di disponibilità (SQL Server)

• Creare un join di un database secondario in un gruppo di disponibilità (SQL Server)

• Creare o configurare un listener del gruppo di disponibilità (SQL Server)

Modalità alternative di creazione di un gruppo di disponibilità

• Usare la Creazione guidata Gruppo di disponibilità (SQL Server Management Studio)

• Usare la finestra di dialogo Nuovo gruppo di disponibilità (SQL Server Management Studio)

• Creare un gruppo di disponibilità (SQL Server PowerShell)

Per abilitare i gruppi di disponibilità AlwaysOn

• Abilitare e disabilitare i gruppi di disponibilità Always On (SQL Server)

Per configurare un endpoint del mirroring del database

• Creare un endpoint del mirroring del database per i gruppi di disponibilità Always On (SQL Server PowerShell)

• Creare un endpoint del mirroring del database per l'autenticazione Windows (Transact-SQL)

• Utilizzare certificati per un endpoint del mirroring del database (Transact-SQL)

• Specifica dell'URL dell'endpoint quando si aggiunge o si modifica una replica di disponibilità (SQL Server)

Per risolvere i problemi relativi alla configurazione dei gruppi di disponibilità AlwaysOn

• Risolvere i problemi relativi alla configurazione di Gruppi di disponibilità Always On (SQL Server)

• Risolvere i problemi relativi a un'operazione di aggiunta file non riuscita (Gruppi di disponibilità Always On)

Contenuto correlato

• Blog:

  serie di informazioni su HADRON riguardanti l'uso del pool di lavoro per database abilitati HADRON in AlwaysOn

  SQL Server AlwaysOn Team Blog: blog ufficiale del team di SQL Server AlwaysOn

  Pagina relativa ai blog del Servizio Supporto Tecnico Clienti per gli ingegneri di SQL Server

• White paper:

  Microsoft SQL Server Always On Solutions Guide for High Availability and Disaster Recovery (Guida alle soluzioni AlwaysOn di Microsoft SQL Server per la disponibilità elevata e il ripristino di emergenza)

  Pagina relativa ai white paper del team di consulenza clienti di SQL Server

Vedi anche

Endpoint del mirroring del database (SQL Server)
Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server)
Listener del gruppo di disponibilità, connettività client e failover dell'applicazione (SQL Server)
Prerequisiti, restrizioni e consigli per i gruppi di disponibilità AlwaysOn (SQL Server)