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

  • Articolo

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.

Nota

Per un'introduzione ai gruppi di disponibilità, vedere Panoramica dei gruppi di disponibilità Always On (SQL Server).

Nota

In alternativa all'uso di Transact-SQL, è possibile usare la procedura guidata Crea gruppo di disponibilità o i cmdlet di SQL Server PowerShell. Per altre informazioni, vedere Usare la Creazione guidata Gruppo di disponibilità (SQL Server Management Studio), Usare la finestra di dialogo Nuovo gruppo di disponibilità (SQL Server Management Studio), o Creare un gruppo di disponibilità (SQL Server PowerShell).

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.

Attività Istruzione/i Transact-SQL Posizione in cui eseguire l'attività*****
Creare un endpoint del mirroring del database (una volta per ogni istanza di SQL Server) CREATE ENDPOINTendpointName ... FOR DATABASE_MIRRORING Eseguire in ogni istanza del server in cui non è presente l'endpoint del mirroring del database.
Creare un gruppo di disponibilità CREATE AVAILABILITY GROUP Eseguire nell'istanza del server che dovrà ospitare la replica primaria iniziale.
Creare un join della replica secondaria al gruppo di disponibilità ALTER AVAILABILITY GROUPnome_gruppo JOIN Eseguire in ogni istanza del server in cui è ospitata una replica secondaria.
Preparare il database secondario BACKUP e RESTORE. Creare i backup nell'istanza del server in cui è ospitata la replica primaria.

Ripristinare i backup in ogni istanza del server che ospita una replica secondaria, utilizzando RESTORE WITH NORECOVERY.
Avviare la sincronizzazione dei dati creando un join di ogni database secondario al gruppo di disponibilità ALTER DATABASE_database SET HADR AVAILABILITY GROUP = nome_gruppo Eseguire in ogni istanza del server in cui è ospitata una replica secondaria.

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

Utilizzo di Transact-SQL

Nota

Per una procedura di configurazione di esempio contenente esempi di codice di ognuna di queste istruzioni Transact-SQL, vedere Esempio: Configurazione di un gruppo di disponibilità in cui viene usata l'autenticazione di Windows.

  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

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.

Ruolo iniziale Sistema Ospitare l'istanza di SQL Server
Primaria COMPUTER01 AgHostInstance
Secondari COMPUTER02 Istanza predefinita

  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.

Segnaposto Descrizione
\\FILESERVER\SQLbackups Condivisione di backup fittizia.
\\FILESERVER\SQLbackups\MyDb1.bak File di backup per MyDb1.
\\FILESERVER\SQLbackups\MyDb2.bak File di backup per MyDb2.
7022 Numero di porta assegnato a ogni endpoint del mirroring del database.
COMPUTER01\AgHostInstance Istanza del server che ospita la replica primaria iniziale.
COMPUTER02 Istanza del server in cui viene ospitata la replica secondaria iniziale. Si tratta dell'istanza del server predefinita in COMPUTER02.
dbm_endpoint Nome specificato per ogni endpoint del mirroring del database.
MyAG Nome del gruppo di disponibilità di esempio.
MyDb1 Nome del primo database di esempio.
MyDb2 Nome del secondo database di esempio.
DOMAIN1\user1 Account del servizio dell'istanza del server che dovrà ospitare la replica primaria iniziale.
DOMAIN2\user2 Account del servizio dell'istanza del server in cui dovrà essere ospitata la replica secondaria iniziale.
TCP://COMPUTER01.Adventure-Works.com:7022 URL dell'endpoint dell'istanza AgHostInstance di SQL Server in COMPUTER01.
TCP://COMPUTER02.Adventure-Works.com:5022 URL dell'endpoint dell'istanza predefinita di SQL Server in COMPUTER02.

Nota

Per altri esempi di codice Transact-SQL per la creazione di un gruppo di disponibilità, vedere 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

Attività correlate

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

Per completare la configurazione del gruppo di disponibilità

Modalità alternative di creazione di un gruppo di disponibilità

Per abilitare i gruppi di disponibilità AlwaysOn

Per configurare un endpoint del mirroring del database

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

Contenuto correlato

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)