Risoluzione dei problemi di timeout di connessione intermittenti tra le repliche del gruppo di disponibilità

Questo articolo illustra come diagnosticare i timeout di connessione intermittenti segnalati tra le repliche del gruppo di disponibilità.

Sintomi ed effetti dei timeout di connessione intermittente della replica del gruppo di disponibilità

L'esecuzione di query su repliche primarie e secondarie restituisce risultati diversi

I carichi di lavoro di sola lettura che eseguono query su repliche secondarie potrebbero eseguire query sui dati non aggiornati. Se si verificano timeout intermittenti della connessione di replica, le modifiche ai dati nel database di replica primaria non vengono ancora riflesse nel database secondario quando si eseguono query sugli stessi dati. Per altre informazioni, vedere la sezione Latenza dei dati nella replica secondaria.

Gruppo di disponibilità del report di diagnostica non sincronizzato

Il dashboard AlwaysOn in SQL Server Management Studio potrebbe segnalare un gruppo di disponibilità non integro con repliche in uno stato non sincronizzato . È anche possibile osservare che le repliche del report del dashboard AlwaysOn si trovano nello stato Non sincronizzato .

Quando si esaminano i log degli errori di SQL Server di tali repliche, è possibile osservare messaggi come il seguente che indicano che si è verificato un timeout della connessione tra le repliche nel gruppo di disponibilità:

Log degli errori dalla replica primaria

2023-02-15 07:10:55.500 spid43s Always On availability groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Log degli errori dalla replica secondaria

2023-02-15 07:11:03.100 spid31s A connection time-out has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

I problemi di connessione intermittente possono influire sull'idoneità del failover di una replica secondaria

Se si configura il gruppo di disponibilità per il failover automatico e il partner di failover con commit sincrono viene disconnesso in modo intermittente dal server primario, il failover automatico potrebbe non riuscire.

È possibile eseguire una query sys.dm_hadr_database_replia_cluster_states per determinare se il database del gruppo di disponibilità è pronto per il failover in quel momento. Di seguito è riportato un esempio dei risultati se l'endpoint del mirroring è stato arrestato nella replica secondaria:

SELECT drcs.database_name, drcs.is_failover_ready, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ar.replica_id=drcs.replica_id
WHERE ars.role_desc='SECONDARY'

Il failover automatico potrebbe non portare online il gruppo di disponibilità nel ruolo primario nel computer partner di failover se il failover coincide con un timeout della connessione di replica.

Quali sono gli errori di timeout della connessione?

Il valore predefinito è 10 secondi per l'impostazione della replica del gruppo di disponibilità, SESSION_TIMEOUT. Questa impostazione è configurata per ogni replica. Determina per quanto tempo la replica attende di ricevere una risposta dalla replica partner prima che restituisca un timeout della connessione. Se una replica non riceve alcuna risposta dalla replica partner, segnala un timeout di connessione nel log degli errori di Microsoft SQL Server e nel registro applicazioni di Windows. La replica che segnala il timeout tenta immediatamente di riconnettersi e continuerà a provare ogni cinque secondi.

In genere, il timeout della connessione viene rilevato e segnalato da una sola replica. Tuttavia, il timeout della connessione potrebbe essere segnalato da entrambe le repliche contemporaneamente. Esistono versioni diverse di questo messaggio, a seconda che il timeout della connessione si sia verificato usando una connessione stabilita in precedenza o una nuova connessione:

Message 35206 A connection timeout has occurred on a previously established connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Message 35201 A connection timeout has occurred while attempting to establish a connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

La replica partner potrebbe non rilevare un timeout. In caso affermativo, potrebbe segnalare il messaggio 35201 o 35206. In caso contrario, segnala una perdita di connessione a ognuno dei database del gruppo di disponibilità:

Message 35267 Always On Availability Groups connection with primary/secondary database terminated for primary/secondary database '<databasename>' on the availability replica '<replicaname>' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Di seguito è riportato un esempio di report di SQL Server nel log degli errori: se si arresta l'endpoint di mirroring nella replica primaria, la replica secondaria rileva un timeout della connessione e i messaggi 35206 e 35267 vengono segnalati nel log degli errori della replica secondaria:

2023-02-15 07:11:03.100 spid31s A connection timeout has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID:[<replicaid>]. This is an informational message only. No user action is required.

In questo esempio la replica primaria non ha rilevato alcun timeout della connessione perché è ancora in grado di comunicare con il database secondario e ha segnalato il messaggio 35267 per ogni database del gruppo di disponibilità (in questo esempio è presente un solo database, 'agdb'):

2023-02-15 07:10:55.500 spid43s Always On Availability Groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Cause di timeout della connessione di replica

Problema dell'applicazione

SQL Server potrebbe essere occupato per diversi motivi e non esegue il servizio della connessione dell'endpoint di mirroring entro il periodo del gruppo SESSION_TIMEOUT di disponibilità. In questo modo si verifica il timeout della connessione. Ecco alcuni di questi motivi:

• SQL Server riscontra un utilizzo della CPU del 100%. Ciò significa che SQL Server o un'altra applicazione sta guidando la CPU per secondi alla volta.

• SQL Server non produce eventi dell'utilità di pianificazione. I thread di SQL Server sono responsabili della resa dell'utilità di pianificazione (CPU) ad altri thread per completare il lavoro se un thread non restituisce in modo tempestivo.

• SQL Server riscontra l'esaurimento dei thread di lavoro, i problemi di memoria insufficiente o i problemi dell'applicazione che influiscono sulla possibilità di gestire la connessione dell'endpoint di mirroring.

Problema di rete

È quindi necessario raccogliere i log di traccia di rete nelle repliche primarie e secondarie quando viene attivato l'errore. A tale scopo, è possibile esaminare la latenza di rete e eliminare pacchetti.

Come diagnosticare i timeout della connessione di replica

Per il problema dei problemi dell'applicazione che impediscono a SQL Server di gestire la connessione con la replica partner, questa sezione illustra come analizzare i log di SQL Server. Questi suggerimenti consentono di identificare la causa radice dei timeout della connessione di replica. Questa sezione termina con indicazioni più avanzate su come raccogliere tracce di rete quando si verificano i timeout della connessione in modo da poter controllare lo stato della rete.

Valutare la tempistica e la posizione dei timeout della connessione di replica

Esaminare la cronologia, la frequenza e le tendenze dei timeout della connessione. L'uso dei messaggi presenti nel log degli errori di SQL Server è un ottimo modo per eseguire questa operazione. Dove vengono segnalati i timeout della connessione? Vengono segnalate in modo coerente nella replica primaria o secondaria? Quando si sono verificati gli errori? Si sono verificati in una determinata settimana del mese, giorno della settimana o ora del giorno? Altre operazioni di manutenzione o elaborazione batch pianificate corrispondono agli orari in cui vengono osservati i timeout della connessione? Questa valutazione consente di definire l'ambito e correlare i timeout della connessione per identificare la causa radice.

Esaminare la sessione eventi estesa AlwaysOn_health

La AlwaysOn_health sessione eventi estesa è stata migliorata per includere l'evento ucs_connection_setup , che viene attivato quando una replica stabilisce una connessione con la replica partner. Ciò può essere utile per la risoluzione dei problemi di timeout della connessione.

Note

L'evento ucs_connection_setup esteso è stato aggiunto agli aggiornamenti cumulativi più recenti di SQL Server. Per osservare questo evento esteso, è necessario eseguire gli aggiornamenti cumulativi più recenti.

Eseguire query sulle viste di gestione distribuita AlwaysOn (DMV)

È possibile eseguire query su DMV Always On per altre informazioni sullo stato connesso della replica. Questa query segnala solo lo stato connesso e gli eventuali errori associati al timeout della connessione al momento in cui si verificano i problemi. Se i problemi di connessione sono intermittenti, la query potrebbe non acquisire facilmente lo stato disconnesso.

SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id

L'esempio seguente mostra uno stato disconnesso prolungato perché l'endpoint di mirroring nella replica primaria è stato arrestato. Eseguendo una query sulla replica primaria, la DMV Always On può segnalare le repliche primarie e tutte le repliche secondarie (l'endpoint è disabilitato nella replica primaria).

Eseguendo una query sulla replica secondaria, le DMV Always On segnalano solo la replica secondaria.

Esaminare la sessione di eventi estesi Always On

1. Connettersi a ogni replica usando il Esplora oggetti di SQL Server Management Studio (SSMS) e aprire i AlwaysOn_health file di eventi estesi.

2. In SSMS passare a File Open (Apri file>) e quindi selezionare Merge Extended Event Files (Unisci file di eventi estesi).

3. Seleziona il pulsante Aggiungi.

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

5. Premere Ctrl e quindi selezionare i file il cui nome inizia con "AlwaysOn_healthxxx.xel".

6. Selezionare Apri e quindi OK.

   Verrà visualizzata una nuova finestra a schede in SSMS che mostra gli eventi AlwaysOn.

   Lo screenshot seguente mostra i AlwaysOn_health dati della replica secondaria. La prima casella descritta mostra la perdita di connessione dopo l'arresto dell'endpoint nella replica primaria. La seconda casella descritta mostra l'errore di connessione che si verifica alla successiva tentativo di connessione della replica secondaria alla replica primaria.

   

Verificare se gli eventi che non producono causano timeout della connessione

Uno dei motivi più comuni per cui una replica di disponibilità non è in grado di eseguire il servizio della connessione di replica partner è un'utilità di pianificazione senza rendimento. Per altre informazioni sulle utilità di pianificazione senza rendimento, vedere Risoluzione dei problemi relativi alla pianificazione e alla resa di SQL Server.

SQL Server tiene traccia degli eventi dell'utilità di pianificazione che non producono fino a 5-10 secondi. Segnala questi eventi nel TrackingNonYieldingScheduler punto dati nell'output del sp_server_diagnostics query_processing componente.

Per verificare la presenza di eventi che potrebbero causare timeout della connessione di replica, seguire questa procedura:

1. Creare un processo di SQL Agent che registra sp_server_diagnostics ogni cinque secondi.

2. Pianificare questo processo nel server che non segnala il timeout della connessione. Ovvero, se la replica server A segnala il timeout della connessione di replica nel log degli errori, configurare il processo di SQL Agent nella replica partner, Server B. In alternativa, se vengono visualizzati timeout di connessione in entrambe le repliche, creare il processo in entrambe le repliche.

3. Eseguire il file batch seguente per creare un processo che viene eseguito sp_server_diagnostics ogni cinque secondi, accoda l'output a un file di testo e quindi avvia il processo. Il comando nell'esempio sp_server_diagnostics 5 seguente viene eseguito ogni cinque secondi. Non è quindi necessario pianificare l'esecuzione di questo processo ogni cinque secondi, avviare il processo e verrà eseguito fino a quando non viene arrestato, ogni cinque secondi:

   USE [msdb]
   GO
   DECLARE @ReturnCode INT
   SELECT @ReturnCode = 0
   DECLARE @jobId BINARY(16)
   EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Run sp_server_diagnostics',
   @owner_login_name=N'sa', @job_id = @jobId OUTPUT
   /****** Object: Step [Run SP_SERVER_DIAGNOSTICS] Script Date: 2/15/2023 4:20:41 PM ******/
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_SERVER_DIAGNOSTICS',
   @subsystem=N'TSQL',
   @command=N'sp_server_diagnostics 5',
   @database_name=N'master',
   @output_file_name=N'D:\cases\2423\sp_server_diagnostics_output.out',
   @flags=2
   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
   EXEC sp_start_job 'Run sp_server_diagnostics'
   

   Note

   In questi comandi passare @output_file_name a un percorso valido e specificare un nome file.

Analizzare i risultati

Quando viene segnalato un timeout della connessione, prendere nota del timestamp dell'evento di timeout visualizzato nel log degli errori di SQL Server. Per le repliche nell'esempio seguente, SQL19AGN1 segnalava i timeout della connessione di replica. Pertanto, è stato creato un processo di SQL Agent in SQL19AGN2, la replica partner. È stato quindi segnalato un timeout della connessione nel log degli SQL19AGN1 errori alle 07:24:31.

Successivamente, l'output del processo di SQL Agent che esegue sp_server_diagnostics viene controllato in corrispondenza dell'ora segnalata, in particolare esaminando il TrackingNonYieldingScheduler punto dati nell'output del query_processing componente. L'output segnala che un'utilità di pianificazione senza rendimento è stata rilevata (come valore esadecimale diverso da zero) sul server SQL19AGN2 (alle 07:24:33) nel tempo in cui il timeout della connessione di replica è stato segnalato in SQL19AGN1 (alle 07:24:31).

Note

L'output seguente sp_server_diagnostics viene concatenato per visualizzare sia il timestamp query_processing TrackingNonYieldingScheduler che i create_time risultati.

Analizzare un evento dell'utilità di pianificazione senza rendimento

Se è stato verificato dai passaggi di diagnosi precedenti che un evento non restituito ha causato il timeout della connessione di replica:

1. Identificare i carichi di lavoro in esecuzione in SQL Server al momento dell'esecuzione degli eventi non restituiti.

2. Analogamente ai timeout di connessione della replica, cercare le tendenze in questi eventi durante il mese, il giorno o la settimana in cui si verificano.

3. Raccogliere la traccia del monitoraggio delle prestazioni nel sistema in cui è stato rilevato l'evento che non produce.

4. Raccogliere i contatori delle prestazioni chiave per le risorse di sistema, tra cui Processore::% Tempo processore, Memoria::MBytes disponibili, Disco logico::Media lunghezza coda disco e Disco logico::Avg Disk sec/Transfer.

5. Se necessario, aprire un evento imprevisto di supporto di SQL Server per ulteriore assistenza per trovare la causa radice per questi eventi che non producono. Condividere i log raccolti per un'ulteriore analisi.

Raccolta dati avanzata: raccogliere la traccia di rete durante il timeout della connessione

Se la diagnosi precedente dell'applicazione SQL Server non ha generato una causa radice, è necessario controllare la rete. Per un'analisi corretta della rete è necessario raccogliere una traccia di rete che copre il tempo di timeout della connessione.

La procedura seguente avvia una traccia di rete di Windows netsh nelle repliche in cui vengono segnalati i timeout della connessione nei log degli errori di SQL Server. Un'attività evento pianificata di Windows viene attivata quando uno degli errori di connessione di SQL Server viene registrato nel registro applicazioni. L'attività pianificata esegue un comando per arrestare la traccia di netsh rete in modo che i dati di traccia della rete della chiave non siano sovrascritti. Questi passaggi presuppongono anche un percorso di *F:* per i log batch e di traccia. Modificare questo percorso per l'ambiente.

1. Avviare una traccia di rete, come illustrato nel frammento di codice seguente, nelle due repliche in cui si verifica il timeout della connessione:

   netsh trace start capture=yes persistent=yes overwrite=yes maxsize=500 tracefile=f:\trace.etl
   

2. Creare attività pianificate di Windows che arrestino la netsh traccia sugli eventi 35206 o 35267. È possibile creare queste attività in una riga di comando amministrativa:

   schtasks /Create /tn Event35206Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35206] /f /RL HIGHEST
   
   schtasks /Create /tn Event35267Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35267] /f /RL HIGHEST
   

3. Dopo che si verifica l'evento e le tracce di rete vengono arrestate e acquisite, è possibile eliminare le ONEVENT attività:

   PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F
   PS C:\Users\sqladmin> Schtasks /Delete /tn Event35267Task /F
   

L'analisi della traccia di rete non rientra nell'ambito di questo strumento di risoluzione dei problemi. Se non è possibile interpretare la traccia di rete, contattare il team di supporto di Microsoft SQL Server e fornire la traccia insieme ad altri file di log richiesti per l'analisi della causa radice.

Quali altre operazioni è possibile eseguire per attenuare i timeout della connessione?

Il gruppo di disponibilità predefinito, SESSION_TIMEOUT, è configurato per 10 secondi. È possibile ridurre i timeout della connessione modificando la proprietà di replica SESSION_TIMEOUT del gruppo di disponibilità. Questa impostazione è per replica. Modificarlo per la replica primaria e per ogni replica secondaria interessata. Ecco un esempio della sintassi. Il valore predefinito SESSION_TIMEOUT è 10. Pertanto, è possibile usare 15 come valore successivo.

ALTER AVAILABILITY GROUP ag
MODIFY REPLICA ON 'SQL19AGN1' WITH (SESSION_TIMEOUT = 15);