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

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

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

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

I carichi di lavoro di sola lettura che eseguono query sulle repliche secondarie potrebbero eseguire query su dati non aggiornati. Se si verificano timeout di connessione alla replica intermittente, le modifiche apportate 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 Always On in SQL Server Management Studio potrebbe segnalare uno stato di non sincronizzazione di un gruppo di disponibilità non integro con repliche. È anche possibile osservare che le repliche del report del dashboard Always On sono nello stato Non sincronizzante.

Quando si esaminano i log degli errori SQL Server di tali repliche, è possibile osservare messaggi come i seguenti che indicano che si è verificato un timeout di 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 sulla preparazione 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 query sys.dm_hadr_database_replia_cluster_states per determinare se il database del gruppo di disponibilità è pronto per il failover in quel momento. Ecco un esempio dei risultati se l'endpoint di 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 alla replica.

Cosa indicano 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 segnali 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 log delle 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 contemporaneamente da entrambe le repliche. 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 ogni 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.

Ecco un esempio di ciò che SQL Server segnala al log degli errori: se si arresta l'endpoint di mirroring nella replica primaria, la replica secondaria rileva un timeout di 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 di connessione perché poteva comunque 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 dei timeout della connessione alla replica

Problema dell'applicazione

SQL Server potrebbe essere occupato per diversi motivi e non funziona la connessione dell'endpoint di mirroring entro il periodo del gruppo SESSION_TIMEOUT di disponibilità. Ciò causa il timeout della connessione. Alcuni di questi motivi sono:

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

• SQL Server si verificano eventi dell'utilità di pianificazione non cedenti. SQL Server thread sono responsabili della resa dell'utilità di pianificazione (CPU) ad altri thread per completare il lavoro se un thread non restituisce tempestivamente.

• SQL Server si verifica l'esaurimento del thread di lavoro, problemi di memoria insufficiente o problemi di applicazione che influiscono sulla capacità di gestire la connessione dell'endpoint di mirroring.

Problema di rete

A tale scopo, è 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 i pacchetti eliminati.

Come diagnosticare i timeout di connessione della replica

Per il problema dei problemi dell'applicazione che impediscono 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 alla replica. Questa sezione termina con indicazioni più avanzate su come raccogliere le tracce di rete quando si verificano i timeout di connessione in modo da poter controllare lo stato della rete.

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

Esaminare la cronologia, la frequenza e le tendenze dei timeout di connessione. L'uso dei messaggi presenti nel log degli errori SQL Server è un ottimo modo per eseguire questa operazione. Dove vengono segnalati i timeout di connessione? Vengono segnalati 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 pianificata o elaborazione batch corrispondono agli orari in cui vengono osservati i timeout della connessione? Questa valutazione consente di definire l'ambito e correlare i timeout di connessione per identificare la causa radice.

Esaminare la sessione di eventi estesa AlwaysOn_health

La AlwaysOn_health sessione di 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.

Nota

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

Query Always On viste a gestione distribuita (DMV)

È possibile eseguire query Always On DMV per altre informazioni sullo stato connesso della replica. Questa query segnala solo lo stato connesso e gli eventuali errori associati al timeout della connessione nel 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, il Always On DMV può segnalare le repliche primarie e secondarie (l'endpoint è disabilitato nella replica primaria).

Eseguendo una query sulla replica secondaria, il Always On DMV segnala solo la replica secondaria.

Esaminare la sessione di evento estesa Always On

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

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

3. Selezionare 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 illustrata mostra la perdita di connessione dopo l'arresto dell'endpoint nella replica primaria. La seconda casella illustrata mostra l'errore di connessione che si verifica la volta successiva in cui la replica secondaria tenta di connettersi alla replica primaria.

   

Verificare se gli eventi non restituiti causano timeout della connessione

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

SQL Server tiene traccia degli eventi dell'utilità di pianificazione che non hanno una durata compresa tra 5 e 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 alla 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 il server A replica segnala il timeout della connessione alla 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. Pertanto, non è necessario pianificare l'esecuzione di questo processo ogni cinque secondi, basta 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'
   

   Nota

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

Analizzare i risultati

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

Successivamente, l'output del processo di SQL Agent eseguito sp_server_diagnostics viene controllato intorno all'ora segnalata, esaminando in particolare 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) nel server SQL19AGN2 (alle 07:24:33) nel momento in cui il timeout della connessione alla replica è stato segnalato in SQL19AGN1 (alle 07:24:31).

Nota

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

Analizzare un evento dell'utilità di pianificazione senza rendimento

Se dai passaggi di diagnosi precedenti è stato verificato che un evento non cedente ha causato il timeout della connessione alla 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 alla 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 non yielding.

4. Raccogliere i contatori delle prestazioni chiave per le risorse di sistema, tra cui Processore::% Tempo processore, Memoria::MByte disponibili, Disco logico::Lunghezza media coda disco e Disco logico::Disco medio sec/Trasferimento.

5. Se necessario, aprire un evento imprevisto di supporto SQL Server per ulteriore assistenza nella ricerca della causa radice per questi eventi non restituiti. 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 prodotto una causa radice, è necessario controllare la rete. Per un'analisi corretta della rete è necessario raccogliere una traccia di rete che copra il tempo del timeout della connessione.

La procedura seguente avvia una traccia di rete di Windows netsh nelle repliche in cui vengono segnalati i timeout di connessione nei log degli errori di SQL Server. Un'attività evento pianificata di Windows viene attivata quando nel registro applicazioni viene registrato uno degli errori di connessione SQL Server. L'attività pianificata esegue un comando per arrestare la netsh traccia di rete in modo che i dati di traccia della rete chiave non vengano sovrascritti. Questi passaggi presuppongono anche un percorso di *F:* per il batch e i log 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 verificano i 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 arrestano 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 l'evento si è verificato e le tracce di rete sono state 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.

Cos'altro è possibile fare per attenuare i timeout di connessione?

Il gruppo di disponibilità predefinito, SESSION_TIMEOUT, viene configurato per 10 secondi. È possibile attenuare i timeout di connessione modificando la proprietà della replica SESSION_TIMEOUT del gruppo di disponibilità. Questa impostazione è per replica. Regolarlo 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);