Continuità aziendale e recupero del database - SQL Server

Articolo
03/18/2024

Si applica a: SQL Server 2016 (13.x) e versioni successive

Questo articolo offre una panoramica delle soluzioni di continuità aziendale per la disponibilità elevata e il ripristino di emergenza in SQL Server.

Un'attività comune di cui deve tenere conto chiunque esegua una distribuzione di SQL Server è verificare se tutte le istanze cruciali di SQL Server e i database all'interno delle stesse sono disponibili quando gli utenti finali e aziendali ne hanno bisogno, ovvero in orario di ufficio o 24 ore su 24. L'obiettivo è fare in modo che le interruzioni delle attività aziendali siano minime o inesistenti. Questo concetto è noto anche come continuità aziendale.

SQL Server 2017 (14.x) introduce molte nuove funzionalità o miglioramenti di quelle già esistenti, alcune delle quali riguardano la disponibilità. L'elemento più importante aggiunto a SQL Server 2017 (14.x) è il supporto per le distribuzioni di SQL Server in Linux. Per un elenco completo delle nuove funzionalità in SQL Server, vedere gli argomenti seguenti:

Questo articolo descrive in particolare gli scenari di disponibilità in SQL Server 2017 (14.x) e versioni successive, nonché le funzionalità di disponibilità, sia nuove che ottimizzate. Gli scenari includono soluzioni ibride in grado di coprire le distribuzioni di SQL Server sia in Windows Server che in Linux, nonché le soluzioni che consentono di aumentare il numero di copie leggibili di un database.

Questo articolo non tratta le opzioni di disponibilità esterne a SQL Server, ad esempio quelle offerte dalla virtualizzazione, ma tutti gli argomenti trattati riguardano le installazioni di SQL Server in una macchina virtuale guest sia nel cloud pubblico, sia in hosting in un server hypervisor locale.

Scenari di SQL Server che usano le funzionalità di disponibilità

Gruppi di disponibilità Always On, istanza del cluster di failover Always On e log shipping possono essere usati in molti modi e non necessariamente solo ai fini della disponibilità. Le funzionalità di disponibilità possono essere usate sostanzialmente in quattro modi:

Disponibilità elevata
Ripristino di emergenza
Migrazioni e aggiornamenti
Scalabilità orizzontale delle copie leggibili di uno o più database

Le sezioni seguenti descrivono le funzionalità rilevanti che possono essere usate in uno scenario specifico. L'unica funzionalità non coperta è la replica di SQL Server. Benché non sia ufficialmente designata come funzionalità di disponibilità dell'ambiente Always On, la replica di SQL Server viene spesso usata per rendere ridondanti i dati in determinati scenari. La replica di tipo merge non è supportata per SQL Server in Linux. Per altre informazioni, vedere Replica di SQL Server in Linux.

Importante

Le funzionalità di disponibilità di SQL Server non sostituiscono la necessità di adottare una strategia efficace e adeguatamente testata per il backup e il ripristino, aspetto imprescindibile di qualsiasi soluzione di disponibilità.

Disponibilità elevata

È importante verificare che il database o le istanze di SQL Server siano disponibili nel caso in cui si riscontri un problema a livello locale in un data center o una singola area del cloud. Questa sezione spiega in che modo le funzionalità di disponibilità di SQL Server possono essere utili per svolgere tale attività. Tutte le funzionalità descritte sono disponibili sia in Windows Server che in Linux.

Gruppi di disponibilità

Introdotti in SQL Server 2012 (11.x), i gruppi di disponibilità offrono una protezione a livello di database in cui ogni transazione di un database viene inviata a un'altra istanza, nota come replica, che contiene una copia di quel database in uno stato speciale. Un gruppo di disponibilità può essere distribuito nella edizione Standard o in quella Enterprise. Le istanze che fanno parte di un gruppo di disponibilità possono essere autonome o istanze del cluster di failover (FCI), descritte nella sezione successiva. Poiché le transazioni vengono inviate a una replica in tempo reale, i gruppi di disponibilità sono consigliati in situazioni che richiedono valori inferiori per il punto di recupero e gli obiettivi del tempo di recupero. Lo spostamento dei dati tra le repliche può essere sincrono o asincrono. L’edizione Enterprise consente fino a tre repliche, inclusa quella primaria, sincrone. Un gruppo di disponibilità ha una copia del database con funzioni di lettura/scrittura complete nella replica primaria, mentre tutte le repliche secondarie non ricevono le transazioni direttamente da utenti finali o applicazioni.

Nota

Always On è un termine generico per le funzionalità di disponibilità in SQL Server e si riferisce sia ai gruppi di disponibilità che alle istanze del cluster di failover. Always On non è il nome della funzionalità del gruppo di disponibilità.

Prima di SQL Server 2022 (16.x), i gruppi di disponibilità fornivano solo la protezione a livello di database e non a livello di istanza. Per ogni replica secondaria, tutto ciò che non viene acquisito nel log delle transazioni o configurato nel database dovrà essere sincronizzato manualmente. Alcuni esempi di oggetti che devono essere sincronizzati manualmente sono gli accessi a livello di istanza, i server collegati e SQL Server Agent - processi.

A partire da SQL Server 2022 (16.x), è possibile gestire gli oggetti metadati, inclusi utenti, account di accesso, autorizzazioni e processi di SQL Server Agent a livello di gruppo di disponibilità oltre al livello di istanza. Per altre informazioni, vedere Gruppi di disponibilità indipendenti.

Un gruppo di disponibilità ha anche un altro componente denominato listener, che consente alle applicazioni e agli utenti finali di connettersi senza dover sapere quale istanza di SQL Server ospita la replica primaria. Ogni gruppo di disponibilità avrebbe un proprio listener. Le implementazioni del listener sono leggermente diverse in Windows Server rispetto a Linux, ma la funzionalità e la modalità di utilizzo sono le stesse. Il diagramma seguente illustra un gruppo di disponibilità basato su Windows Server che usa un cluster di failover di Windows Server (WSFC). Sia in Linux che in Windows Server è necessario un cluster sottostante a livello di sistema operativo per la disponibilità. L'esempio illustra una configurazione semplice a due server, o nodi, in cui il cluster sottostante è un cluster di failover di Windows Server.

Diagramma di un gruppo di disponibilità semplice.

Edizione Standard ed edizione Enterprise hanno valori massimi diversi per quanto riguarda le repliche. Un gruppo di disponibilità nella edizione Standard, noto come gruppo di disponibilità di base, supporta due repliche, una primaria e una secondaria, con un singolo database nel gruppo di disponibilità. L'edizione Enterprise non solo consente la configurazione di più database in un unico gruppo di disponibilità, ma può anche avere fino a nove repliche in totale, una primaria e otto secondarie. L'edizione Enterprise offre anche altri vantaggi facoltativi, ad esempio le repliche secondarie leggibili, la possibilità di eseguire copie di backup da una replica secondaria e altro ancora.

Nota

Il mirroring del database, deprecato in SQL Server 2012 (11.x), non è disponibile nella versione di SQL Server per Linux e non verrà aggiunto. I clienti che usano ancora il mirroring del database devono iniziare a pianificare la migrazione ai gruppi di disponibilità, che sostituiscono il mirroring del database.

Rispetto alla disponibilità, i gruppi di disponibilità possono offrire il failover automatico o manuale. Il failover automatico può verificarsi se è configurato lo spostamento sincrono dei dati e i database nella replica primaria e secondaria sono in uno stato sincronizzato. Purché venga usato il listener e l'applicazione usi una versione successiva di .NET Framework (3.5 con un aggiornamento o 4.0 e versioni successive), il failover viene gestito in modo da non incidere, o avere un impatto minimo, sugli utenti finali se si usa un listener. Il failover per trasformare una replica secondaria nella nuova replica primaria può essere configurato in modo da essere automatico o manuale e in genere viene misurato in secondi.

Nell'elenco che segue sono evidenziate alcune differenze tra Windows Server e Linux rispetto ai gruppi di disponibilità:

A causa delle differenze di funzionamento del cluster sottostante tra Linux e Windows Server, tutti i failover, manuali o automatici, dei gruppi di disponibilità vengono eseguiti usando il cluster di Linux. Nelle distribuzioni dei gruppi di disponibilità basati su Windows Server i failover manuali devono essere eseguiti usando SQL Server. I failover automatici vengono gestiti dal cluster sottostante sia in Windows Server che in Linux.
Per SQL Server in Linux, la configurazione consigliata per i gruppi di disponibilità è di almeno tre repliche. Ciò è dovuto il modo in cui funziona il cluster sottostante.
In Linux il nome comune usato da ogni listener è definito in DNS e non nel cluster come in Windows Server.

A partire da SQL Server 2017 (14.x) sono disponibili alcune nuove funzionalità e miglioramenti per i gruppi di disponibilità:

Tipi di cluster
REQUIRED_SECONDARIES_TO_COMMIT
Supporto ottimizzato di Microsoft Distributed Transaction Coordinator (DTC) per le configurazioni basate su Windows Server
Scenari di scalabilità orizzontale aggiuntivi per i database di sola lettura (descritti più avanti in questo articolo)

Tipi di cluster del gruppo di disponibilità

Il modulo di disponibilità predefinito del clustering in Windows Server è abilitato da una funzionalità nota come clustering di failover. Consente di creare un cluster WSFC da usare con un gruppo di disponibilità o un'istanza del cluster di failover. L'integrazione per i gruppi di disponibilità e le istanze del cluster di failover è gestita da DLL delle risorse compatibili con i cluster presenti in SQL Server.

SQL Server in Linux supporta più tecnologie di clustering. Microsoft supporta i componenti di SQL Server, mentre i nostri partner supportano la tecnologia di clustering pertinente. Ad esempio, insieme a Pacemaker, SQL Server in Linux supporta anche HPE Serviceguard e DH2i DxEnterprise come soluzione cluster.

Un cluster di failover basato su Windows e una soluzione cluster Linux sono più simili che diversi. Entrambi consentono di prendere singoli server e combinarli in una configurazione per garantire la disponibilità e si basano su concetti come risorse, vincoli (anche se implementati in modo diverso), failover e così via.

Per supportare Pacemaker sia per i gruppi di disponibilità che per le configurazioni di istanze del cluster di failover, incluso ad esempio il failover automatico, Microsoft offre il pacchetto mssql-server-ha, che è simile, ma non esattamente identico, alle DLL della risorsa in un WSFC, per Pacemaker. Una delle differenze tra un cluster di failover di Windows Server e Pacemaker è che non esistono risorse nome di rete in Pacemaker, ovvero il componente che consente di astrarre il nome del listener (o il nome dell'istanza del cluster di failover) per un cluster di failover di Windows Server. DNS offre la risoluzione dei nomi in Linux.

A causa della differenza nello stack di cluster, è necessario apportare alcune modifiche per i gruppi di disponibilità, poiché SQL Server deve gestire alcuni dei metadati gestiti in modo nativo da un cluster WSFC. La modifica più rilevante è l'introduzione di un tipo di cluster per un gruppo di disponibilità. Questa operazione viene archiviata in sys.availability_groups nelle colonne cluster_type e cluster_type_desc. Esistono tre tipi di cluster:

WSFC
Esterno
None

Tutti i gruppi di disponibilità che richiedono disponibilità elevata devono usare un cluster sottostante, che nel caso di SQL Server 2017 (14.x) e versioni successive significa WSFC o un agente di clustering Linux. Per i gruppi di disponibilità basati su Windows Server che usano un cluster WSFC sottostante, il tipo di cluster predefinito è WSFC e non è necessario impostarlo. Per i gruppi di disponibilità basati su Linux, quando si crea il gruppo di disponibilità, il tipo di cluster deve essere impostato su Esterno. L'integrazione con una soluzione cluster esterna in Linux viene configurata dopo la creazione del gruppo di disponibilità, mentre per un cluster di failover di Windows Server (WSFC), si esegue al momento della creazione.

Un tipo di cluster None può essere usato con gruppi di disponibilità sia di Windows Server che di Linux. Impostare il tipo di cluster su None indica che il gruppo di disponibilità non richiede un cluster sottostante. Ciò significa che SQL Server 2017 (14.x) è la prima versione di SQL Server che supporta i gruppi di disponibilità senza un cluster, ma questo vantaggio è controbilanciato dal fatto che questa configurazione non è supportata come soluzione a disponibilità elevata.

Importante

A partire da SQL Server 2017 (14.x), non è possibile modificare un tipo di cluster per un gruppo di disponibilità una volta creato. Non è quindi possibile trasformare un gruppo di disponibilità di tipo None in Esterno o WSFC o viceversa.

Per gli utenti che vogliono semplicemente aggiungere altre copie di sola lettura di un database o apprezzano le funzionalità offerte dai gruppi di disponibilità per la migrazione o gli aggiornamenti ma non vogliono essere vincolati all'ulteriore complessità di un cluster sottostante o alla replica, un gruppo di disponibilità con un tipo di cluster None è una soluzione ideale. Per altre informazioni, vedere le sezioni Migrazioni e aggiornamenti e Scalabilità in lettura.

Lo screenshot riportato di seguito illustra il supporto per i diversi tipi di cluster in SQL Server Management Studio (SSMS). È necessario che sia in esecuzione la versione 17.1 o successiva. La schermata seguente è tratta dalla versione 17.2.

REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT

SQL Server 2016 (13.x) ha incrementato il supporto per il numero di repliche sincrone da due a tre nell'edizione Enterprise. Tuttavia, se una replica secondaria viene sincronizzata ma nell'altra si verifica un problema, non esiste un modo per controllare il comportamento in modo da indicare alla replica primaria di attendere la replica che si comporta in modo errato o consentirle di continuare. Ciò significa che la replica primaria a un certo punto continuerebbe a ricevere traffico in scrittura anche se la replica secondaria non è in uno stato sincronizzato, ovvero si ha una perdita di dati nella replica secondaria. A partire da SQL Server 2017 (14.x), è possibile controllare il comportamento di ciò che accade quando sono presenti repliche sincrone con REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT. L'opzione funziona come indicato di seguito:

Sono possibili tre valori: 0, 1 e 2
Il valore è il numero di repliche secondarie che devono essere sincronizzate, con implicazioni per la perdita di dati, la disponibilità di gruppo di disponibilità e il failover
Per i cluster WSFC e il tipo di cluster None il valore predefinito è 0 e può essere impostato manualmente su 1 o 2
Per il tipo di cluster Esterno, per impostazione predefinita, il meccanismo di cluster imposta il valore, che può essere sostituito manualmente. Per tre repliche sincrone, il valore predefinito sarà 1.

In Linux il valore per REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT viene configurato automaticamente dal cluster nella risorsa del gruppo di disponibilità. In Windows viene impostato con Transact-SQL.

Un valore superiore a 0 garantisce una maggiore protezione dei dati, poiché se il numero di repliche secondarie richiesto non è disponibile, la replica primaria non sarà disponibile finché non viene risolta. REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT influisce anche sul comportamento del failover, poiché il failover automatico non può verificarsi se il numero corretto di repliche secondarie non è nello stato appropriato. In Linux il valore 0 non consente il failover automatico, quindi in Linux, quando si usano repliche sincrone con failover automatico, il valore impostato deve essere superiore a 0 per ottenere il failover automatico. 0 in Windows Server è il comportamento di SQL Server 2016 (13.x) e versioni precedenti.

Supporto avanzato per Microsoft Distributed Transaction Coordinator

Prima di SQL Server 2016 (13.x), distribuire istanze del cluster di failover era l'unico modo per ottenere la disponibilità in SQL Server per le applicazioni che richiedono transazioni distribuite che usano automaticamente il controllo DTC. Una transazione distribuita può essere eseguita in due modi:

Una transazione che si estende su più database nella stessa istanza di SQL Server
Una transazione che si estende su più di un'istanza di SQL Server o può implicare un'origine dati non di SQL Server

SQL Server 2016 (13.x) ha introdotto un supporto parziale per DTC con i gruppi di disponibilità tipici del secondo scenario. SQL Server 2017 (14.x) rappresenta un ulteriore passo avanti poiché supporta entrambi gli scenari con il controllo DTC.

In SQL Server 2017 (14.x) e versioni successive, il supporto per il controllo DTC può essere aggiunto a un gruppo di disponibilità anche dopo la creazione del gruppo. In SQL Server 2016 (13.x) è possibile abilitare il supporto per DTC in un gruppo di disponibilità solo quando il gruppo di disponibilità è stato creato.

Istanze del cluster di failover

Le installazioni in cluster sono una funzionalità di SQL Server a partire dalla versione 6.5. Le istanze del cluster di failover sono un metodo efficace per garantire la disponibilità per l'intera installazione di SQL Server, nota come istanza. Ciò significa che tutti gli elementi all'interno dell'istanza, tra cui database, SQL Server Agent, processi, server collegati e così via, verranno spostati in un altro server nel caso in cui si verifichi un problema con il server sottostante. Tutte le istanze del cluster di failover richiedono un'archiviazione condivisa, anche se disponibile attraverso la rete. Le risorse dell'istanza FCI possono essere eseguite da, ed essere di proprietà di, un solo nodo alla volta. Nell'immagine seguente il primo nodo del cluster è proprietario dell'istanza del cluster di failover, quindi è anche proprietario delle risorse di archiviazione condivise associate, come indica la linea continua tra il nodo e la risorsa di archiviazione.

Diagramma di un'istanza del cluster di failover.

Dopo un failover la proprietà cambia come illustra l'immagine seguente.

Diagramma di un'istanza del cluster di failover dopo il failover.

La perdita di dati è zero con un'istanza del cluster di failover, ma l'archiviazione condivisa sottostante è un singolo punto di guasto poiché è presente una sola copia dei dati. Le istanze del cluster di failover vengono spesso combinate con un altro metodo di disponibilità, ad esempio un gruppo di disponibilità o il log shipping, per avere copie ridondanti dei database. Il metodo aggiuntivo distribuito deve usare una risorsa di archiviazione fisicamente separata dall'istanza FCI. Quando l'istanza del cluster di failover effettua il failover a un altro nodo, si interrompe in un nodo e inizia in un altro, come se si spegnesse e riaccendesse un server. Un'istanza FCI passa attraverso il normale processo di recupero, ovvero viene eseguito il rollforward di tutte le transazioni che lo richiedono e per tutte le transazioni incomplete viene eseguito il rollback. Di conseguenza, il database è coerente da un punto dati al momento dell'errore o del failover manuale, quindi non vi è perdita di dati. I database sono disponibili solo al termine del recupero, quindi il tempo di recupero dipende da molti fattori ed è in genere più lungo del failover effettuato in un gruppo di disponibilità. Lo svantaggio è che quando si effettua il failover in un gruppo di disponibilità, per poter usare un database può essere necessario dover eseguire ulteriori operazioni, ad esempio l'abilitazione di un processo di SQL Server Agent.

Come il gruppo di disponibilità, le istanze del cluster di failover estrapolano il nodo del cluster sottostante in cui sono ospitate. Un'istanza FCI mantiene sempre lo stesso nome. Le applicazioni e gli utenti finali non si connettono mai ai nodi e viene usato il nome univoco assegnato all'istanza FCI. Un'istanza del cluster di failover può far parte di un gruppo di disponibilità come una delle istanze in cui viene ospitata una replica primaria o secondaria.

Nell'elenco che segue sono evidenziate alcune differenze tra Windows Server e Linux rispetto alle istanze FCI:

In Windows Server un'istanza FCI è parte del processo di installazione. Un'istanza FCI in Linux viene configurata dopo l'installazione di SQL Server.
Linux supporta solo una singola installazione di SQL Server per ogni host, in modo che tutte le istanze FCI siano un'istanza predefinita. Windows Server supporta fino a 25 istanze FCI per ogni WSFC.
Il nome comune usato dalle istanze FCI in Linux è definito in DNS e deve essere lo stesso della risorsa creata per l'istanza FCI.

Log shipping

Se gli obiettivi del punto di ripristino e del tempo di ripristino sono più flessibili o se i database non sono considerati cruciali, il log shipping è un'altra funzionalità di disponibilità collaudata in SQL Server. In base ai backup nativi di SQL Server, il processo per il log shipping genera automaticamente i backup del log delle transazioni, li copia in una o più istanze, note come warm standby, e applica automaticamente i backup del log delle transazioni a tale standby. Il log shipping usa SQL Server Agent - processi per automatizzare il processo di backup, copia e applicazione dei backup del log delle transazioni.

Diagramma del log shipping.

Senza dubbio il principale vantaggio offerto dal log shipping in alcune capacità è che prende in considerazione l'errore umano. L'applicazione dei log delle transazioni può subire un ritardo. Quindi, se ad esempio qualcuno rilascia un oggetto UPDATE senza una clausola WHERE, lo standby può non contenere la modifica ed è quindi possibile usarlo mentre si ripara il sistema primario. Benché il log shipping sia facile da configurare, il passaggio dall'istanza primaria al warm standby, noto come modifica del ruolo, è sempre manuale. Una modifica del ruolo viene avviata attraverso Transact-SQL e, come per un gruppo di disponibilità, tutti gli oggetti non acquisiti nel log delle transazioni devono essere sincronizzati manualmente. Il log shipping deve inoltre essere configurato per ogni database, mentre un singolo gruppo di disponibilità può contenere più database.

A differenza di un gruppo di disponibilità o di un'istanza del cluster di failover, il log shipping non ha astrazioni per una modifica del ruolo, che le applicazioni devono essere in grado di gestire. È possibile ricorrere a tecniche come l'alias DNS (CNAME), ma esistono pro e contro, ad esempio il tempo necessario per l'aggiornamento di DNS dopo il passaggio.

Ripristino di emergenza

Quando la località di disponibilità primaria è interessata da un evento catastrofico come un terremoto o un'inondazione, l'azienda deve essere pronta a portare i propri sistemi online in altre località. Questa sezione spiega in che modo le funzionalità di disponibilità di SQL Server possono contribuire alla continuità delle attività.