Problembehandlung bei zeitweiligen Verbindungstimeouts zwischen Verfügbarkeitsgruppenreplikaten

In diesem Artikel können Sie zeitweilige Verbindungstimeouts diagnostizieren, die zwischen Verfügbarkeitsgruppenreplikaten gemeldet werden.

Symptome und Auswirkungen von zeitweiligen Verbindungstimeouts für Verfügbarkeitsgruppenreplikate

Das Abfragen von primären und sekundären Replikaten gibt unterschiedliche Ergebnisse zurück.

Schreibgeschützte Workloads, die sekundäre Replikate abfragen, können veraltete Daten abfragen. Wenn zeitweilige Replikatverbindungstimeouts auftreten, werden Änderungen an Daten in der primären Replikatdatenbank noch nicht in der sekundären Datenbank widergespiegelt, wenn Sie dieselben Daten abfragen. Weitere Informationen finden Sie im Abschnitt Datenlatenz auf sekundären Replikaten .

Verfügbarkeitsgruppe des Diagnoseberichts nicht synchronisiert

Die Always On Dashboard in SQL Server Management Studio meldet möglicherweise eine fehlerhafte Verfügbarkeitsgruppe mit Replikaten, die sich im Zustand Nicht synchronisiert befinden. Sie können auch beobachten, dass sich die Always On Dashboard Berichtsreplikaten im Zustand Nicht synchronisiert befinden.

Wenn Sie die SQL Server Fehlerprotokolle dieser Replikate überprüfen, können Sie Meldungen wie die folgenden sehen, die darauf hinweisen, dass zwischen den Replikaten in der Verfügbarkeitsgruppe ein Verbindungstimeout aufgetreten ist:

Fehlerprotokoll vom primären Replikat

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.

Fehlerprotokoll vom sekundären Replikat

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.

Zeitweilige Verbindungsprobleme können sich auf die Failoverbereitschaft eines sekundären Replikats auswirken.

Wenn Sie die Verfügbarkeitsgruppe für automatisches Failover konfigurieren und der Partner für synchrone Commitfailover zeitweilig vom primären Failover getrennt ist, ist das automatische Failover möglicherweise nicht erfolgreich.

Sie können abfragen sys.dm_hadr_database_replia_cluster_states , um zu bestimmen, ob die Verfügbarkeitsgruppendatenbank zu diesem Zeitpunkt failoverbereit ist. Hier sehen Sie ein Beispiel für die Ergebnisse, wenn der Spiegelungsendpunkt auf dem sekundären Replikat beendet wurde:

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'

Beim automatischen Failover wird die Verfügbarkeitsgruppe möglicherweise nicht in der primären Rolle auf dem Failoverpartnercomputer online geschaltet, wenn das Failover mit einem Timeout für die Replikatverbindung zusammenfällt.

Was deuten die Fehler aufgrund des Verbindungstimeouts auf?

Der Standardwert für die Verfügbarkeitsgruppenreplikateinstellung SESSION_TIMEOUTbeträgt 10 Sekunden. Diese Einstellung wird für jedes Replikat konfiguriert. Es bestimmt, wie lange das Replikat wartet, um eine Antwort von seinem Partnerreplikat zu erhalten, bevor es ein Verbindungstimeout meldet. Wenn ein Replikat keine Antwort vom Partnerreplikat erhält, meldet es ein Verbindungstimeout im Microsoft SQL Server-Fehlerprotokoll und im Windows-Anwendungsprotokoll. Das Replikat, das das Timeout meldet, versucht sofort, die Verbindung wiederherzustellen, und versucht es weiterhin alle fünf Sekunden.

In der Regel wird das Verbindungstimeout nur von einem Replikat erkannt und gemeldet. Das Verbindungstimeout kann jedoch von beiden Replikaten gleichzeitig gemeldet werden. Es gibt unterschiedliche Versionen dieser Nachricht, je nachdem, ob das Verbindungstimeout über eine zuvor hergestellte Verbindung oder eine neue Verbindung aufgetreten ist:

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.

Das Partnerreplikat erkennt möglicherweise kein Timeout. Wenn dies der Fall ist, wird möglicherweise die Meldung 35201 oder 35206 gemeldet. Ist dies nicht der Fall, wird ein Verbindungsverlust für jede Verfügbarkeitsgruppendatenbank gemeldet:

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 sehen Sie ein Beispiel dafür, was SQL Server dem Fehlerprotokoll meldet: Wenn Sie den Spiegelungsendpunkt auf dem primären Replikat beenden, erkennt das sekundäre Replikat ein Verbindungstimeout, und die Meldungen 35206 und 35267 werden im Fehlerprotokoll des sekundären Replikats gemeldet:

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 diesem Beispiel hat das primäre Replikat kein Verbindungstimeout erkannt, da es weiterhin mit dem sekundären Replikat kommunizieren konnte, und es hat die Meldung 35267 für jede Verfügbarkeitsgruppendatenbank gemeldet (in diesem Beispiel gibt es nur eine Datenbank, "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.

Ursachen für Replikatverbindungstimeouts

Anwendungsproblem

SQL Server kann aus verschiedenen Gründen ausgelastet sein und die Spiegelungsendpunktverbindung nicht innerhalb des Verfügbarkeitsgruppenzeitraums SESSION_TIMEOUT warten. Dies führt zu einem Verbindungstimeout. Einige der folgenden Gründe sind:

• SQL Server hat eine CPU-Auslastung von 100 Prozent. Dies bedeutet, dass SQL Server oder eine andere Anwendung die CPU für Sekunden gleichzeitig antreibt.

• SQL Server führt zu nicht liefernden Schedulerereignissen. SQL Server Threads sind dafür verantwortlich, den Scheduler (CPU) an andere Threads zu liefern, um ihre Arbeit abzuschließen, wenn ein Thread nicht rechtzeitig ergibt.

• SQL Server hat eine Überlastung des Arbeitsthreads, Probleme mit nicht genügend Arbeitsspeicher oder Anwendungsprobleme, die sich auf die Fähigkeit auswirken, die Verbindung mit dem Spiegelungsendpunkt zu bedienen.

Netzwerkproblem

Dies erfordert, dass Sie Netzwerkablaufverfolgungsprotokolle auf den primären und sekundären Replikaten sammeln, wenn der Fehler ausgelöst wird. Dazu können Sie die Netzwerklatenz und verworfene Pakete untersuchen.

Diagnostizieren von Replikatverbindungstimeouts

Bei Anwendungsproblemen, die SQL Server daran hindern, die Verbindung mit dem Partnerreplikat zu warten, wird in diesem Abschnitt erläutert, wie die SQL Server Protokolle analysiert werden. Diese Tipps können Ihnen helfen, die Grundursache der Replikatverbindungstimeouts zu identifizieren. Dieser Abschnitt endet mit einer ausführlicheren Anleitung zum Erfassen von Netzwerkablaufverfolgungen, wenn es zu Verbindungstimeouts kommt, damit Sie die Netzwerk-status überprüfen können.

Bewerten von Zeit- und Ortszeitüberschreitungen für Replikatverbindungen

Überprüfen Sie den Verlauf, die Häufigkeit und die Trends der Verbindungstimeouts. Die Verwendung der Meldungen, die Sie im SQL Server Fehlerprotokoll finden, ist eine hervorragende Möglichkeit, dies zu tun. Wo werden die Verbindungstimeouts gemeldet? Werden sie konsistent auf dem primären oder sekundären Replikat gemeldet? Wann sind die Fehler aufgetreten? Sind sie in einer bestimmten Woche des Monats, wochentags oder tageszeit aufgetreten? Entspricht eine andere geplante Wartung oder Batchverarbeitung den Zeiten, zu denen die Verbindungstimeouts beobachtet werden? Diese Bewertung kann Ihnen helfen, die Verbindungstimeouts festzulegen und zu korrelieren, um die Grundursache zu identifizieren.

Überprüfen der AlwaysOn_health erweiterten Ereignissitzung

Die AlwaysOn_health erweiterte Ereignissitzung wurde erweitert, um das ucs_connection_setup -Ereignis einzuschließen, das ausgelöst wird, wenn ein Replikat eine Verbindung mit seinem Partnerreplikat herstellt. Dies kann bei der Behandlung von Verbindungstimeoutproblemen hilfreich sein.

Hinweis

Das ucs_connection_setup erweiterte Ereignis wurde dem neuesten SQL Server kumulativen Updates hinzugefügt. Sie müssen die neuesten kumulativen Updates ausführen, um dieses erweiterte Ereignis zu beobachten.

Abfragen Always On Verteilte Verwaltungssichten (DMVs)

Sie können Always On DMVs abfragen, um weitere Informationen zum verbundenen Zustand des Replikats zu erhalten. Diese Abfrage meldet nur den Verbindungsstatus und alle Fehler, die mit dem Verbindungstimeout zum Zeitpunkt des Auftretens der Probleme verbunden sind. Wenn die Verbindungsprobleme zeitweilig auftreten, erfasst die Abfrage den Zustand "Getrennt" möglicherweise nicht einfach.

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

Das folgende Beispiel zeigt einen dauerhaft getrennten Zustand, da der Spiegelungsendpunkt auf dem primären Replikat beendet wurde. Durch Abfragen des primären Replikats kann die Always On DMV Berichte über das primäre und alle sekundären Replikate erstellen (der Endpunkt ist auf dem primären Replikat deaktiviert).

Durch Abfragen des sekundären Replikats meldet der Always On DMVs nur das sekundäre Replikat.

Überprüfen der Always On erweiterten Ereignissitzung

1. Stellen Sie mithilfe von SQL Server Management Studio (SSMS) eine Verbindung mit jedem Replikat Objekt-Explorer her, und öffnen Sie die erweiterten AlwaysOn_health Ereignisdateien.

2. Wechseln Sie in SSMS zu Datei>öffnen, und wählen Sie dann Erweiterte Ereignisdateien zusammenführen aus.

3. Klicken Sie auf die Schaltfläche Hinzufügen.

4. Navigieren Sie im Dialogfeld Datei öffnen zu den Dateien im Verzeichnis SQL Server \LOG.

5. Drücken Sie CTRL, und wählen Sie dann die Dateien aus, deren Name mit "AlwaysOn_healthxxx.xel" beginnt.

6. Wählen Sie Öffnen und dann OK aus.

   In SSMS sollte ein neues Fenster im Registerkartenformat angezeigt werden, in dem die AlwaysOn-Ereignisse angezeigt werden.

   Der folgende Screenshot zeigt die AlwaysOn_health Daten aus dem sekundären Replikat. Das erste umrissene Feld zeigt den Verbindungsverlust an, nachdem der Endpunkt auf dem primären Replikat beendet wurde. Das zweite beschriebene Feld zeigt den Verbindungsfehler an, der auftritt, wenn das sekundäre Replikat das nächste Mal versucht, eine Verbindung mit dem primären Replikat herzustellen.

   

Überprüfen Sie, ob ereignisse, die keine Ergebnisse liefern, Verbindungstimeouts verursachen.

Einer der häufigsten Gründe dafür, dass ein Verfügbarkeitsreplikat die Partnerreplikatverbindung nicht bedienen kann, ist ein nicht liefernder Scheduler. Weitere Informationen zu nicht liefernden Planern finden Sie unter Problembehandlung SQL Server Planung und Ausbeute.

SQL Server verfolgt nicht liefernde Schedulerereignisse nach, die nur 5 bis 10 Sekunden lang sind. Es meldet diese Ereignisse im TrackingNonYieldingScheduler Datenpunkt in der sp_server_diagnostics query_processing Komponentenausgabe.

Führen Sie die folgenden Schritte aus, um nach Ereignissen zu suchen, die nicht liefern, die zu Replikatverbindungstimeouts führen können:

1. Erstellen Sie einen SQL-Agent-Auftrag, der alle fünf Sekunden aufzeichnet sp_server_diagnostics .

2. Planen Sie diesen Auftrag auf dem Server, der das Verbindungstimeout nicht meldet. Das heißt, wenn Server A-Replikat das Replikatverbindungstimeout im Fehlerprotokoll meldet, richten Sie den SQL-Agent-Auftrag auf dem Partnerreplikat Server B ein. Alternativ können Sie den Auftrag auf beiden Replikaten erstellen, wenn Verbindungstimeouts auf beiden Replikaten auftreten.

3. Führen Sie die folgende Batchdatei aus, um einen Auftrag zu erstellen, der alle fünf Sekunden ausgeführt wird sp_server_diagnostics , fügt die Ausgabe an eine Textdatei an und startet dann den Auftrag. Der Befehl im folgenden Beispiel sp_server_diagnostics 5 wird alle fünf Sekunden ausgeführt. Es ist also nicht erforderlich, diesen Auftrag so zu planen, dass er alle fünf Sekunden ausgeführt wird. Starten Sie einfach den Auftrag, und er wird alle fünf Sekunden ausgeführt, bis er beendet wird:

   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'
   

   Hinweis

   Ändern Sie @output_file_name in diesen Befehlen in einen gültigen Pfad, und geben Sie einen Dateinamen an.

Analysieren der Ergebnisse

Wenn ein Verbindungstimeout gemeldet wird, notieren Sie sich den Zeitstempel des Timeoutereignisses, das im SQL Server Fehlerprotokoll angezeigt wird. Für die Replikate im folgenden Beispiel SQL19AGN1 hat die Replikatverbindungstimeouts gemeldet. Daher wurde ein SQL-Agent-Auftrag auf SQL19AGN2erstellt, dem Partnerreplikat. Anschließend wurde im SQL19AGN1 Fehlerprotokoll um 07:24:31 Uhr ein Verbindungstimeout gemeldet.

Als Nächstes wird die Ausgabe des SQL-Agent-Auftrags, der sp_server_diagnostics ausgeführt wird, um die gemeldete Zeit zu überprüfen. Dabei wird insbesondere der TrackingNonYieldingScheduler Datenpunkt in der query_processing Komponentenausgabe überprüft. Die Ausgabe meldet, dass ein Nicht-Ergebnisplaner (als Hexadezimalwert ungleich Null) auf SQL19AGN2 dem Server (um 07:24:33) um den Zeitpunkt nachverfolgt wurde, zu dem das Timeout für die Replikatverbindung am SQL19AGN1 gemeldet wurde (um 07:24:31).

Hinweis

Die folgende sp_server_diagnostics Ausgabe wird verkettet, um sowohl die create_time Ergebnisse (Zeitstempel) als query_processing TrackingNonYieldingScheduler auch anzuzeigen.

Untersuchen eines nicht liefernden Schedulerereignisses

Wenn Sie anhand der früheren Diagnoseschritte überprüft haben, dass ein ereignisunabhängiges Ereignis das Replikatverbindungstimeout verursacht hat:

1. Identifizieren Sie die Workloads, die in SQL Server ausgeführt werden, wenn die ereignisse ausgeführt werden, die keine Ergebnisse liefern.

2. Suchen Sie ähnlich wie bei den Replikatverbindungstimeouts nach Trends bei diesen Ereignissen während des Monats, tages oder der Woche, in dem sie auftreten.

3. Erfassen Sie die Leistungsüberwachungsablaufverfolgung auf dem System, auf dem das ereignisunabhängige Ereignis erkannt wurde.

4. Sammeln Sie wichtige Leistungsindikatoren für Systemressourcen, einschließlich Prozessor::% Prozessorzeit, Arbeitsspeicher::Verfügbare MB, LogischerDatenträger::Durchschn. Datenträgerwarteschlangenlänge und Logischer Datenträger::Durchschn. Datenträgers s/Übertragung.

5. Wenn dies erforderlich ist, öffnen Sie einen SQL Server Supportvorfall, um weitere Unterstützung bei der Suche nach der Grundursache für diese ereignisse zu erhalten, die nicht zur Folge haben. Geben Sie die gesammelten Protokolle zur weiteren Analyse weiter.

Erweiterte Datensammlung: Erfassen der Netzwerkablaufverfolgung während eines Verbindungstimeouts

Wenn die vorherige Diagnose der SQL Server Anwendung keine Grundursache ergeben hat, sollten Sie das Netzwerk überprüfen. Eine erfolgreiche Analyse des Netzwerks erfordert, dass Sie eine Netzwerkablaufverfolgung erfassen, die die Zeit des Verbindungstimeouts abdeckt.

Mit dem folgenden Verfahren wird eine Windows-Netzwerkablaufverfolgung netsh für die Replikate gestartet, bei denen die Verbindungstimeouts in den SQL Server Fehlerprotokollen gemeldet werden. Eine Geplante Windows-Ereignisaufgabe wird ausgelöst, wenn einer der SQL Server Verbindungsfehler im Anwendungsprotokoll aufgezeichnet wird. Der geplante Task führt einen Befehl aus, um die netsh Netzwerkablaufverfolgung zu beenden, damit die wichtigsten Netzwerkablaufverfolgungsdaten nicht überschrieben werden. Bei diesen Schritten wird auch der Pfad *F:* für den Batch und die Ablaufverfolgungsprotokolle vorausgesetzt. Passen Sie diesen Pfad an Ihre Umgebung an.

1. Starten Sie eine Netzwerkablaufverfolgung, wie im folgenden Codeausschnitt gezeigt, auf den beiden Replikaten, auf denen die Verbindungstimeouts auftreten:

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

2. Erstellen Sie geplante Windows-Aufgaben, die die netsh Ablaufverfolgung für Ereignisse 35206 oder 35267 beenden. Sie können diese Aufgaben an einer administrativen Befehlszeile erstellen:

   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. Nachdem das Ereignis eingetreten ist und die Netzwerkablaufverfolgungen beendet und erfasst wurden, können Sie die ONEVENT Aufgaben löschen:

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

Die Analyse der Netzwerkablaufverfolgung liegt außerhalb des Bereichs dieser Problembehandlung. Wenn Sie die Netzwerkablaufverfolgung nicht interpretieren können, wenden Sie sich an das Microsoft SQL Server-Supportteam, und stellen Sie die Ablaufverfolgung zusammen mit anderen angeforderten Protokolldateien zur Ursachenanalyse bereit.

Was kann ich sonst noch tun, um die Verbindungstimeouts zu verringern?

Die Standardverfügbarkeitsgruppe ( SESSION_TIMEOUT) ist für 10 Sekunden konfiguriert. Möglicherweise können Sie die Verbindungstimeouts verringern, indem Sie die Verfügbarkeitsgruppenreplikateigenschaft SESSION_TIMEOUT anpassen. Diese Einstellung gilt pro Replikat. Passen Sie es für das primäre und jedes betroffene sekundäre Replikat an. Hier sehen Sie ein Beispiel für die Syntax. Der Standardwert SESSION_TIMEOUT ist 10. Daher können Sie 15 als nächsten Wert verwenden.

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