Risolvere i problemi di un database del gruppo di disponibilità in stato di ripristino

Questo articolo consente di risolvere i problemi di un database del gruppo di disponibilità in stato di ripristino.

Che cos'è il ripristino dello stato?

Lo stato di ripristino si verifica quando il server secondario deve annullare le modifiche già applicate per tornare sincronizzato con il server primario.

Le repliche primarie e secondarie del gruppo di disponibilità rimangono in uno stato connesso durante il normale funzionamento, in modo che le modifiche nella replica primaria vengano sincronizzate attivamente con le repliche secondarie.

Durante i failover, questo stato connesso viene interrotto. Una volta che la nuova replica primaria è disponibile online, la connettività viene ristabilita tra la replica primaria e le repliche secondarie. Durante questo stato di connessione iniziale, viene negoziato un punto di ripristino comune in cui il nuovo database secondario deve avviare il ripristino in modo che sia sincronizzato con il database primario.

Se una transazione di grandi dimensioni è in esecuzione al momento del failover, il nuovo log del database secondario è precedente al database di replica primaria. Il nuovo punto di ripristino comune negoziato richiederà alla replica secondaria di ricevere pagine dalla replica primaria affinché si trovi in uno stato in cui la sincronizzazione possa riprendere. Il processo di ripristino è anche denominato "Annulla di ripetimento".

Il processo di ripristino è intrinsecamente lento, si verifica spesso e in genere le transazioni di piccole dimensioni che attivano lo stato di ripristino non vengono notati. Lo stato di ripristino viene spesso notato quando il failover interrompe una transazione di grandi dimensioni, causando l'invio di molte pagine dal database primario al database secondario per ripristinare lo stato ripristinabile del database di replica secondaria.

Sintomi ed effetto di un database del gruppo di disponibilità in stato di ripristino

Quando un database è in stato di ripristino nella replica secondaria, il database non viene sincronizzato e pertanto non riceve modifiche dalla replica primaria. Una perdita improvvisa del database nella replica primaria potrebbe causare la perdita di dati.

Always On dashboard segnala la mancata sincronizzazione nel database primario

Dopo aver eseguito il failover di un gruppo di disponibilità, è possibile notare che il database secondario viene segnalato come non sincronizzato mentre il failover ha avuto esito positivo. Il dashboard Always On segnala la mancata sincronizzazione nel database primario e il ripristino nel database secondario.

Always On dashboard segnala la mancata sincronizzazione nel database primario	Always On dashboard segnala il ripristino del database secondario

Always On DMV segnala NOT SYNCHRONIZING nel database primario

Quando si esegue una query sui gruppi di disponibilità (DMV) Always On seguenti nel database primario, lo stato del database è NOT SYNCHRONIZING.

SELECT DISTINCT ar.replica_server_name, drcs.database_name, drs.database_id, drs.synchronization_state_desc, drs.database_state_desc
FROM sys.availability_replicas ar 
JOIN sys.dm_hadr_database_replica_states drs 
ON ar.replica_id=drs.replica_id 
JOIN sys.dm_hadr_database_replica_cluster_states drcs
ON drs.group_database_id=drcs.group_database_id

Quando si eseguono query sulle DMV sul database secondario, il database del gruppo di disponibilità è in stato REVERTING .

I carichi di lavoro di sola lettura e creazione di report non riescono ad accedere al database secondario

Mentre il database secondario viene ripristinato, non è possibile accedervi o eseguirne una query. Questi carichi di lavoro di sola lettura sono offline e interrotti. A seconda di quanto tempo il database è in stato di ripristino, potrebbe essere necessario reindirizzare tali carichi di lavoro a un'altra replica secondaria o alla replica primaria se questi carichi di lavoro sono di importanza critica per l'azienda.

Se si dispone di un carico di lavoro di sola lettura, ad esempio un carico di lavoro di report indirizzato alla replica secondaria, questi batch potrebbero non riuscire con il messaggio 922:

Msg 922, Level 14, State 1, Line 2 Database 'agdb' is being recovered. In attesa del completamento del ripristino.

Un'applicazione che tenta di accedere al database di replica secondaria in stato di ripristino non riesce a connettersi e genera l'errore 18456:

2023-01-26 13:01:13.100 Errore di accesso: 18456, Gravità: 14, Stato: 38. 2023-01-26 13:01:13.100 Accesso non riuscito per l'utente '<UserName>'. Motivo: impossibile aprire il database 'agdb' specificato in modo esplicito. [CLIENT: <computer> locale]

Questo errore può essere segnalato anche nel log degli errori SQL Server se vengono controllati gli account di accesso non riusciti.

Stimare il tempo rimanente nello stato di ripristino

Usare uno dei metodi seguenti per stimare il tempo rimanente nello stato di ripristino:

Usare la sessione XEvent AlwaysOn_health

Il registro di diagnostica eventi esteso AlwaysOn_health dispone di un evento hadr_trace_message che segnala l'avanzamento dello stato di ripristino ogni cinque minuti.

Connettersi alla replica secondaria usando SQL Server Management Studio (SSMS) Esplora oggetti ed eseguire il drill-in Gestione, Eventi estesi e quindi Sessioni. Fare clic con il pulsante destro del mouse sull'evento AlwaysOn_health e scegliere Watch Live Data (Guarda dati live). È consigliabile ottenere una nuova finestra a schede che segnala lo stato corrente dell'operazione di ripristino. Lo stato viene segnalato ogni cinque minuti tramite l'evento hadr_trace_message e viene segnalata la percentuale di operazione di ripristino completata.

Nota

L'evento hadr_trace_message esteso è stato aggiunto agli aggiornamenti cumulativi più recenti in SQL Server 2019 e versioni successive. È necessario eseguire gli aggiornamenti cumulativi più recenti per osservare questo evento esteso nella AlwaysOn_health sessione di eventi estesa.

Il log degli errori SQL Server nella replica secondaria non è molto utile quando si stima il ripristino del completamento. Dall'immagine seguente è possibile osservare da 10:08 a 11:03 mentre è in stato di ripristino, viene segnalato poco. Dopo che il database secondario ha ricevuto tutte le pagine dalla replica primaria, è ora in grado di eseguire il rollback della transazione in esecuzione nel database primario originale che ha attivato lo stato di ripristino. Il ripristino viene eseguito dalle 11:03 alle 11:05. Poco dopo il completamento del ripristino, il database deve iniziare a eseguire la sincronizzazione con la replica primaria e recuperare tutte le modifiche apportate al database primario mentre il database secondario era in stato di ripristino.

Monitorare il ripristino del tempo di completamento usando Monitor prestazioni

Monitorare lo stato di ripristino dello stato usando i contatori delle prestazioni SQL Server:D Replicaatabase:Total Log Requiring Undo and SQL Server:D atabase Replica:Log Remaining for Undo e scegliere il database del gruppo di disponibilità per l'istanza. Nell'esempio nello screenshot seguente, total log requiring undo is reported as 56.3 mb, and Log Remaining for Undo is slowly drop to 0 that is reporting reverting progress.

Quali sono le altre opzioni oltre all'attesa?

Microsoft consiglia di stimare il tempo per il completamento dello stato di ripristino.

Aggiungere una replica secondaria a un gruppo di disponibilità

Se nel gruppo di disponibilità sono presenti solo due repliche, se possibile, aggiungere un'altra replica secondaria in grado di offrire disponibilità elevata e ridondanza e sincronizzarsi attivamente con la replica primaria mentre l'altra replica secondaria completa il ripristino.

Importante

Non eseguire il failover in una replica i cui database sono in stato di ripristino. Ciò può comportare un database inutilizzabile che richiede un ripristino dal backup. Non riavviare l'istanza di replica secondaria, non accelererà questo processo e potrebbe compromettere lo stato del database di replica secondaria in stato di ripristino.

Come risolvere i carichi di lavoro di sola lettura non riusciti

Poiché i database di replica secondaria nel ripristino non sono accessibili, i carichi di lavoro di sola lettura hanno esito negativo. Aggiornare la tabella di routing delle finalità di lettura per instradare il traffico alla replica primaria o a un'altra replica secondaria fino a quando il database di replica secondaria interessato non completa il processo di ripristino.

Evitare di ripristinare lo stato : monitorare le transazioni di grandi dimensioni

Se si osserva spesso questo stato durante i failover pianificati, implementare una procedura che controlla la presenza di transazioni di grandi dimensioni prima dei failover. La query seguente segnala l'ora di inizio e l'ora corrente delle transazioni aperte nel sistema e fornisce il buffer di input delle transazioni.

SELECT tat.transaction_begin_time, getdate() AS 'current time', es.program_name, es.login_time, es.session_id, tst.open_transaction_count, eib.event_info
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id=tst.transaction_id
JOIN sys.dm_exec_sessions es ON tst.session_id=es.session_id 
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) eib WHERE es.is_user_process = 1
ORDER BY tat.transaction_begin_time ASC