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

Questo articolo illustra come risolvere i problemi relativi a un database del gruppo di disponibilità in stato di ripristino.

Che cos'è lo stato di ripristino?

Il ripristino dello stato 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. Dopo che la nuova replica primaria è stata 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 è in anticipo rispetto al database di replica primaria. Il nuovo punto di ripristino comune negoziato richiederà alla replica secondaria di ricevere pagine dalla replica primaria affinché sia in uno stato in cui la sincronizzazione può riprendere. Il processo di ripristino è anche denominato "Annulla di rollforward".

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

Sintomi ed effetti 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 comportare una perdita di dati.

Report del dashboard Always On non sincronizzati nel database primario

Dopo il failover di un gruppo di disponibilità, è possibile osservare che il database secondario non viene sincronizzato mentre il failover ha avuto esito positivo. Il dashboard AlwaysOn segnala non la sincronizzazione sul database primario e il ripristino sul database secondario.

Report del dashboard Always On non sincronizzati nel database primario	Report del dashboard AlwaysOn ripristinando il database secondario

Le DMV Always On non sincronizzano il database primario

Quando si esegue una query sulle DMV (Dynamic Management Views) dei gruppi di disponibilità AlwaysOn seguenti nel database primario, il database si trova nello stato 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 eseguire query. Questi carichi di lavoro di sola lettura sono offline e interrotti. A seconda della durata del ripristino dello stato del database, potrebbe essere necessario reindirizzare tali carichi di lavoro a un'altra replica secondaria o alla replica primaria se questi carichi di lavoro sono business critical.

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:

Messaggio 922, livello 14, stato 1, riga 2 database 'agdb' in corso di recupero. Attendere il completamento del processo di recupero.

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 specificato in modo esplicito 'agdb'. [CLIENT: <computer> locale]

Questo errore può essere segnalato anche nel log degli errori di 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 log di diagnostica degli eventi estesi AlwaysOn_health ha un evento hadr_trace_message che segnala lo stato di avanzamento del 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 selezionare Watch Live Data (Controlla dati live). Dovrebbe essere visualizzata 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 completata di operazione di ripristino.

Note

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

Il log degli errori di SQL Server nella replica secondaria non è molto utile quando si stima il completamento del ripristino. Dall'immagine seguente è possibile osservare da 10:08 a 11:03 mentre si ripristina lo stato, viene segnalato poco. Dopo che il database secondario ha ricevuto tutte le pagine dalla replica primaria, è ora possibile eseguire il rollback della transazione in esecuzione sul 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 dovrebbe 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 tempo di ripristino del completamento usando Monitor prestazioni

Monitorare lo stato di ripristino dello stato usando i contatori delle prestazioni SQL Server:Replica database:Totale log che richiede undo e SQL Server:Replica di database:Log rimanenti per Annulla e scegliere il database del gruppo di disponibilità per l'istanza. Nell'esempio riportato nello screenshot seguente, il log totale che richiede l'annullamento viene segnalato come 56,3 mb e Il log rimanente per Annulla viene lentamente eliminato fino a 0 che segnala lo stato di avanzamento del ripristino.

Quali sono le opzioni diverse dall'attesa?

Microsoft consiglia di stimare il tempo di 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 fornire disponibilità elevata e ridondanza e sincronizzare 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 della replica secondaria, non accelera 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 durante il 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 finché il database di replica secondaria interessato non completa il processo di ripristino.

Evitare il ripristino dello stato - Monitorare le transazioni di grandi dimensioni

Se si osserva spesso questo stato durante i failover pianificati, implementare una procedura che verifica la presenza di transazioni di grandi dimensioni prima dei failover. La query seguente segnala l'ora di inizio della transazione e l'ora corrente di tutte le 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