Condividi tramite


Creare un gruppo di disponibilità (Transact-SQL)

In questo argomento viene descritto come utilizzare Transact-SQL per creare e configurare un gruppo di disponibilità su istanze di SQL Server 2012 nelle quali è abilitata la funzionalità Gruppi di disponibilità AlwaysOn. Un gruppo di disponibilità definisce un set di database utente di cui eseguire 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 di Gruppi di disponibilità AlwaysOn (SQL Server).

  • Prima di iniziare:  

    Prerequisiti

    Sicurezza

    Riepilogo delle attività e istruzioni Transact-SQL corrispondenti

  • Per creare e configurare un gruppo di disponibilità, utilizzando: Transact-SQL

  • **Esempio: ** Configurazione di un gruppo di disponibilità in cui viene utilizzata l'autenticazione di Windows

  • Attività correlate

  • Contenuto correlato

[!NOTA]

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

Prima di iniziare

Prima di iniziare a creare il primo gruppo di disponibilità, è consigliabile leggere questa sezione.

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 Gruppi di disponibilità AlwaysOn. Per ulteriori informazioni, si consiglia di leggere Prerequisiti, restrizioni e consigli per i gruppi di disponibilità AlwaysOn (SQL Server).

Sicurezza

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.

Icona freccia utilizzata con il collegamento Torna all'inizio[Torna all'inizio]

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 utilizzare per queste attività. È necessario eseguire le attività Gruppi di disponibilità AlwaysOn 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 ENDPOINT endpointName … 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 GROUP group_name JOIN

Eseguire in ogni istanza del server in cui viene ospitata una replica secondaria.

Preparare il database secondario

BACKUP e RESTORE.

Creare i backup nell'istanza del server in cui viene 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_name SET HADR AVAILABILITY GROUP = group_name

Eseguire in ogni istanza del server in cui viene ospitata una replica secondaria.

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

Icona freccia utilizzata con il collegamento Torna all'inizio[Torna all'inizio]

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

[!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 utilizzata l'autenticazione di Windows.

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

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

  3. Creare un join della nuova replica secondaria al gruppo di disponibilità. Per ulteriori informazioni, vedere Creare un join di 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, utilizzando RESTORE WITH NORECOVERY. Per ulteriori informazioni, vedere Creare un gruppo di disponibilità (Transact-SQL), a partire dal passaggio per il ripristino del backup del database.

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

Icona freccia utilizzata con il collegamento Torna all'inizio[Torna all'inizio]

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

In questo esempio viene creata una procedura di configurazione Gruppi di disponibilità AlwaysOn di esempio in cui viene utilizzato Transact-SQL per configurare endpoint del mirroring del database in cui viene utilizzata 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 Gruppi di disponibilità AlwaysOn. Per ulteriori informazioni, vedere Prerequisiti, restrizioni e consigli per i gruppi di disponibilità AlwaysOn (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 utilizzare 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 utilizza 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
      

[Torna all'inizio dell'esempio]

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

Istanza host di SQL Server

Primaria

COMPUTER01

AgHostInstance

Secondaria

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 utilizza 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 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. 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, in 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 utilizzare 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 esempi di codice Transact-SQL aggiuntivi sulla 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.

    Nell'esempio di codice seguente si creano 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.

    Nota importanteImportante

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

    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.

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

    Nota importanteImportante

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

    Nell'esempio di codice seguente vengono creati 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
    

[Torna all'inizio dell'esempio]

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 esempi di codice Transact-SQL aggiuntivi sulla 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' 
    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

Icona freccia utilizzata con il collegamento Torna all'inizio[Torna all'inizio dell'esempio]

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 Gruppi di disponibilità AlwaysOn

Per configurare un endpoint del mirroring del database

Per risolvere i problemi relativi alla configurazione di Gruppi di disponibilità AlwaysOn

Icona freccia utilizzata con il collegamento Torna all'inizio[Torna all'inizio]

Contenuto correlato

Icona freccia utilizzata con il collegamento Torna all'inizio[Torna all'inizio]

Vedere anche

Concetti

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)