The life of a SQL Server DBA

Corri.

In un mondo in cui tutto corre ad una velocità superiore a quella del battito umano, lavorare nel settore della consulenza IT significa inseguire aggiornamenti continui, tecnologie sempre più sofisticate e soluzioni “innovative” che spesso arrivano prima ancora di essere comprese.

Fermati.

Fermandosi a pensare sul valore del tempo che hai speso durante il giorno, cosa rimane? Progettiamo e ricerchiamo soluzioni solo per il gusto dell’innovazione e arrivare primi, oppure c’è dell’altro?

A fine giornata ti restano le soluzioni che hai trovato. Le sfide vinte risolvendo problemi. Le soddisfazioni che hai dato ai clienti per soddisfare le loro necessità e semplificare la vita. Resta il rapporto di fiducia che costruisci con loro.

E in questo articolo non parleremo di una novità rivoluzionaria, ma di un sistema nato nel 1989, ormai ben rodato, che accompagna ancora oggi tanti progetti strategici. Parliamo di Microsoft SQL Server, della sua resilienza... e di quando qualcosa non va come dovrebbe.
 

Un po' di contesto

Un cliente decide di installare un nuovo sistema gestionale per ottimizzare quella che è una parte (molto!) importante della sua attività aziendale. Questo gestionale, utilizzato da decine di utenti finali, si appoggia ad un database SQL Server, sotto la nostra responsabilità. Un'ottima infrastruttura software e hardware, sulla carta perfettamente dimensionata. Tutto bene, se non fosse per la variabile più delicata dell’equazione: l’applicativo. Anche il miglior database può diventare inefficiente se l’applicativo che lo interroga è mal progettato, con query e transazioni gestite in modo opinabile. E questo è decisamente opinabile. 

Dopo mesi di monitoraggio, thread mail infiniti con il gestore dell’applicativo e numerosi ticket, possiamo finalmente dire che la situazione è stabile. Non è magia: è servita (tanta, santa) pazienza, tante ottimizzazioni puntuali e, soprattutto, un rapporto di fiducia con il cliente che, una volta compresa la natura del problema, ci ha dato la libertà di intervenire.

Entrando ora nel merito degli aspetti tecnici, possiamo suddividere il lavoro in tre fasi distinte:

1. Testing – dove abbiamo individuato quello che sembra essere un bug di SQL Server (e no, non è un caso isolato).
2. Entrata in produzione – quando i grafici dello strumento di monitoraggio hanno iniziato a “scaldarsi” e un singolo parametro ha fatto la differenza.
3. Lungo termine, caratterizzato da analisi periodiche delle interrogazioni più lunghe e interventi mirati attraverso riscritture del codice SQL (suggerito al fornitore dell’applicativo), inserimento di indici e hint.

In questo articolo ci concentriamo sulla prima fase, raccontando un’anomalia tanto silenziosa quanto insidiosa: un sospetto bug che colpisce i cluster SQL e rende un database inaccessibile senza apparente motivo.

Storia di un test di failover e di database che non si aprono

Per garantire la massima disponibilità (HA – High Availability), i database in questione sono configurati in un ambiente altamente resiliente. L’infrastruttura è basata su due server Windows, ciascuno con risorse e storage indipendenti, appartenenti a un failover cluster su cui girano due istanze distinte di SQL Server. La replica dei database è affidata alla tecnologia degli Availability Groups (AG).

Ciascun AG (e quindi i database in esso contenuti) ha un nodo che è replica primaria (attiva, aperta in lettura e scrittura) e uno o più nodi che sono repliche secondarie (passive, che si limitano ad allinearsi applicando i transaction log, e nel caso di versione Enterprise possono essere aperti, al massimo, in lettura). La replica è a livello di database, non dell’intera istanza, e supporta diverse modalità operative. Il vantaggio degli AG è la possibilità di fare failover da un nodo all’altro, garantendo la disponibilità dei database anche quando un nodo non è disponibile.

Qualche nozione sugli AG e il failover

Tra le varie configurazioni per la sincronizzazione permesse da questa tecnologia, è stata scelta quella con:

• Commit sincrono, in cui una modifica sul database primario viene confermata solo dopo essere stata replicata con successo sul secondario. Questo garantisce coerenza tra le repliche. 
• Failover automatico, che consente al sistema di passare automaticamente il controllo al nodo secondario in caso di guasto del primario (o di un semplice riavvio programmato), riducendo al minimo l’interruzione del servizio.

Il failover automatico è possibile solo in presenza di commit sincrono e repliche allineate.

Il problema e l'analisi del Bug: database inaccessibile dopo failover su SQL Server AG

Fin qui, tutto bene: una tecnologia solida, progettata per gestire carichi di lavoro importanti e che salva la vita anche nelle situazioni più gravi, per gioia di clienti e DBA. Ma durante i test interni, abbiamo intercettato un comportamento anomalo. E purtroppo, non è la prima volta che lo incontriamo.

Il test consisteva in una simulazione di failover “forzato”:

1. Spegnimento brutale del nodo primario (chiamiamolo nodo A) per verificare che il sistema passasse correttamente al secondario (nodo B) e che l’applicativo gestisse senza errori la transizione.
2. Dopo il successo del primo test, è stato fatto l’inverso: riavvio improvviso di B, per forzare il ritorno del ruolo primario su A.

Tutto sembra andare come previsto. Il cluster esegue il failover, le risorse (i database) vengono portate offline a livello di cluster e riportate online sul nodo A, che assume nuovamente il ruolo di primario. Tutti i database risultano online. Tutti, tranne uno.

Il database in questione risulta inaccessibile: non è possibile interrogarlo né in lettura, né in scrittura. Eppure:

• Lo stato riportato da SQL Server è Synchronized
• Nessun errore appare nei log (della replica primaria)
• La dashboard degli AG restituisce tutto verde

In condizioni normali, un database inaccessibile può trovarsi in stato di restoring (per esempio durante il recupero da crash), oppure bloccato da errori strutturali. Ma in questo caso, nulla di tutto ciò è evidente. Il problema sembra invisibile, nessuna avvisaglia… fino a quando non provi ad accedere al database.

La criticità è limitata a un singolo database: tutti gli altri database dell’AG funzionano regolarmente, l’istanza è sana, e il gruppo AG in sé non segnala problemi. Il nodo primario continua a mostrarsi in regola, a meno di tentare un accesso diretto al database incriminato.

Sul nodo secondario, invece, la situazione è ben diversa: la replica segnala uno stato Not Synchronized, e il database entra in stato Not Healthy. La replica secondaria rileva la mancata ricezione di aggiornamenti dal primario e tenta inutilmente di riallinearsi. Il problema diventa evidente solo a posteriori, quando ormai la replica è già compromessa.

Questo è un estratto dei log relativi al gruppo AG “malato”, presenti nei log del secondario:

Quando il nodo secondario viene riavviato o torna disponibile dopo un’interruzione, tenta automaticamente di riallinearsi con il nodo primario. Dopo aver ristabilito la connessione al cluster e individuato il database in questione, la replica entra nello stato CHECK_IF_SEEDING_NEEDED, durante il quale verifica se è necessario inizializzare completamente la replica (SEEDING), ad esempio perché manca il database, oppure se può proseguire con il semplice allineamento tramite log delle transazioni (fase CATCHUP). 

In questo scenario, procede con la fase di CATCHUP e, se questo fosse un film natalizio, ad allineamento terminato correttamente RESOLVING e infine, SECONDARY, con il quale la replica assume il suo ruolo.

In realtà, dicevamo, inutile spaventarsi: gli AG sono una tecnologia ben rodata e questo processo va a buon fine nel 99,9% dei casi, con quello 0,1% solo per rendere le cose interessanti. In questo caso, infatti, l’allineamento non è stato possibile, il secondario entra in stato FAILED e si resta con un database primario inaccessibile e un secondario rotto. Tipicamente, il secondario ritenta a intervalli regolari di riallinearsi (di default, ogni 30 minuti) fino ad un massimo di retry consentiti, qui senza successo.

Dopo analisi approfondite dei log, test replicati su ambienti differenti, e numerosi tentativi di risoluzione, non è ancora stato possibile identificare con certezza la causa del problema. In rete si trovano segnalazioni simili, segno che non si tratta di un caso isolato, ma di un comportamento anomalo noto, ancora privo di una soluzione ufficiale. Un dettaglio importante: il problema si è manifestato anche senza un failover attivo, ma semplicemente in seguito al riavvio del nodo secondario. Questo suggerisce che possa essere innescato da una condizione transitoria di mancata comunicazione tra le repliche, non necessariamente da un cambio di ruolo.

In attesa di un fix da parte di Microsoft, abbiamo messo a punto un piano d’azione operativo, ampiamente testato e pronto ad entrare in gioco ogni volta che si ripresenta la stessa condizione. La chiave di tutto è la proattività: rilevare tempestivamente il problema e agire rapidamente per minimizzare l’impatto.
 

Datemi un alert e vi solleverò il mondo

Dopo un failover o una perdita di comunicazione tra le repliche, il primo segnale che qualcosa non va è spesso il più semplice: il database non è accessibile. Che si tratti di un’applicazione in errore o di un tentativo diretto via SQL Server Management Studio, la connessione fallisce.

Di default, i tentativi di login non riusciti vengono registrati nei log di SQL Server. Questo può aiutare in una fase di analisi a posteriori, per individuare l’esatto momento in cui il database è diventato inaccessibile. Tuttavia, quando la situazione non rientra da sola e non ci sono cause apparenti (ad esempio, si sta tentando di accedere a una replica secondaria non leggibile), potrebbe trattarsi proprio del bug descritto finora.

Fatto salvo che, dopo problemi di connessione al cluster, failover, riavvio di nodi o altre situazioni che toccano le repliche è buona norma far verificare la situazione ad un DBA, affidarsi solo all’episodica osservazione manuale rischia di essere insufficiente.

Per questo, è stato messo a punto un sistema di alert proattivo, in modo da rilevare il problema mentre accade, anche se non è visibile nei classici cruscotti degli AG o se i log non mostrano anomalie evidenti. L’obiettivo dell’alert è tanto banale quanto efficace: verificare periodicamente che tutti i database primari siano effettivamente accessibili e leggibili. Questo permette di intercettare tempestivamente anche situazioni "anomale" ma formalmente consistenti, come nel nostro caso o in altre situazioni dove per qualche motivo il database non è aperto (ad esempio, perché portato offline da SQL Server per problemi di corruzione file o consistenza, non che quello sia molto piacevole). 

L’alert esegue un controllo ogni minuto su tutte le istanze coinvolte nel cluster (supponendo che siano tutte monitorate). La logica è semplice: per ogni database ospitato come replica primaria sull’istanza, esegue una query su una tabella di sistema (sys.tables) per verificarne l’accessibilità. Se la query fallisce, scatta l’allarme (si tratta di alert che valuta una condizione booleana: se TRUE, partono le sirene). Questo approccio funziona anche se si sta monitorando solo l’attuale replica primaria tramite listener: saranno interrogati solo i database attivi, rilevando correttamente i casi critici.

DECLARE @dbname sysname
DECLARE @test nvarchar(5)
DECLARE @sqltext nvarchar(100)
DECLARE @tempResult int
DECLARE @tempResults2 int
DECLARE @AGs TABLE (
    DBname sysname)

--Ottengo il nome dei database primari sull'istanza corrente
INSERT @AGs
    SELECT
        Cluster_States.database_name AS DBname
        FROM master.sys.availability_groups Groups
        INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
        INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
        INNER JOIN master.sys.availability_group_listeners Listeners ON Groups.group_id = Listeners.group_id
        INNER JOIN master.sys.dm_hadr_database_replica_states DBStates ON Replicas.replica_id = DBStates.replica_id
        INNER JOIN master.sys.dm_hadr_database_replica_cluster_states Cluster_States ON DBStates.group_database_id = Cluster_States.group_database_id
            AND Replicas.replica_id = Cluster_States.replica_id
        WHERE primary_replica  = replica_server_name AND primary_replica = @@SERVERNAME;

SET @test = 'FALSE';

DECLARE db_cursor CURSOR FAST_FORWARD FOR
    SELECT DBname FROM @AGs;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sqltext = 'SELECT TOP 1 @tmpresult = 1 FROM ' + QUOTENAME(@dbname) + '.sys.tables WITH (NOLOCK);';

    --Interrogo il database
    BEGIN TRY
        EXEC @tempResult = sp_executesql @sqltext, N'@tmpresult int OUTPUT', @tmpresult = @tempResults2 OUTPUT;
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;  --Dovesse fallire il test, otteniamo la motivazione
        SET @test = 'TRUE'
    END CATCH

    FETCH NEXT FROM db_cursor INTO @dbname;

END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

SELECT @test;

Workaround e soluzione per ripristinare un Database AG inaccessibile

Per ripristinare l’operatività del database, l’unico workaround realmente efficace è disattivare il Commit sincrono, andando a svincolare temporaneamente le due repliche. In questo modo, la replica primaria torna accessibile mentre la secondaria (una volta online) andrà a riallinearsi senza vincolare il commit delle transazioni in corso. 

Al contempo, per il vincolo citato precedentemente, occorre mettere i database in failover_mode manuale.

Tuttavia, si tratta solo di una soluzione temporanea: anche dopo il completo riallineamento delle repliche (cioè una volta smaltiti i transaction log in attesa), riattivando il commit sincrono il problema si ripresenta. Il database torna inaccessibile, come se nulla fosse cambiato.

L’unico modo certo per uscire da questa condizione è ricostruire completamente la replica del database coinvolto andando a:

1. Rimuovere il database dall’Availability Group
2. Droppare il database nella replica secondaria (che a questo punto non è più allineata)
3. Aggiungere nuovamente il database all’AG, ricostruendo il database nella replica con uno dei metodi supportati (ad esempio l’automatic seeding o il ripristino di un backup e l’applicazione dei log, seguiti dalla sincronizzazione).

Grazie al workaround del commit asincrono, nel corso di queste operazioni il database rimarrà accessibile, ma occorre la massima attenzione: fino al completamento del processo, il database non potrà essere oggetto di failover, rendendolo un single point of failure temporaneo. In caso di nuovo problema al nodo primario durante questa fase, il servizio sul database affetto non potrà essere garantito.

In conclusione

Quello che conta, anche in situazioni frustranti a causa dell’assenza di soluzioni perfette e spiegazioni ufficiali, è portare a casa il risultato. Ora siamo consapevoli della problematica, siamo in grado di individuarla non appena si verifica e abbiamo una strategia pronta per la risoluzione.

Tutto questo a riaffermare, se mai ce ne fosse bisogno, il fondamentale valore del tempo speso nel testing, della consulenza continua e del rapporto con il cliente, che si affida a noi per risolvere il problema. Non è questione di cluster o SQL Server, è questione di far funzionare le cose, ogni giorno.

Alla prossima puntata!