Risolvere i problemi relativi al failover dei gruppi di disponibilità Always On

Sommario

Questo articolo illustra la procedura di risoluzione dei problemi per determinare il motivo del failover del gruppo di disponibilità AlwaysOn in SQL Server. Illustra come identificare gli eventi di integrità nel log del cluster di Windows, diagnosticare cause comuni come eventi di integrità del cluster, timeout di lease, timeout del controllo di integrità e problemi di integrità di SQL Server e applicare correzioni per ciascuna di esse.

Come i problemi di integrità di Always On e il failover influiscono sui carichi di lavoro

Always On implementa un solido monitoraggio dell'integrità tramite meccanismi diversi per garantire l'integrità dell'istanza di Microsoft SQL Server che ospita la replica primaria, il cluster sottostante e l'integrità del sistema. Il carico di lavoro di produzione viene interrotto momentaneamente quando viene identificato un cluster Windows o un problema di integrità Always On.

Quando viene rilevata una condizione di salute, in genere si verifica la seguente sequenza di eventi. In questo strumento di risoluzione dei problemi, si fa riferimento ai seguenti eventi di stato:

• Le repliche e i database del gruppo di disponibilità passano dal ruolo primario al ruolo di risoluzione.

• I database del gruppo di disponibilità passano alla modalità offline e non sono più accessibili.

• Cluster windows contrassegna la risorsa cluster del gruppo di disponibilità come non riuscita.

• Cluster Windows tenta di riportare online il ruolo del gruppo di disponibilità (nella replica partner di failover originale o automatica).

• Il ruolo del gruppo di disponibilità viene portato online con successo se viene rilevato come in stato integro dal monitoraggio dell'integrità di Always On e di Windows Cluster.

In caso di esito positivo, le repliche e i database del gruppo di disponibilità passano al ruolo primario e i database del gruppo di disponibilità vengono online e sono accessibili dall'applicazione.

Le applicazioni non possono accedere ai database del gruppo di disponibilità

Quando il sistema rileva un problema relativo all'integrità, la replica del gruppo di disponibilità e i relativi database passano al ruolo Resolving e i database del gruppo di disponibilità vengono disconnessi. Dopo che la replica viene online nel ruolo primario (nel server di replica originale o nel server di replica partner di failover), la replica e i database tornano online. Mentre la replica e i database vengono risolti e sono offline, tutte le applicazioni che tentano di accedere a tali database del gruppo di disponibilità hanno esito negativo e generano un messaggio "Errore 983": Unable to access availability database.... Se SQL Server è configurato per registrare tentativi di accesso non riusciti, registra anche questo errore nel log degli errori Microsoft SQL Server.

Logon Error: 983, Severity: 14, State: 1.

Logon Unable to access availability database '<databasename>' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

Il periodo durante il quale il gruppo di disponibilità è nello stato di risoluzione prima che torni online nel ruolo primario dura in genere solo pochi secondi o anche meno di un secondo.

Identificare e diagnosticare gli eventi relativi allo stato di integrità o il failover del gruppo di disponibilità Always On

Identificare le tendenze sullo stato di integrità di Always On

È possibile analizzare un singolo evento di integrità Always On oppure potrebbe verificarsi una tendenza recente o continuativa di problemi di integrità che interrompono in modo intermittente la produzione. Le domande seguenti possono aiutarti a restringere il campo e a mettere in correlazione le modifiche recenti nel tuo ambiente di produzione che potrebbero essere collegate a questi problemi di stato:

• Quando è iniziata la tendenza degli eventi di integrità Always On o del cluster?
• Gli eventi relativi allo stato di integrità si verificano in un giorno specifico?
• Gli eventi di integrità si verificano in un momento specifico della giornata?
• Gli eventi relativi allo stato di integrità si verificano in un determinato giorno o in una determinata settimana del mese?

Se si individua una tendenza, verificare la manutenzione pianificata del sistema (il sistema host in un ambiente virtuale), i batch ETL e altri processi pianificati che potrebbero essere correlati a questi eventi relativi allo stato di integrità. Se il sistema è una macchina virtuale, esaminare il sistema host per individuare le modifiche eventualmente introdotte al momento delle interruzioni.

Considerare carichi di lavoro di produzione ad hoc particolarmente intensi che potrebbero coincidere con il momento in cui si verificano problemi di stato del sistema, ad esempio quando gli utenti accedono per la prima volta al sistema o quando rientrano dalla pausa pranzo.

Esaminare il log del cluster

Il log del cluster Windows è il log più completo per identificare il tipo di evento relativo all'integrità di Always On o del cluster e la condizione di integrità rilevata che ha causato l'evento. Per generare e aprire il log del cluster, seguire questa procedura:

Usare Windows PowerShell per generare il log del cluster windows nel nodo del cluster che ospita la replica primaria al momento dell'evento di integrità. Ad esempio, eseguire il cmdlet seguente in una finestra di PowerShell con privilegi elevati usando sql19agn1 come nome del server basato su SQL Server:

get-clusterlog -Node sql19agn1 -UseLocalTime     

Trova l'evento di stato nel log del cluster

Always On usa diversi meccanismi di monitoraggio dello stato di integrità per monitorare lo stato di integrità del gruppo di disponibilità. Oltre a un evento di integrità del cluster Windows (in cui Cluster Windows rileva un problema di integrità tra i nodi del cluster), Always On include quattro diversi tipi di controlli di integrità:

• Il servizio SQL Server non è in esecuzione
• Timeout del lease di SQL Server
• Timeout del controllo dello stato di SQL Server
• Un problema di integrità interno di SQL Server

È possibile individuare uno qualsiasi di questi eventi di integrità specifici di Always On cercando nel log del cluster la stringa [hadrag] Resource Alive result 0. Il log del cluster salva questa stringa quando rileva uno di questi eventi. Ad esempio:

00001334.00002ef4::2019/06/24-18:24:36.153 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0.

È possibile usare uno strumento per trovare tutti gli eventi di integrità nel log del cluster in modo da poter generare un report di riepilogo dei problemi di integrità Always On. Questo report consente di identificare le tendenze cronologiche e determinare se una determinata condizione di integrità Always On è ricorrente. Lo screenshot seguente mostra come usare un editor di testo (NotePad++, in questo caso) per trovare tutte le righe nel log del cluster che contengono la [hadrag] Resource Alive result 0 stringa:

Identificare e risolvere il problema di integrità che ha attivato il failover

Per identificare i problemi di stato nel log del cluster della replica primaria, confrontali con i problemi descritti nelle sezioni seguenti. Tra i motivi più comuni del failover di AG vi sono:

• Evento sullo stato di integrità del cluster
• Il servizio SQL Server è inattivo (evento di integrità Always On)
• Timeout del lease (un evento di integrità di Always On)
• Timeout del controllo di integrità (un evento di integrità Always On)
• Stato di integrità di SQL Server (un evento di stato di integrità di Always On)

Eventi relativi allo stato di integrità del cluster

Microsoft Windows Cluster monitora l'integrità dei server membri nel cluster. Se rileva un problema di integrità, rimuove un server membro del cluster dal cluster. Le risorse del cluster, incluso il ruolo del gruppo di disponibilità ospitato nel server membro del cluster rimosso, si spostano nella replica partner di failover del gruppo di disponibilità se è configurata per il failover automatico.

Sintomi

Ecco un esempio di evento relativo all'integrità del cluster nel log del cluster. Per trovarlo, cercare Lost quorum o Cluster service has terminated, poiché uno dei due potrebbe essere presente durante il cambio di ruolo o il failover del gruppo di disponibilità.

00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: Lost quorum (1)
00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: goingAway: 0, core.IsServiceShutdown: 0
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925)
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [NETFT] Cluster Service preterminate succeeded.
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925), executing OnStop
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM]: Shutting down, so unloading the cluster database.
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM] Shutting down, so unloading the cluster database (waitForLock: false).
000019cc.000019d0::2022/12/15-14:26:02.654 WARN [RHS] Cluster service has terminated. Cluster.Service.Running.Event got signaled.

È anche possibile identificare questo evento eseguendo una ricerca nel registro eventi di sistema Windows.

Critical SQL19AGN1.CSSSQL 1135 Microsoft-Windows-FailoverClusterin Node Mgr NT AUTHORITY\SYSTEM Cluster node 'SQL19AGN2' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Critical SQL19AGN1.CSSSQL 1177 Microsoft-Windows-FailoverClusterin Quorum Manager NT AUTHORITY\SYSTEM The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Diagnosticare un evento di integrità del cluster

Gli errori nel registro eventi di Windows (eventi 1135 e 1177) suggeriscono che la connettività di rete è una causa dell'evento. Questa condizione è il motivo più comune per cui viene rilevato un problema di integrità del cluster. L'esempio seguente mostra che altri server membri del cluster non possono comunicare con questo server che ospita la replica primaria del gruppo di disponibilità e che questo problema ha attivato la rimozione del nodo del cluster dal cluster:

00000fe4.00001edc::2022/12/14-22:44:36.870 INFO [NODE] Node 1: New join with n3: stage: 'Attempt Initial Connection' status (10060) reason: 'Failed to connect to remote endpoint <endpoint address>'
00000fe4.00001620::2022/12/15-14:26:02.050 INFO [IM] got event: Remote endpoint <endpoint address> unreachable from <endpoint address>
00000fe4.00001620::2022/12/15-14:26:02.050 WARN [NDP] All routes for route (virtual) local <local address> to remote <remote address> are down
00000fe4.0000179c::2022/12/15-14:26:02.053 WARN [NODE] Node 1: Connection to Node 2 is broken. Reason GracefulClose(1226)' because of 'channel to remote endpoint <endpoint address> is closed'

È possibile cercare nel log del cluster l'evidenza di un errore di connessione al nodo. Dalla posizione nel log del cluster in cui è stato trovato Lost quorum, cercare a ritroso stringhe come Failed to connect to remote endpoint, unreachable e is broken.

Soluzione

Assicurarsi che il monitoraggio dell'integrità del cluster sia adatto all'ambiente host. Per altre informazioni sui gruppi di disponibilità AlwaysOn di SQL Server ospitati in Microsoft Azure, vedere Panoramica del cluster di failover di Windows Server - SQL Server in macchine virtuali di Azure.

Se necessario, valuta di contattare il supporto Microsoft Windows per l'alta disponibilità per aprire un ticket di supporto.

Servizio SQL Server inattivo: evento di integrità Always On

Il monitoraggio dell'integrità AlwaysOn può rilevare se il servizio SQL Server che ospita la replica primaria del gruppo di disponibilità non è più in esecuzione.

Sintomi

Ecco un esempio del report del log del cluster per il ruolo del gruppo di disponibilità 'ag' che indica un errore perché QueryServiceStatusEx ha restituito un ID processo 0:

00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] QueryServiceStatusEx returned a process id 0
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] SQL server service is not alive
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] Resource Alive result 0.
00001898.0000185c::2023/02/27-13:27:41.121 WARN [RHS] Resource ag IsAlive has indicated failure.

Diagnosticare gli eventi di arresto del servizio SQL

Controllare il registro eventi di sistema di Windows e il log degli errori di SQL Server per un arresto imprevisto di SQL Server.

Se SQL Server è stato arrestato da un arresto del sistema o da un arresto amministrativo, viene visualizzata la voce seguente nel log degli errori SQL Server:

2023-03-10 09:38:46.73 spid9s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.

Il registro eventi di sistema Windows mostra la voce di errore seguente:

Information 3/10/2023 9:41:06 AM Service Control Manager 7036 None The SQL Server (MSSQLSERVER) service entered the stopped state.

Il registro eventi di sistema di Windows mostra la voce di errore seguente se SQL Server si arresta in modo imprevisto:

Error 3/10/2023 8:37:46 AM Service Control Manager 7034 None The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).

Controllare la fine del log degli errori di SQL Server per individuare gli indizi. Se il log degli errori termina improvvisamente, questa condizione indica che si è verificato un arresto forzato. Ad esempio, se SQL Server è stato terminato tramite Gestione attività, il report degli errori SQL Server non rivela informazioni su eventuali problemi interni che potrebbero aver causato l'arresto del processo.

Soluzione

Assicurarsi che gli amministratori di sistema e del database autorizzati abbiano accesso al sistema per ridurre al minimo le terminazioni impreviste del servizio SQL Server. Dopo aver esaminato i log eventi, esaminare il motivo per cui un servizio doveva essere terminato in modo imprevisto.

Se un problema di integrità interno di SQL Server ha causato l'interruzione imprevista di SQL Server, potrebbero esserci indizi di una possibile eccezione irreversibile (incluso un file di diagnostica del dump della memoria generato) alla fine del log degli errori SQL. Esaminare gli indizi e intraprendere l'azione necessaria. Se si trova un file di dump, è consigliabile contattare il supporto di Microsoft SQL Server e fornire il contenuto del file di dump e del log degli errori di SQL Server per ulteriori indagini.

Timeout del lease: un evento di stato di Always On

Always On usa un meccanismo di "lease" per monitorare l'integrità del computer in cui è installato SQL Server. Il timeout di lease predefinito è di 20 secondi.

Sintomi

Di seguito è riportato un output di esempio di timeout di lease Always On dal log del cluster. È possibile cercare queste stringhe per individuare un timeout di lease nel log del cluster.

00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Availability Group lease is no longer valid 
00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0. 
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:35:57.0, 98.068572, 509227008.000000, 0.000395, 0.000350 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:7.0, 12.314941, 451817472.000000, 0.000278, 0.000266 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:17.0, 17.270742, 416096256.000000, 0.000376, 0.000292 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:27.0, 38.399895, 416301056.000000, 0.000446, 0.000304 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:37.0, 100.000000, 417517568.000000, 0.001292, 0.000666

Per ulteriori informazioni sul timeout del lease, vedere la sezione Meccanismo di lease in Funzionamento e linee guida dei timeout del lease, del cluster e del controllo di integrità per i gruppi di disponibilità Always On.

Diagnosticare e risolvere gli eventi di timeout del lease Always On

Due problemi principali possono causare un timeout del lease:

• Dump della memoria di SQL Server: quando SQL Server rileva determinati eventi interni relativi all'integrità del sistema, ad esempio una violazione di accesso, un errore di asserzione o un deadlock dello scheduler, genera un file dump diagnostico (.mdmp) nella cartella \LOG di SQL Server. Il processo di generazione di un dump della memoria sospende l'esecuzione di SQL Server per un breve periodo. In quel periodo, il meccanismo di lease può rilevare l'assenza di risposta del servizio e attivare un'azione. Per altre informazioni, vedi Impatto della generazione di dump.

• Un problema di prestazioni a livello di sistema: un timeout del lease non indica necessariamente un problema relativo allo stato di SQL Server. Potrebbe invece indicare un problema di integrità a livello di sistema che influisce anche sull'integrità del server basato su SQL Server.

  • Utilizzo elevato della CPU nel sistema (vicino al 100%).
  • Condizioni di memoria insufficiente: memoria virtuale insufficiente e/o uno dei processi in fase di paging.
  • WSFC in modalità offline a causa della perdita del quorum.
  • Limitazione delle risorse delle VM che compromette le prestazioni e causa la scadenza del lease.

Soluzione

Per informazioni dettagliate sulla risoluzione dei problemi, vedere MSSQLSERVER_19407. Ecco i due problemi più comuni:

1. Diagnostica del file dump di SQL Server

SQL Server potrebbe rilevare un problema interno di stato, ad esempio una violazione di accesso, un'asserzione o scheduler in deadlock. In questo caso, il programma genera un mini file dump (con estensione mdmp) nella cartella SQL Server \LOG del processo di SQL Server per la diagnosi. Il processo di SQL Server viene bloccato per diversi secondi mentre il file di mini dump viene scritto su disco. Durante questo periodo, tutti i thread nel processo di SQL Server sono in uno stato sospeso, incluso il thread lease monitorato dal monitoraggio di integrità di Always On. Di conseguenza, Always On potrebbe rilevare un timeout di lease.

**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   11/02/14 21:21:10 spid 1920
*
* Deadlocked Schedulers
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
Stack Signature for the dump is 0x00000000000002BA
Error: 19407, Severity: 16, State: 1.
The lease between availability group 'ag' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Per risolvere questo problema, analizzare il file di dump della memoria per individuare la causa principale. È consigliabile contattare il supporto di Microsoft SQL Server per fornire il contenuto del file di dump e del log degli errori di SQL Server per ulteriori indagini.

2. Utilizzo elevato della CPU o altro problema di prestazioni del sistema

Un timeout del lease indica un problema di prestazioni che interessa l'intero sistema, incluso SQL Server. Per diagnosticare il problema del sistema, i report di diagnostica di integrità di Always On riportano i dati del monitoraggio delle prestazioni nel log del cluster e includono l'evento di timeout del lease. I dati sulle prestazioni coprono circa 50 secondi che precedono l'evento di timeout del lease e riportano l'utilizzo della CPU, la memoria libera e la latenza del disco.

Di seguito è riportato un esempio dei dati sulle prestazioni segnalati che mostrano un timeout di lease nel log del cluster. In questo output di esempio, un elevato utilizzo complessivo della CPU potrebbe essere correlato al timeout del lease.

00000f90.000015c0::2020/08/07-14:16:41.378 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00000f90.000015c0::2020/08/07-14:16:41.382 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:20.0, 83.266073, 31700828160.000000, 0.018094, 0.015752
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:30.0, 93.653224, 31697063936.000000, 0.038590, 0.026897
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:40.0, 94.270691, 31696265216.000000, 0.166000, 0.038962
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:50.0, 90.272016, 31695409152.000000, 0.215141, 0.106084
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:16:1.0, 99.991336, 31695892480.000000, 0.046983, 0.035440

Se i dati sulle prestazioni mostrano un utilizzo elevato della CPU, una condizione di memoria insufficiente o una latenza elevata del disco al momento di un timeout del lease, inizia a raccogliere i dati di Monitor prestazioni per l'intera giornata sulla replica primaria per analizzare questi sintomi. Acquisendo i dati di monitoraggio delle prestazioni in un periodo più lungo, è possibile identificare meglio i valori di base e di picco per queste risorse e monitorare le modifiche in queste risorse quando si verifica un timeout del lease. Durante la raccolta di questi dati, considera se vi siano carichi di lavoro pianificati o ad hoc in SQL Server che coincidono con il momento in cui si verificano questi problemi relativi alle risorse e gli eventi relativi allo stato di integrità.

È anche necessario acquisire contatori che segnalano lo stesso utilizzo delle risorse di sistema, inclusi i seguenti:

• Processor Information::% Processor Time
• Memory::Available MBytes
• Logical Disk::Avg. Disk sec/Read
• Logical Disk::Avg. Disk sec/Write
• Logical Disk::Avg. Disk Read Queue Length
• Logical Disk::Avg. Disk Write Queue Length
• MSSQLServer:SQL Statistics::Batch Requests/sec

Timeout del controllo di integrità: un evento di integrità di Always On

Always On usa un meccanismo di controllo integrità per monitorare l'integrità di SQL Server e la possibilità per le applicazioni client di connettersi.

Sintomi

Quando una replica del gruppo di disponibilità passa al ruolo primario, il monitoraggio dello stato di integrità di Always On stabilisce una connessione ODBC locale all'istanza di SQL Server. Mentre Always On è connesso e monitorato, se SQL Server non risponde alla connessione ODBC entro il periodo impostato per il timeout del controllo integrità del gruppo di disponibilità (il valore predefinito è 30 secondi), Always On attiva un evento di timeout del controllo integrità. In questo caso, il gruppo di disponibilità passa dal ruolo primario al ruolo Risoluzione e avvia il failover, se è configurato per eseguire questa operazione.

Per altre informazioni sui timeout del controllo integrità, vedere la sezione "Operazione di timeout del controllo integrità" in Meccanismi e linee guida dei timeout di lease, cluster e controllo integrità per i gruppi di disponibilità Always On.

Di seguito è riportato un timeout del controllo integrità Always On come indicato nel log del cluster:

0000211c.00002d70::2021/02/24-02:50:01.890 WARN [RES] SQL Server Availability Group: [hadrag] Failed to retrieve data column. Return code -1
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Resource Alive result 0.
0000211c.00002594::2021/02/24-02:50:02.453 WARN [RHS] Resource AG IsAlive has indicated failure.
00001278.00002ed8::2021/02/24-02:50:02.453 INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'AG', gen(0) result 1/0.

Diagnosticare e risolvere gli eventi di timeout del controllo dello stato di integrità di Always On

La sezione seguente consente di esaminare i log di SQL Server per individuare eventi "breadcrumb" che si possono trovare e che sono correlati ai timeout dei controlli di integrità di Always On rilevati e segnalati. I log che si esaminano qui includono il log del cluster (in cui viene confermato il timeout del controllo di integrità), i log degli eventi estesi system_health e i log degli errori di SQL Server (entrambi presenti nella cartella di SQL Server \LOG), nonché il registro eventi di sistema di Windows. Utilizzare questi e altri log per cercare eventi correlati che potrebbero aiutare a circoscrivere la causa del timeout del controllo di integrità.

1. Verificare la presenza di eventi dello scheduler che non rilasciano il controllo

Il timeout del controllo integrità Always On è spesso causato da eventi "non restituiti" in SQL Server. Quando SQL Server rileva che un thread non cede il controllo a uno scheduler, segnala che si è verificato un evento di scheduler non yielding. Se si notano altre attività nello stesso scheduler che non ricevono tempo CPU, questa condizione è il principale segnale di uno scheduler non cooperativo. Questo comportamento può causare un ritardo nell'esecuzione di tali attività e privare del tempo di CPU i carichi di lavoro assegnati a un determinato scheduler.

Per verificare la presenza di eventi dello scheduler che non risponde, seguire questi passaggi:

1. Controllare i log degli eventi estesi di SQL Server system_health per determinare se sia stato segnalato un evento di utilità di pianificazione che non cede, di qualche tipo, in prossimità dell'evento di timeout del controllo di integrità di Always On. Gli eventi che potrebbero non restituire risultati includono i seguenti:

   • scheduler_monitor_non_yielding_ring_buffer_recorded
   • scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
   • scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
   • scheduler_monitor_non_yielding_rm_ring_buffer_recorded

2. Aprire i log degli eventi estesi system_health di SQL Server sulla replica primaria in corrispondenza del momento in cui si sospetta il timeout del controllo di integrità.

3. In SQL Server Management Studio (SSMS) passare a File>Open e selezionare Merge Extended Event Files.

4. Seleziona il pulsante Aggiungi.

5. Nella finestra di dialogo File Open passare ai file nella directory SQL Server \LOG.

6. Premere e tenere premuto Controllo, quindi selezionare i file i cui nomi iniziano con system_health_xxx.xel.

7. Selezionare Apri>OK.

8. Filtrare i risultati. Fare clic con il pulsante destro del mouse su un evento nella colonna nome e scegliere Filtra in base a questo valore.

   

9. Definire un filtro per ordinare le righe in cui i valori nella colonna name contengono yield, come illustrato nello screenshot seguente. Questo filtro restituisce tutti i tipi di eventi di non-yielding che potrebbero essere stati registrati nei system_health log.

   

10. Confrontare i timestamp per verificare se, al momento del timeout della verifica di integrità, si sono verificati eventi senza risposta. Ecco il timeout del controllo di integrità come riportato nel log del cluster:

    0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1: [hadrag] Resource Alive result 0.
    

    Si può notare che si sono verificati eventi che non producono risultati in corrispondenza del timeout del controllo dello stato di integrità.

    

Se vengono rilevati eventi senza rilascio, verificare la causa dell'evento senza rilascio. Valutare di contattare il team di supporto di SQL Server per analizzare gli eventi non-yielding.

2. Controllare il log degli errori di SQL Server

Controllare il log errori di SQL Server per individuare gli eventi corrispondenti al momento del timeout del controllo dello stato di integrità. Questi eventi potrebbero fornire indizi che suggeriscono ulteriori passaggi per circoscrivere la causa principale dei timeout del controllo dello stato di integrità.

Ad esempio, la seguente voce del log mostra che si è verificato un timeout del controllo di integrità nel log del cluster:

0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Resource Alive result 0.

Nel log degli errori SQL Server, entro pochi secondi dal timeout del controllo integrità, SQL Server segnala che è stata rilevata una latenza di I/O grave:

2021-02-23 20:49:54.64 spid12s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\agdb_log.ldf] in database id 12. The OS file handle is 0x0000000000001594. The offset of the latest long I/O is: 0x000030435b0000. The duration of the long I/O is: 26728 ms.

Controllare il registro degli eventi di sistema per individuare possibili indicazioni nel sistema che potrebbero essere correlate all'evento di timeout del controllo dello stato. Quando si consulta il registro eventi di sistema di Windows, si potrebbe trovare un problema di I/O segnalato nello stesso momento per lo stesso timeout del controllo di integrità:

02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"Reset to device, \Device\<device ID>, was issued."
02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"The IO operation at logical block address <block address> for Disk 6 (PDO name: \Device\<device ID>) was retried."

Stato di integrità di SQL Server: un evento di stato di integrità di Always On

AlwaysOn monitora diversi tipi di eventi di integrità di SQL Server. Mentre ospita una replica primaria del gruppo di disponibilità, SQL Server esegue continuamente sp_server_diagnostics, che riporta lo stato di integrità di SQL Server mediante diversi componenti. Quando vengono rilevati problemi di stato di integrità, sp_server_diagnostics segnala un errore per quello specifico componente, quindi invia i risultati al processo di rilevamento dello stato di integrità Always On. Quando viene segnalato un errore, il ruolo Gruppo di disponibilità mostra lo stato di errore e il possibile failover se il gruppo di disponibilità è configurato per eseguire questa operazione.

Sintomi

Di seguito è riportato un esempio di un problema di integrità di SQL Server come segnalato nel sp_server_diagnostics log del cluster. SQL Server segnala uno stato di errore nel componente di sistema al monitoraggio dell'integrità Always On e il gruppo di disponibilità "contoso-ag" viene passato a uno stato di errore.

INFO [RES] SQL Server Availability Group: [hadrag] SQL Server component 'system' health state has been changed from 'warning' to 'error' at 2019-06-20 15:05:52.330
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Resource Alive result 0.
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
WARN [RHS] Resource contoso-ag IsAlive has indicated failure.
INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'contoso-ag', gen(0) result 1/0.

Diagnosticare gli eventi di stato di SQL Server

Il tipo di problema segnalato dallo stato di integrità di SQL Server dovrebbe determinare la direzione dell'analisi delle cause radice.

Per impostazione predefinita, quando si distribuisce un gruppo di disponibilità, viene FAILURE_CONDITION_LEVEL impostato su tre. In questo modo viene attivato il monitoraggio di alcuni profili di integrità, ma non di tutti i profili di integrità di SQL Server. Al livello predefinito, Always On attiva un evento di stato di integrità quando SQL Server produce troppi file di dump, una violazione di accesso in scrittura o uno spinlock orfano. L'impostazione del gruppo di disponibilità sul livello quattro o cinque espande i tipi di problemi di integrità SQL Server monitorati. Per altre informazioni sui monitor di integrità di Always On di SQL Server, vedere Configurare un criterio di failover automatico flessibile per un gruppo di disponibilità - SQL Server Always On.

Per identificare il problema di integrità specifico di Always On, seguire questa procedura:

1. Aprire i log dell'evento esteso di diagnostica del cluster SQL Server sulla replica primaria fino all'ora in cui si è verificato il presunto evento di integrità di SQL Server.

2. In SSMS, passare a File>Apri e quindi selezionare Unisci file di eventi estesi.

3. Selezionare Aggiungi.

4. Nella finestra di dialogo File Open passare ai file nella directory SQL Server \LOG.

5. Premere Ctrl, selezionare i file i cui nomi corrispondono <servername>_<instance>_SQLDIAG_xxx.xela e quindi selezionare Apri>OK.

   

   Viene visualizzata una nuova finestra a schede in SSMS che include gli eventi estesi, come illustrato nello screenshot seguente.

6. Per analizzare un problema relativo allo stato di integrità di SQL Server, individuare il component_health_result il cui valore state_desc è error. Ecco un esempio di evento di un componente di sistema che ha segnalato un errore a Always On health monitoring:

   

7. Fare doppio clic sulla colonna di dati nel riquadro inferiore. Questa azione apre i dati dettagliati dei componenti in un nuovo riquadro della finestra di SSMS da esaminare. Ecco l'aspetto dei dati dei componenti di sistema:

   

   I dati totalDumprequests=186 indicano che sono presenti troppi eventi di diagnostica del file di dump generati in questo SQL Server. Questa condizione fa sì che il componente di sistema segnala uno stato di errore. Quando il monitoraggio dell'integrità Always On riceve questo stato di errore, attiva un evento di integrità per il gruppo di disponibilità. È anche possibile verificare che non vengano rilevate violazioni di accesso in scrittura o spinlock orfani in base ai dati forniti nei dati del componente di sistema.

Soluzione

A seconda del tipo di problema rilevato, risolverlo di conseguenza. Come descritto nell'articolo Configurare un criterio di failover automatico flessibile per un gruppo di disponibilità - SQL Server Always On, questa condizione può essere causata da diversi problemi. Ecco alcuni esempi:

• Indisponibilità del servizio SQL Server.
• Timeout del lease
• La replica di disponibilità è in stato di errore.
• Dump di memoria generati da spinlock orfani, violazioni di accesso o troppi dump di memoria generati in un breve periodo di tempo.
• Condizione persistente di memoria insufficiente nel pool di risorse interno di SQL Server.
• Rilevamento di un deadlock nell'utilità di pianificazione.
• Rilevamento di un deadlock irrisolvibile.

Se necessario, contattare il supporto tecnico di SQL Server per aprire un ticket di supporto e ricevere ulteriore assistenza nell’individuazione della causa principale di questi problemi interni di integrità di SQL Server.