Risolvere i problemi di timeout di connessione intermittenti tra le repliche del gruppo di disponibilità

Sommario

In un gruppo di disponibilità AlwaysOn SQL Server, si verifica un timeout della connessione quando una replica non riceve una risposta dalla replica partner entro il SESSION_TIMEOUT periodo. Il log degli errori SQL Server segnala questi timeout intermittenti come errori 35201, 35206 e 35267. Questi timeout possono lasciare il gruppo di disponibilità in uno stato non sincronizzato . Le cause più comuni includono un problema dell'applicazione, ad esempio un utilizzo elevato della CPU o un'utilità di pianificazione che non produce o un problema di rete, ad esempio la latenza o pacchetti eliminati. Questo articolo illustra come interpretare gli errori di timeout della connessione e diagnosticare la causa radice usando i log degli errori di SQL Server, le viste a gestione dinamica Always On (DMV), gli eventi estesi e le tracce di rete. Viene inoltre illustrato come attenuare i timeout, incluso come modificare l'impostazione della replica SESSION_TIMEOUT del gruppo di disponibilità.

Sintomi ed effetti dei timeout intermittenti della connessione

Le repliche primarie e secondarie restituiscono risultati diversi

I carichi di lavoro di sola lettura che interrogano le repliche secondarie potrebbero interrogare dati non aggiornati. Se si verificano timeout di connessione di replica intermittenti, 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.

Il gruppo di disponibilità del report di diagnostica non è sincronizzato

Il dashboard Always On in SQL Server Management Studio (SSMS) potrebbe segnalare un gruppo di disponibilità non integro con repliche nello stato Non sincronizzato.

Schermata del dashboard di Always On che mostra le repliche nello stato

Quando si esaminano i log degli errori di SQL Server per tali repliche, è possibile che vengano visualizzati messaggi come il seguente che indicano un timeout di connessione tra le repliche nel gruppo di disponibilità.

Ecco il 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.

Ecco il log degli errori della 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 prontezza al failover di una replica secondaria

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

È possibile eseguire una query sys.dm_hadr_database_replica_cluster_states per verificare se il database del gruppo di disponibilità è pronto per il failover. Ecco un esempio dei risultati se l'endpoint del mirroring viene 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'

Schermata dell'endpoint di mirroring arrestato sulla replica secondaria.

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.

Significato degli errori di timeout della connessione

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

In genere, una sola replica rileva e segnala il timeout della connessione. Tuttavia, entrambe le repliche potrebbero segnalare il timeout della connessione contemporaneamente. Esistono versioni diverse del messaggio, a seconda che il timeout della connessione si sia verificato in una connessione stabilita in precedenza o in 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.

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 un timeout della connessione perché è ancora in grado di comunicare con la replica secondaria. È stato segnalato il messaggio 35267 per ogni database nel gruppo di disponibilità. In questo caso è 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 di connessione della replica

Problema dell'applicazione

SQL Server potrebbe essere troppo occupato per gestire la connessione dell'endpoint di mirroring entro il periodo del gruppo SESSION_TIMEOUT di disponibilità, causando il timeout della connessione. Ecco alcuni motivi comuni:

  • SQL Server riscontra un utilizzo della CPU del 100%. Questa condizione significa che SQL Server o un'altra applicazione sta consumando tutta la CPU disponibile per secondi alla volta.

  • SQL Server non produce eventi dell'utilità di pianificazione. I thread di SQL Server sono responsabili di cedere lo scheduler (CPU) ad altri thread affinché possano completare il proprio lavoro. Se un thread non restituisce in modo tempestivo, può ritardare la connessione dell'endpoint di mirroring e causare un timeout.

  • 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

Raccogliere le tracce di rete nelle repliche primarie e secondarie quando si verifica l'errore e quindi esaminarle per individuare la latenza di rete e eliminare pacchetti.

Diagnosticare i timeout di connessione della replica

Questa sezione illustra come analizzare i log di SQL Server per diagnosticare i problemi dell'applicazione che impediscono SQL Server di gestire la connessione con la replica partner. Questi suggerimenti possono aiutarti a identificare la causa principale dei timeout di connessione della replica. Termina con indicazioni più avanzate sulla raccolta di tracce di rete quando si verificano i timeout della connessione in modo da poter controllare lo stato della rete.

Valutare quando e dove si verificano i timeout di connessione della replica

Esaminare la cronologia, la frequenza e le tendenze dei timeout della connessione. I messaggi nel log degli errori di SQL Server sono una buona fonte per questa revisione. 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 di connessione per identificare la causa radice.

Esamina la sessione di eventi estesi 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. Questo evento può essere utile quando si risolvono i problemi di timeout della connessione.

Nota

L'evento ucs_connection_setup esteso è disponibile a partire da SQL Server 2019 CU15. Per altre informazioni, vedere Configurare gli eventi estesi per i gruppi di disponibilità.

Eseguire query sulle viste di gestione dinamica Always On (DMV)

È possibile interrogare le DMV Always On per ulteriori informazioni sullo stato di connessione 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 di disconnessione prolungato perché l'endpoint di mirroring sulla replica primaria è stato arrestato. Quando si esegue 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).

Screenshot che mostra uno stato di disconnessione prolungato perché l'endpoint del mirroring sulla replica primaria è stato arrestato.

Quando si interroga la replica secondaria, la DMV Always On restituisce informazioni solo sulla replica secondaria.

Schermata di uno stato di disconnessione prolungato perché l'endpoint di mirroring nella replica secondaria è stato arrestato.

Esaminare la sessione di eventi estesi Always On

  1. Connettersi a ogni replica usando Esplora oggetti SSMS e aprire i AlwaysOn_health file degli eventi estesi.

  2. In SSMS passare a File>Apri e quindi selezionare 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. Selezionare e tenere premuto CTRL e quindi selezionare i file il cui nome inizia con AlwaysOn_healthxxx.xel.

  6. Selezionare Apri e quindi OK.

    In SSMS viene visualizzata una nuova finestra a schede che mostra gli eventi AlwaysOn.

    Lo screenshot seguente mostra i AlwaysOn_health dati della replica secondaria. La prima casella evidenziata mostra la perdita di connessione dopo l'arresto dell'endpoint sulla 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.

    Schermata dei dati di AlwaysOn_health dalla replica secondaria.

Verificare se gli eventi non restituitivi causano timeout della connessione

Uno dei motivi più comuni per cui una replica di disponibilità non riesce a gestire la connessione della replica partner è un'utilità di pianificazione che non cede il controllo. Per altre informazioni sulle utilità di pianificazione senza rendimento, vedere Risoluzione dei problemi relativi alla pianificazione e alla resa di SQL Server.

SQL Server rileva gli eventi in cui lo scheduler non cede il controllo, anche se durano appena 5-10 secondi. Riporta questi eventi nel punto dati TrackingNonYieldingScheduler nell'output del componente sp_server_diagnostics query_processing

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. Pianifica questa attività sul server che non segnala il timeout di connessione. Ovvero, si supponga che la replica Server A segnala il timeout della connessione nel log degli errori. In tal caso, configura il processo di SQL Server Agent sulla replica partner, Server B. In alternativa, se si verificano timeout di connessione in entrambe le repliche, crea il processo su entrambe le repliche.

  3. Eseguire lo script T-SQL 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. Nell'esempio seguente il sp_server_diagnostics 5 comando viene eseguito ogni cinque secondi. Non è quindi necessario pianificare l'esecuzione di questo processo ogni cinque secondi. È sufficiente avviare il processo e viene 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 si verifica un timeout della connessione, prendere nota del timestamp dell'evento di timeout visualizzato nel log degli errori SQL Server. Per le repliche dell'esempio seguente, SQL19AGN1 segnala i timeout di connessione della replica. Pertanto, si crea un processo di SQL Agent in SQL19AGN2, la replica partner. Viene quindi segnalato un timeout della connessione nel log degli SQL19AGN1 errori alle 07:24:31.

Schermata del timeout di connessione segnalato nel registro degli errori di SQL19AGN1.

Quindi, controlla l'output del processo di SQL Server Agent che esegue sp_server_diagnostics intorno all'ora segnalata. In particolare, esamina il TrackingNonYieldingScheduler dato nell'output del componente query_processing. L'output riporta che una utilità di pianificazione non yielding è stata monitorata (come valore esadecimale diverso da zero) sul server SQL19AGN2 (alle 07:24:33) nel periodo in cui è stato segnalato il timeout della connessione della replica su SQL19AGN1 (alle 07:24:31).

Nota

Il seguente risultato sp_server_diagnostics viene concatenato per mostrare sia il create_time (timestamp) sia i risultati query_processing TrackingNonYieldingScheduler.

Schermata dell'output concatenato di sp_server_diagnostics che mostra create_time e i risultati di query_processing.

Analizzare un evento dell'utilità di pianificazione senza rendimento

Se si verifica dai precedenti passaggi di diagnosi che un evento che non cede il controllo ha causato il timeout della connessione della replica, seguire questi passaggi.

  1. Identificare i carichi di lavoro in esecuzione in SQL Server al momento in cui si verificano gli eventi non restituiti.

  2. Analogamente ai timeout della connessione di replica, individuare eventuali tendenze in questi eventi nel corso del mese, del giorno o della 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 Processor::% Tempo processore, Memoria::Available MBytes, Logical Disk::Avg Disk Queue Length e Logical Disk::Avg Disk sec/Transfer.

  5. Se necessario, aprire un ticket di supporto SQL Server per ulteriore assistenza nell'individuazione della causa principale di questi eventi non yielding. Condividere i log raccolti per un'ulteriore analisi.

Raccogliere una traccia di rete durante un timeout della connessione

Se la diagnosi precedente dell'applicazione SQL Server non produce una causa radice, controllare la rete. Per analizzare correttamente la rete, è necessario raccogliere una traccia di rete che copre il tempo di timeout della connessione.

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

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

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

  2. Creare stoptrace.bat. È possibile creare questo file al prompt dei comandi amministrativo.

    echo netsh trace stop > F:\stoptrace.bat

  3. Crea attività pianificate di Windows che interrompono la traccia netsh al verificarsi degli eventi 35206 o 35267. È possibile creare queste attività al prompt dei comandi amministrativi.

    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

  4. Dopo che l'evento si è verificato e le tracce di rete sono state interrotte e acquisite, eliminare le attività ONEVENT.

    schtasks /Delete /tn Event35206Task /F
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.

Attenuare i timeout di connessione

Potrebbe essere possibile attenuare i timeout della connessione modificando la proprietà SESSION_TIMEOUT della replica del gruppo di disponibilità. Questa impostazione è per replica, quindi modificarla per la replica primaria e per ogni replica secondaria interessata. Il valore predefinito è 10 secondi, quindi è possibile provare 15 secondi come valore successivo. Ecco un esempio della sintassi.

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

Contenuti correlati

  • Last updated on