Configurare un gruppo di disponibilità di SQL Server con scalabilità in lettura in Linux

Si applica a: SQL Server - Linux

Questo articolo illustra come creare un gruppo di disponibilità Always On di SQL Server in Linux senza un modulo di gestione cluster. Questa architettura fornisce solo la scalabilità in lettura. Non fornisce la disponibilità elevata.

Esistono due tipi di architetture per i gruppi di disponibilità. Un'architettura per la disponibilità elevata usa un modulo di gestione cluster per garantire la continuità operativa in modo più efficace. Per creare un'architettura per la disponibilità elevata, vedere Configurare un gruppo di disponibilità Always On di SQL Server per la disponibilità elevata in Linux.

Un gruppo di disponibilità con CLUSTER_TYPE = NONE può includere repliche ospitate in diverse piattaforme del sistema operativo. Non può tuttavia supportare la disponibilità elevata.

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.

In Linux è necessario creare un gruppo di disponibilità prima di aggiungerlo come risorsa cluster, da gestire con il cluster. Questo documento propone un esempio di creazione del gruppo di disponibilità.

1. Aggiornare il nome del computer per ogni host.

   Ogni nome di istanza di SQL Server deve essere:

   • 15 caratteri o meno.
   • Essere univoco all'interno della rete.

   Per impostare il nome del computer, modificare /etc/hostname. Lo script seguente consente di modificare /etc/hostname con vi:

   sudo vi /etc/hostname
   

2. Configurare il file hosts.

   Nota

   Se i nomi host sono registrati con i relativi indirizzi IP nel server DNS, non è necessario eseguire i passaggi seguenti. Verificare che tutti i nodi che faranno parte della configurazione del gruppo di disponibilità possano comunicare tra loro. Se si effettua il ping del nome host si dovrebbe ottenere come risposta l'indirizzo IP corrispondente. Assicurarsi anche che il file /etc/hosts non contenga un record che mappa l'indirizzo IP di localhost 127.0.0.1 al nome host del nodo.

   Il file hosts in ogni server contiene gli indirizzi IP e i nomi di tutti i server che faranno parte del gruppo di disponibilità.

   Il comando seguente restituisce l'indirizzo IP del server corrente:

   sudo ip addr show
   

   Aggiornare /etc/hosts. Lo script seguente consente di modificare /etc/hosts con vi:

   sudo vi /etc/hosts
   

   L'esempio seguente mostra /etc/hosts in node1 con l'aggiunta di node1, node2 e node3. In questo esempio node1 indica il server che ospita la replica primaria, mentre node2 e node3 indicano i server che ospitano le repliche secondarie.

   127.0.0.1    localhost localhost4 localhost4.localdomain4
   ::1          localhost localhost6 localhost6.localdomain6
   10.128.18.12 node1
   10.128.16.77 node2
   10.128.15.33 node3
   

Installare SQL Server

Installa SQL Server. I collegamenti seguenti rimandano alle istruzioni di installazione di SQL Server per varie distribuzioni:

• Avvio rapido: Installare SQL Server e creare un database in Red Hat
• Avvio rapido: Installare SQL Server e creare un database in SUSE Linux Enterprise Server
• Avvio rapido: Installare SQL Server e creare un database in Ubuntu

Abilitare i gruppi di disponibilità Always On

Abilitare i gruppi di disponibilità Always On in ogni nodo che ospita un'istanza di SQL Server e riavviare mssql-server. Eseguire lo script seguente:

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

Abilitare una sessione eventi AlwaysOn_health

Facoltativamente, è possibile abilitare gli eventi estesi (Extended Events, XE) per diagnosticare più facilmente la causa radice durante la risoluzione dei problemi relativi ai gruppi di disponibilità. 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 di eventi estesi, vedere Configurare gli eventi estesi per i gruppi di disponibilità.

Creare un certificato

Il servizio SQL Server in Linux usa i certificati per autenticare la comunicazione tra gli endpoint del 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. Stabilire la connessione all'istanza primaria di SQL Server. Per creare il certificato, eseguire lo script Transact-SQL seguente:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master-key-password>';

CREATE CERTIFICATE dbm_certificate
    WITH SUBJECT = 'dbm';

BACKUP CERTIFICATE dbm_certificate
    TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/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 /var/opt/mssql/data/dbm_certificate.cer e di una chiave privata in var/opt/mssql/data/dbm_certificate.pvk. Copiare questi due file nello stesso percorso in tutti i server che ospiteranno le repliche di disponibilità. Usare l'utente mssql o concedere l'autorizzazione all'utente mssql per questi file.

Nel server di origine, ad esempio, il comando seguente copia i file nel computer di destinazione. Sostituire i valori <node2> con i nomi delle istanze di SQL Server che ospiteranno le repliche.

cd /var/opt/mssql/data
scp dbm_certificate.* root@<node2>:/var/opt/mssql/data/

In ogni server di destinazione assegnare all'utente mssql l'autorizzazione per accedere al certificato.

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

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. 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 tutti i server secondari:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master-key-password>';

CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<private-key-password>'
);

Nell'esempio precedente sostituire <private-key-password> con la stessa password usata durante la creazione del certificato nella replica primaria.

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 assegna l'autorizzazione di connessione al certificato creato. 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;

Nota

Se si usa SQL Server Express Edition in un nodo per ospitare una replica di sola configurazione, l'unico valore valido per ROLE è WITNESS. Eseguire lo script seguente in SQL Server Express Edition:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP
(
            LISTENER_PORT = 5022
)
    FOR DATABASE_MIRRORING
(
            ROLE = WITNESS,
            AUTHENTICATION = CERTIFICATE dbm_certificate,
            ENCRYPTION = REQUIRED ALGORITHM AES
);

ALTER ENDPOINT [Hadr_endpoint]
    STATE = STARTED;

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

Importante

In SQL Server 2017 (14.x), l'unico metodo di autenticazione supportato per l'endpoint di mirroring del database è CERTIFICATE. L'opzione WINDOWS non è disponibile.

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

Creare il gruppo di disponibilità

Creare il gruppo di disponibilità. Impostare CLUSTER_TYPE = NONE. Impostare anche ogni replica con FAILOVER_MODE = MANUAL. Le applicazioni client che eseguono carichi di lavoro di analisi o esecuzione 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à con nome 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 ogni 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;

Aggiungere istanze secondarie di SQL Server al 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. In ogni replica secondaria di SQL Server eseguire lo script Transact-SQL seguente per l'aggiunta al gruppo di disponibilità:

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

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Aggiungere un database al gruppo di disponibilità

Verifica che il database che aggiungi al gruppo di disponibilità sia in modalità di ripristino 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. Nell'istanza primaria di SQL Server eseguire lo script Transact-SQL (T-SQL) seguente per creare ed eseguire il backup di un database denominato db1:

CREATE DATABASE [db1];
GO

ALTER DATABASE [db1]
    SET RECOVERY FULL;
GO

BACKUP DATABASE [db1]
    TO DISK = N'/var/opt/mssql/data/db1.bak';

Nella replica primaria di SQL Server eseguire lo script T-SQL seguente per aggiungere un database denominato db1 a un gruppo di disponibilità denominato ag1:

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

Verificare che il database sia creato nei server secondari

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

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

Questo gruppo di disponibilità non è una configurazione a disponibilità elevata. Se la disponibilità elevata è necessaria, seguire le istruzioni in Configurare un gruppo di disponibilità Always On per SQL Server in Linux. In particolare, creare il gruppo di disponibilità con CLUSTER_TYPE=WSFC (in Windows) o CLUSTER_TYPE=EXTERNAL (in Linux). È quindi possibile procedere all'integrazione di un modulo di gestione cluster (Windows Server Failover Clustering in Windows o Pacemaker in Linux).

Eseguire la connessione a repliche secondarie di sola lettura

Esistono 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. Oppure possono usare il routing di sola lettura, per il quale è necessario un listener.

• Ripartire il carico di lavoro di sola lettura in una replica secondaria di un gruppo di disponibilità Always On
• Routing di sola lettura

Eseguire il failover della replica primaria in un gruppo di disponibilità con 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:

1. 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);
   

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

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

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

5. Alzare il livello della replica secondaria di destinazione a replica primaria.

   ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
   

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

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

8. 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:

1. Nella replica secondaria (N2) avviare il failover forzato:

   ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

2. Nella nuova replica primaria (N2) rimuovere la replica primaria originale (N1):

   ALTER AVAILABILITY GROUP [AGRScale]
   REMOVE REPLICA ON N'N1';
   

3. Verificare che tutto il traffico dell'applicazione punti al listener e/o alla nuova replica primaria.

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

5. Se sono presenti dati o modifiche non sincronizzate, conservare questi dati tramite backup o altre opzioni di replica dei dati adatte alle esigenze aziendali.

6. Rimuovere quindi il gruppo di disponibilità dalla replica primaria originale (N1):

   DROP AVAILABILITY GROUP [AGRScale];
   

7. Eliminare il database del gruppo di disponibilità nella replica primaria originale (N1):

   USE [master]
   GO
   DROP DATABASE [AGDBRScale]
   GO
   

8. (Facoltativo) Se lo si desidera, è ora possibile aggiungere di nuovo N1 come nuova replica secondaria ad AGRScale del gruppo di disponibilità.

Contenuto correlato

• Gruppi di disponibilità distribuiti
• Che cos'è un gruppo di disponibilità Always On?
• Eseguire un failover manuale forzato di un gruppo di disponibilità Always On (SQL Server)