Configurare la scalabilità in lettura per un gruppo di disponibilità Always On

Articolo
04/26/2023

Si applica a: SQL Server

È possibile configurare un gruppo di disponibilità SQL Server Always On per i carichi di lavoro di scalabilità in lettura in Windows. Esistono due tipi di architettura per i gruppi di disponibilità:

L'architettura per la disponibilità elevata che usa una gestione cluster per garantire la continuità operativa e può includere repliche secondarie leggibili. Per la creazione di un'architettura a disponibilità elevata, vedere Creazione e configurazione di gruppi di disponibilità (SQL Server).
Un'architettura che supporta solo i carichi di lavoro di scalabilità in lettura.

Questo articolo illustra come creare un gruppo di disponibilità senza una gestione cluster per i carichi di lavoro di scalabilità in lettura. Questa architettura fornisce solo la scalabilità in lettura. Non fornisce la disponibilità elevata.

Nota

Un gruppo di disponibilità con CLUSTER_TYPE = NONE può includere repliche che sono ospitate in piattaforme di sistema operativo diverse. Non può tuttavia supportare la disponibilità elevata. Per il sistema operativo Linux, vedere Configurare un gruppo di disponibilità SQL Server con scalabilità in lettura per Linux.

Prerequisiti

Prima di creare il gruppo di disponibilità, è necessario:

Impostare l'ambiente in modo che tutti i server che ospitano le repliche di disponibilità possano comunicare.
Installa SQL Server. Per informazioni dettagliate, vedere Installare SQL Server.

Abilitare la funzionalità Gruppi di disponibilità Always On e riavviare mssql-server

Nota

Il comando seguente usa cmdlet del modulo sqlserver pubblicato in PowerShell Gallery. È possibile installare questo modulo usando il comando Install-Module.

Abilitare Gruppi di disponibilità Always On per ogni replica che ospita un'istanza di SQL Server. Quindi riavviare il servizio SQL Server. Eseguire il comando seguente per abilitare e quindi riavviare i servizi SQL Server:

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

Abilitare una sessione eventi AlwaysOn_health

Per diagnosticare più facilmente la causa radice durante la risoluzione dei problemi che interessano un gruppo di disponibilità, è possibile abilitare facoltativamente una sessione di eventi estesi dei gruppi di disponibilità Always On (XEvents). A tale scopo, eseguire il comando seguente in ogni istanza di SQL Server:

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

Per altre informazioni su questa sessione XEvents, vedere Eventi estesi dei gruppi di disponibilità Always On.

Autenticazione endpoint del mirroring del database

Per il funzionamento corretto della sincronizzazione, le repliche incluse nel gruppo di disponibilità per scalabilità in lettura devono eseguire l'autenticazione attraverso l'endpoint. I due scenari principali che è possibile usare per l'autenticazione di questo tipo sono illustrati nelle sezioni successive.

Account di servizio

In un ambiente Active Directory in cui tutte le repliche secondarie sono aggiunte allo stesso dominio, SQL Server può eseguire l'autenticazione usando l'account del servizio. È necessario creare in modo esplicito un account di accesso per l'account di servizio in ogni istanza di SQL Server:

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

Negli ambienti in cui le repliche secondarie non possono essere aggiunte a un dominio di Active Directory, è necessario usare l'autenticazione SQL. Lo script Transact-SQL seguente crea un account di accesso con nome dbm_login e un utente con nome dbm_user. Aggiornare lo script con una password complessa. Per creare l'utente dell'endpoint di mirroring del database, eseguire il comando seguente in tutte le istanze di SQL Server:

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_Password!@#>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

Autenticazione del certificato

Se si usa una replica secondaria che richiede l'autenticazione con l'autenticazione SQL, usare un certificato per l'autenticazione tra gli endpoint di mirroring.

Lo script Transact-SQL seguente crea una chiave master e un certificato. Quindi esegue il backup del certificato e protegge il file con una chiave privata. Aggiornare lo script con password complesse. Eseguire lo script nell'istanza di SQL Server primaria per creare il certificato:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

A questo punto la replica di SQL Server primaria dispone di un certificato in c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer e di una chiave privata in c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk. Copiare questi due file nello stesso percorso in tutti i server che ospiteranno le repliche di disponibilità.

In ogni replica secondaria verificare che l'account del servizio per l'istanza di SQL Server abbia le autorizzazioni per l'accesso al certificato.

Creare il certificato nei server secondari

Lo script Transact-SQL seguente crea una chiave master e un certificato dal backup creato nella replica primaria di SQL Server. Inoltre, il comando autorizza gli utenti ad accedere al certificato. Aggiornare lo script con password complesse. La password di decrittografia è la stessa password usata per creare il file .pvk in un passaggio precedente. Per creare il certificato eseguire lo script seguente in tutte le repliche secondarie:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

Creare endpoint di mirroring del database in tutte le repliche

Gli endpoint del mirroring del database usano il protocollo TCP (Transmission Control Protocol) per inviare e ricevere messaggi tra istanze del server che partecipano a sessioni di mirroring del database o ospitano repliche di disponibilità. L'endpoint del mirroring del database è in attesa su un numero di porta TCP univoco.

Lo script Transact-SQL seguente crea un endpoint di ascolto denominato Hadr_endpoint per il gruppo di disponibilità. Avvia l'endpoint e concede l'autorizzazione di connessione all'account del servizio o all'account di accesso SQL creato in un passaggio precedente. Prima di eseguire lo script, sostituire i valori compresi tra **< ... >**. Facoltativamente è possibile includere un indirizzo IP LISTENER_IP = (0.0.0.0). L'indirizzo IP del listener deve essere un indirizzo IPv4. È anche possibile usare 0.0.0.0.

Aggiornare lo script Transact-SQL seguente per il proprio ambiente in tutte le istanze di SQL Server:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

La porta TCP sul firewall deve essere aperta per la porta del listener.

Per altre informazioni, vedere Endpoint del mirroring del database (SQL Server).

Creare un gruppo di disponibilità

Creare un gruppo di disponibilità. Impostare CLUSTER_TYPE = NONE. Impostare anche ogni replica con FAILOVER_MODE = NONE. Le applicazioni client che eseguono carichi di lavoro di analisi e report possono connettersi direttamente ai database secondari. È anche possibile creare un elenco di routing di sola lettura. Le connessioni alla replica primaria inoltrano le richieste di connessione in lettura a ogni replica secondaria dell'elenco di routing in base a uno schema round-robin.

Lo script Transact-SQL seguente crea un gruppo di disponibilità denominato ag1. Lo script configura le repliche del gruppo di disponibilità con SEEDING_MODE = AUTOMATIC. In base a questa impostazione, SQL Server crea automaticamente il database in ciascun server secondario dopo l'aggiunta al gruppo di disponibilità.

Aggiornare lo script seguente per il proprio ambiente. Sostituire i valori <node1> e <node2> con i nomi delle istanze di SQL Server che ospitano le repliche. Sostituire il valore <5022> con la porta impostata per l'endpoint. Nella replica primaria di SQL Server eseguire lo script Transact-SQL seguente:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Creare un join fra le istanze di SQL Server secondarie e il gruppo di disponibilità

Lo script Transact-SQL seguente aggiunge un server al gruppo di disponibilità con nome ag1. Aggiornare lo script per il proprio ambiente. Per creare un join per il gruppo di disponibilità, eseguire lo script Transact-SQL seguente su ciascuna replica secondaria di SQL Server:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Aggiungere un database al gruppo di disponibilità

Verificare che il database che si aggiunge al gruppo di disponibilità si trovi nel modello di recupero con registrazione completa e disponga di un backup del log valido. Se si tratta di un database di prova o di un database appena creato, eseguire un backup del database. Per creare un database denominato db1 ed eseguirne il backup, eseguire lo script Transact-SQL seguente sull'istanza primaria di SQL Server:

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

Per aggiungere un database denominato db1 a un gruppo di disponibilità denominato ag1, eseguire lo script Transact-SQL seguente nella replica di SQL Server primaria:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Verificare che il database sia creato nei server secondari

Per verificare che il database db1 sia stato creato e sia sincronizzato, eseguire la query seguente in ogni replica secondaria di SQL Server:

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

Questo gruppo di disponibilità non è una configurazione a disponibilità elevata. Se è necessaria la disponibilità elevata, seguire le istruzioni in Configure an Always On Availability Group for SQL Server on Linux (Configurare un gruppo di disponibilità Always On per SQL Server in Linux) o in Creazione e configurazione di gruppi di disponibilità in Windows.

Eseguire la connessione a repliche secondarie di sola lettura

Ci sono due modi per eseguire la connessione a repliche secondarie di sola lettura:

Le applicazioni possono connettersi direttamente all'istanza di SQL Server che ospita la replica secondaria ed eseguire query sui database. Per altre informazioni, vedere Repliche secondarie leggibili.
Le applicazioni possono anche usare il routing di sola lettura, per il quale è necessario un listener. Se si distribuisce uno scenario con scalabilità in lettura senza gestione cluster, è comunque possibile creare un listener che punti all'indirizzo IP della replica primaria corrente e alla stessa porta da quella in cui SQL Server resta in ascolto. Sarà necessario ricreare il listener in modo che punti al nuovo indirizzo IP primario dopo un failover. Per altre informazioni, vedere Routing di sola lettura.

Eseguire il failover della replica primaria in un gruppo di disponibilità per scalabilità in lettura

Ogni gruppo di disponibilità include solo una replica primaria, che consente operazioni di lettura e scrittura. Per modificare la replica primaria, è possibile effettuare il failover. In un gruppo di disponibilità tipico il processo di failover è automatizzato da Gestione cluster. In un gruppo di disponibilità con tipo di cluster NONE, il processo di failover è manuale.

Esistono due modi per effettuare il failover della replica primaria in un gruppo di disponibilità con tipo di cluster NONE:

Failover manuale senza perdita di dati
Failover manuale forzato con perdita di dati

Failover manuale senza perdita di dati

Usare questo metodo quando la replica primaria è disponibile, ma è necessario modificare temporaneamente o definitivamente l'istanza che ospita la replica primaria. Per evitare una potenziale perdita di dati, prima di effettuare il failover manuale, verificare che la replica secondaria di destinazione sia aggiornata.

Per effettuare il failover manuale senza perdita di dati:

Impostare la replica primaria corrente e la replica secondaria di destinazione come SYNCHRONOUS_COMMIT.

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

Per verificare che per le transazioni attive venga eseguito il commit della replica primaria e di almeno una replica secondaria sincrona, eseguire la query seguente:
```
SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id; 
```
La replica secondaria è sincronizzata quando synchronization_state_desc è SYNCHRONIZED.
Aggiornare REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT su 1.

Lo script seguente imposta REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT su 1 in un gruppo di disponibilità denominato ag1. Prima di eseguire lo script seguente, sostituire ag1 con il nome del gruppo di disponibilità:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
Questa impostazione assicura che per ogni transazione attiva venga eseguito il commit della replica primaria e di almeno una replica secondaria sincrona.

Nota

Questa impostazione non è specifica del failover e deve essere impostata in base ai requisiti dell'ambiente.
Imposta la replica primaria e le repliche secondarie che non partecipano al failover offline per prepararti alla modifica del ruolo:
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Alzare il livello della replica secondaria di destinazione a replica primaria.
```
ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
```
Aggiorna il ruolo della replica primaria precedente e di altre secondarie in SECONDARY, quindi esegui il comando seguente nell'istanza di SQL Server che ospita la replica primaria precedente:
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
Nota

Per eliminare un gruppo di disponibilità, usare DROP AVAILABILITY GROUP. Per un gruppo di disponibilità creato con il tipo di cluster NONE o EXTERNAL, eseguire il comando su tutte le repliche che fanno parte del gruppo di disponibilità.
Riprendere lo spostamento dati, eseguire il comando seguente per ogni database nel gruppo di disponibilità nell'istanza di SQL Server che ospita la replica primaria:
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
Ricreare ogni listener creato a scopo di scalabilità in lettura e che non rientra nella gestione cluster. Se il listener originale punta alla replica primaria precedente, rimuoverlo e ricrearlo in modo che punti a quella nuova.

Failover manuale forzato con perdita di dati

Se la replica primaria non è disponibile e non può essere ripristinata immediatamente, è necessario forzare un failover nella replica secondaria con perdita di dati. Tuttavia, se la replica primaria originale viene ripristinata dopo il failover, assumerà il ruolo primario. Per evitare che ogni replica si trovi in uno stato diverso, rimuovere la replica primaria originale dal gruppo di disponibilità dopo un failover forzato con perdita di dati. Quando la replica primaria originale torna online, rimuovere completamente il gruppo di disponibilità.

Per forzare un failover manuale con perdita di dati dalla replica primaria N1 alla replica secondaria N2, seguire questa procedura:

Nella replica secondaria (N2) avviare il failover forzato:

ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Nella nuova replica primaria (N2) rimuovere la replica primaria originale (N1):
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
Verificare che tutto il traffico dell'applicazione punti al listener e/o alla nuova replica primaria.
Se la replica primaria originale (N1) torna online, portare immediatamente offline AGRScale del gruppo di disponibilità nella replica primaria originale (N1):
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
Se sono presenti dati o modifiche non sincronizzate, conservare questi dati tramite backup o altre opzioni di replica dei dati adatte alle esigenze aziendali.
Rimuovere quindi il gruppo di disponibilità dalla replica primaria originale (N1):
```
DROP AVAILABILITY GROUP [AGRScale];
```
Eliminare il database del gruppo di disponibilità nella replica primaria originale (N1):
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(Facoltativo) Se lo si desidera, è ora possibile aggiungere di nuovo N1 come nuova replica secondaria ad AGRScale del gruppo di disponibilità.

Si noti che se si usa un listener per connettersi, è necessario ricreare il listener dopo aver eseguito il failover.

Condividi tramite

Configurare la scalabilità in lettura per un gruppo di disponibilità Always On

Prerequisiti

Abilitare la funzionalità Gruppi di disponibilità Always On e riavviare mssql-server

Abilitare una sessione eventi AlwaysOn_health

Autenticazione endpoint del mirroring del database

Account di servizio

Autenticazione del certificato

Creare il certificato nei server secondari

Creare endpoint di mirroring del database in tutte le repliche

Creare un gruppo di disponibilità

Creare un join fra le istanze di SQL Server secondarie e il gruppo di disponibilità

Aggiungere un database al gruppo di disponibilità

Verificare che il database sia creato nei server secondari

Eseguire la connessione a repliche secondarie di sola lettura

Eseguire il failover della replica primaria in un gruppo di disponibilità per scalabilità in lettura

Failover manuale senza perdita di dati

Failover manuale forzato con perdita di dati

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

Configurare la scalabilità in lettura per un gruppo di disponibilità Always On

Prerequisiti

Abilitare la funzionalità Gruppi di disponibilità Always On e riavviare mssql-server

Abilitare una sessione eventi AlwaysOn_health

Autenticazione endpoint del mirroring del database

Account di servizio

Autenticazione dell'account di accesso SQL

Autenticazione del certificato

Creare il certificato nei server secondari

Creare endpoint di mirroring del database in tutte le repliche

Creare un gruppo di disponibilità

Creare un join fra le istanze di SQL Server secondarie e il gruppo di disponibilità

Aggiungere un database al gruppo di disponibilità

Verificare che il database sia creato nei server secondari

Eseguire la connessione a repliche secondarie di sola lettura

Eseguire il failover della replica primaria in un gruppo di disponibilità per scalabilità in lettura

Failover manuale senza perdita di dati

Failover manuale forzato con perdita di dati

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive