Problemen met onregelmatige verbindingstime-outs tussen replica's van beschikbaarheidsgroepen oplossen

Dit artikel helpt u bij het vaststellen van onregelmatige verbindingstime-outs die worden gerapporteerd tussen replica's van beschikbaarheidsgroepen.

Symptomen en effecten van onregelmatige time-outs voor replicaverbindingen van beschikbaarheidsgroepen

Het uitvoeren van query's op primaire en secundaire replica's retourneert verschillende resultaten

Alleen-lezenworkloads die een query uitvoeren op secundaire replica's, kunnen verouderde gegevens opvragen. Als er onregelmatige time-outs voor replicaverbindingen optreden, worden wijzigingen in gegevens in de primaire replicadatabase nog niet doorgevoerd in de secundaire database wanneer u dezelfde gegevens opvraagt. Zie de sectie Gegevenslatentie op secundaire replica voor meer informatie.

Beschikbaarheidsgroep voor diagnostische rapporten is niet gesynchroniseerd

Het AlwaysOn-dashboard in SQL Server Management Studio rapporteert mogelijk een beschadigde beschikbaarheidsgroep met replica's die de status Niet synchroniseren heeft. Mogelijk ziet u ook dat de replica's van het AlwaysOn-dashboardrapport de status Niet synchroniseren hebben.

Wanneer u de SQL Server-foutenlogboeken van deze replica's bekijkt, ziet u mogelijk berichten zoals het volgende die aangeven dat er een time-out optreedt voor de verbinding tussen de replica's in de beschikbaarheidsgroep:

Foutenlogboek van de primaire replica

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.

Foutenlogboek van de secundaire replica

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.

Onregelmatige verbindingsproblemen kunnen van invloed zijn op de failovergereedheid van een secundaire replica

Als u de beschikbaarheidsgroep configureert voor automatische failover en de synchrone doorvoerfailoverpartner af en toe wordt losgekoppeld van de primaire, kan automatische failover mislukken.

U kunt een query uitvoeren sys.dm_hadr_database_replia_cluster_states om te bepalen of de database van de beschikbaarheidsgroep op dat moment gereed is voor failover. Hier volgt een voorbeeld van de resultaten als het eindpunt voor spiegeling is gestopt op de secundaire replica:

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'

Automatische failover brengt de beschikbaarheidsgroep mogelijk niet online in de primaire rol op de failoverpartnercomputer als failover samenvalt met een time-out van een replicaverbinding.

Wat geven de time-outfouten voor de verbinding aan?

De standaardwaarde is 10 seconden voor de replica-instelling van de beschikbaarheidsgroep. SESSION_TIMEOUT Deze instelling is geconfigureerd voor elke replica. Hiermee wordt bepaald hoe lang de replica wacht op het ontvangen van een reactie van de partnerreplica voordat er een time-out voor de verbinding wordt gerapporteerd. Als een replica geen antwoord krijgt van de partnerreplica, wordt er een time-out voor de verbinding gerapporteerd in het foutenlogboek van Microsoft SQL Server en het Windows-toepassingslogboek. De replica die de time-out rapporteert, probeert onmiddellijk opnieuw verbinding te maken en blijft elke vijf seconden proberen.

Doorgaans wordt de time-out van de verbinding gedetecteerd en gerapporteerd door slechts één replica. De time-out van de verbinding kan echter tegelijkertijd door beide replica's worden gerapporteerd. Er zijn verschillende versies van dit bericht, afhankelijk van of de time-out van de verbinding is opgetreden met behulp van een eerder tot stand gebrachte verbinding of een nieuwe verbinding:

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.

De partnerreplica detecteert mogelijk geen time-out. Als dit het geval is, kan het bericht 35201 of 35206 rapporteren. Als dat niet het probleem is, wordt er een verbindingsverlies gerapporteerd aan elk van de databases van de beschikbaarheidsgroep:

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.

Hier volgt een voorbeeld van wat SQL Server rapporteert aan het foutenlogboek: als u het eindpunt voor spiegeling op de primaire replica stopt, detecteert de secundaire replica een time-out voor de verbinding en worden berichten 35206 en 35267 gerapporteerd in het foutenlogboek van de secundaire replica:

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 dit voorbeeld detecteerde de primaire replica geen time-out voor de verbinding omdat deze nog steeds kon communiceren met de secundaire, en het bericht 35267 gerapporteerd voor elke beschikbaarheidsgroepdatabase (in dit voorbeeld is er slechts één 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.

Oorzaken van time-outs voor replicaverbindingen

Toepassingsprobleem

SQL Server kan om verschillende redenen bezet zijn en biedt geen service voor de eindpuntverbinding voor spiegeling binnen de periode van de beschikbaarheidsgroep SESSION_TIMEOUT . Hierdoor treedt er een time-out op voor de verbinding. Enkele van deze redenen zijn:

• SQL Server ondervindt 100 procent CPU-gebruik. Dit betekent dat SQL Server of een andere toepassing de CPU gedurende enkele seconden tegelijk aan het stimuleren is.

• SQL Server ondervindt niet-opleverende scheduler-gebeurtenissen. SQL Server-threads zijn verantwoordelijk voor het leveren van de scheduler (CPU) aan andere threads om hun werk te voltooien als een thread niet tijdig oplevert.

• SQL Server ondervindt uitputting van werkrolthreads, problemen met onvoldoende geheugen of problemen met toepassingen die van invloed zijn op de mogelijkheid om de verbinding met het spiegelingseindpunt te onderhouden.

Netwerkprobleem

Hiervoor moet u netwerktraceringslogboeken verzamelen op de primaire en secundaire replica's wanneer de fout wordt geactiveerd. Hiervoor kunt u de netwerklatentie en verwijderde pakketten onderzoeken.

Time-outs voor replicaverbindingen diagnosticeren

Voor het probleem van toepassingsproblemen waardoor SQL Server de verbinding met de partnerreplica niet kan onderhouden, wordt in deze sectie uitgelegd hoe u de SQL Server-logboeken analyseert. Met deze tips kunt u de hoofdoorzaak van time-outs van de replicaverbinding identificeren. Deze sectie eindigt met meer geavanceerde richtlijnen over het verzamelen van netwerktraceringen wanneer de time-outs voor de verbinding optreden, zodat u de netwerkstatus kunt controleren.

Timing en locatie van time-outs voor replicaverbindingen evalueren

Bekijk de geschiedenis, de frequentie en trends van de time-outs van de verbinding. Het gebruik van de berichten die u in het SQL Server-foutenlogboek vindt, is een uitstekende manier om dit te doen. Waar worden de time-outs van de verbinding gerapporteerd? Worden ze consistent gerapporteerd op de primaire of secundaire replica? Wanneer zijn de fouten opgetreden? Zijn ze in een bepaalde week van de maand, de dag van de week of het tijdstip van de dag opgetreden? Komen andere geplande onderhouds- of batchverwerking overeen met de tijdstippen waarop de time-outs van de verbinding worden waargenomen? Met deze evaluatie kunt u de verbindingstime-outs bepalen en correleren om de hoofdoorzaak te identificeren.

Bekijk de uitgebreide gebeurtenissessie van AlwaysOn_health

De AlwaysOn_health uitgebreide gebeurtenissessie is uitgebreid met de ucs_connection_setup gebeurtenis, die wordt geactiveerd wanneer een replica verbinding maakt met de partnerreplica. Dit kan handig zijn bij het oplossen van time-outproblemen voor verbindingen.

Query's uitvoeren op AlwaysOn Distributed Management Views (DMV's)

U kunt query's uitvoeren op AlwaysOn-DMV's voor meer informatie over de verbonden status van de replica. Deze query rapporteert alleen de verbonden status en eventuele fouten die zijn gekoppeld aan de time-out van de verbinding op het moment dat de problemen optreden. Als de verbindingsproblemen af en toe optreden, kan de query de niet-verbonden status mogelijk niet eenvoudig vastleggen.

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

In het volgende voorbeeld ziet u een langdurige niet-verbonden status omdat het eindpunt voor spiegeling op de primaire replica is gestopt. Door een query uit te voeren op de primaire replica, kan de AlwaysOn DMV rapporteren over de primaire en alle secundaire replica's (het eindpunt is uitgeschakeld op de primaire replica).

Door query's uit te voeren op de secundaire replica, rapporteert de AlwaysOn DMV's alleen op de secundaire replica.

De uitgebreide gebeurtenissessie van AlwaysOn controleren

1. Maak verbinding met elke replica met behulp van SSMS (SQL Server Management Studio) Objectverkenner en open de AlwaysOn_health uitgebreide gebeurtenisbestanden.

2. Ga in SSMS naar Bestand>openen en selecteer Uitgebreide gebeurtenisbestanden samenvoegen.

3. Selecteer de knop Toevoegen.

4. Navigeer in het dialoogvenster Bestand openen naar de bestanden in de map SQL Server \LOG .

5. Druk op Control en selecteer vervolgens de bestanden waarvan de naam begint met 'AlwaysOn_healthxxx.xel'.

6. Selecteer Openen en selecteer vervolgens OK.

   U ziet nu een nieuw venster met tabbladen in SSMS waarin de AlwaysOn-gebeurtenissen worden weergegeven.

   In de volgende schermopname ziet u de AlwaysOn_health gegevens van de secundaire replica. In het eerste overzichtsvak ziet u het verbindingsverlies nadat het eindpunt op de primaire replica is gestopt. In het tweede overzichtsvak ziet u de verbindingsfout die optreedt wanneer de secundaire replica de volgende keer verbinding probeert te maken met de primaire replica.

   

Controleer of niet-gegenereerde gebeurtenissen time-outs voor verbindingen veroorzaken

Een van de meest voorkomende redenen waarom een beschikbaarheidsreplica de partnerreplicaverbinding niet kan onderhouden, is een niet-opleverende planner. Zie Problemen met sql Server-planning en -opbrengst oplossen voor meer informatie over niet-rendementsplanners.

SQL Server houdt niet-rendementende scheduler-gebeurtenissen bij die zo kort zijn als 5 tot 10 seconden. Deze gebeurtenissen worden gerapporteerd in het TrackingNonYieldingScheduler gegevenspunt in de uitvoer van het sp_server_diagnostics query_processing onderdeel.

Voer de volgende stappen uit om te controleren op niet-gegenereerde gebeurtenissen die time-outs voor replicaverbindingen kunnen veroorzaken:

1. Maak een SQL Agent-taak die elke vijf seconden wordt geregistreerd sp_server_diagnostics .

2. Plan deze taak op de server die de time-out van de verbinding niet rapporteert. Als Server A-replica de time-out voor de replicaverbinding rapporteert in het foutenlogboek, stelt u de SQL Agent-taak in op de partnerreplica, Server B. Als u verbindingstime-outs op beide replica's ziet, maakt u de taak op beide replica's.

3. Voer het volgende batchbestand uit om een taak te maken die om de vijf seconden wordt uitgevoerd sp_server_diagnostics , de uitvoer aan een tekstbestand toevoegt en de taak vervolgens start. De opdracht in het volgende voorbeeld sp_server_diagnostics 5 wordt elke vijf seconden uitgevoerd. U hoeft deze taak dus niet te plannen om elke vijf seconden uit te voeren, alleen de taak te starten en deze wordt uitgevoerd totdat deze is gestopt, om de vijf seconden:

   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'
   

   Notitie

   In deze opdrachten gaat u naar @output_file_name een geldig pad en geeft u een bestandsnaam op.

De resultaten analyseren

Wanneer er een time-out voor de verbinding wordt gerapporteerd, noteert u de tijdstempel van de time-outgebeurtenis die wordt weergegeven in het foutenlogboek van SQL Server. Voor de replica's in het volgende voorbeeld SQL19AGN1 werd de time-outs van de replicaverbinding gerapporteerd. Daarom is er een SQL Agent-taak gemaakt op SQL19AGN2, de partnerreplica. Vervolgens is er een time-out voor de verbinding gerapporteerd in het SQL19AGN1 foutenlogboek om 07:24:31.

Vervolgens wordt de uitvoer van de SQL Agent-taak die wordt uitgevoerd sp_server_diagnostics rond de gerapporteerde tijd gecontroleerd, met name door het TrackingNonYieldingScheduler gegevenspunt in de query_processing onderdeeluitvoer te controleren. De uitvoer rapporteert dat een niet-rendementsplanner is bijgehouden (als een niet-nul hexadecimale waarde) op de server SQL19AGN2 (om 07:24:33) rond het tijdstip waarop de time-out van de replicaverbinding is gerapporteerd op SQL19AGN1 (om 07:24:31).

Een niet-rendementende scheduler-gebeurtenis onderzoeken

Als u hebt gecontroleerd op basis van de eerdere diagnosestappen dat een niet-opleverende gebeurtenis de time-out van de replicaverbinding heeft veroorzaakt:

1. Identificeer de workloads die worden uitgevoerd in SQL Server op het moment dat de niet-opleverende gebeurtenissen worden uitgevoerd.

2. Net als bij time-outs voor replicaverbindingen zoekt u naar trends in deze gebeurtenissen tijdens de maand, dag of week die ze hebben plaatsgevonden.

3. Verzamel tracering van prestatiemeter op het systeem waarop de niet-opbrengstgebeurtenis is gedetecteerd.

4. Verzamel belangrijke prestatiemeteritems voor systeembronnen, waaronder Processor::% Processortijd, Geheugen::Beschikbare MBytes, Logische schijf::Avg Disk Queue Length en Logical Disk::Avg Disk sec/Transfer.

5. Als dit nodig is, opent u een SQL Server-ondersteuningsincident voor verdere hulp bij het vinden van de hoofdoorzaak voor deze niet-opleverende gebeurtenissen. Deel de logboeken die u hebt verzameld voor verdere analyse.

Geavanceerde gegevensverzameling: netwerktracering verzamelen tijdens time-out van verbinding

Als de vorige diagnose van de SQL Server-toepassing geen hoofdoorzaak oplevert, moet u het netwerk controleren. Voor een geslaagde analyse van het netwerk moet u een netwerktracering verzamelen die betrekking heeft op de tijd van de time-out van de verbinding.

Met de volgende procedure wordt een Windows-netwerktracering netsh gestart op de replica's waarop de time-outs voor de verbinding worden gerapporteerd in de SQL Server-foutenlogboeken. Een geplande Windows-gebeurtenistaak wordt geactiveerd wanneer een van de SQL Server-verbindingsfouten wordt vastgelegd in het toepassingslogboek. De geplande taak voert een opdracht uit om de netsh netwerktracering te stoppen, zodat de sleutelnetwerktraceringsgegevens niet worden overschreven. Bij deze stappen wordt ook uitgegaan van een pad van *F:* voor de batch- en traceringslogboeken. Pas dit pad aan uw omgeving aan.

1. Start een netwerktracering, zoals wordt weergegeven in het volgende codefragment, op de twee replica's waarop de time-outs voor de verbinding optreden:

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

2. Maak geplande Windows-taken die de netsh tracering stoppen op gebeurtenissen 35206 of 35267. U kunt deze taken maken op een beheeropdrachtregel:

   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. Nadat de gebeurtenis zich voordoet en de netwerktraceringen zijn gestopt en vastgelegd, kunt u de ONEVENT taken verwijderen:

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

Analyse van de netwerktracering valt buiten het bereik van deze probleemoplosser. Als u de netwerktracering niet kunt interpreteren, neemt u contact op met het ondersteuningsteam van Microsoft SQL Server en geeft u de tracering op samen met andere aangevraagde logboekbestanden voor hoofdoorzaakanalyse.

Wat kan ik nog meer doen om de time-outs van de verbinding te beperken?

De standaard beschikbaarheidsgroep, SESSION_TIMEOUTwordt 10 seconden geconfigureerd. Mogelijk kunt u de time-outs van de verbinding beperken door de replica-eigenschap SESSION_TIMEOUT van de beschikbaarheidsgroep aan te passen. Deze instelling is per replica. Pas deze aan voor de primaire en elke betrokken secundaire replica. Hier volgt een voorbeeld van de syntaxis. De standaardwaarde SESSION_TIMEOUT is 10. Daarom kunt u 15 gebruiken als de volgende waarde.

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