Problemen met failover van AlwaysOn-beschikbaarheidsgroepen oplossen

Overzicht

Dit artikel bevat stappen voor probleemoplossing om te bepalen waarom uw AlwaysOn-beschikbaarheidsgroep een failover heeft uitgevoerd in SQL Server. Hierin wordt uitgelegd hoe u statusgebeurtenissen in het logboek van het Windows-cluster kunt identificeren, veelvoorkomende oorzaken kunt diagnosticeren, zoals clusterstatusgebeurtenissen, lease-time-outs, time-outs van statuscontroles en statusproblemen met SQL Server, en voor elk daarvan oplossingen kunt toepassen.

Hoe AlwaysOn-statusproblemen en failover van invloed zijn op workloads

AlwaysOn implementeert robuuste statuscontrole via verschillende mechanismen om de status van het Microsoft SQL Server-exemplaar te garanderen dat als host fungeert voor de primaire replica, het onderliggende cluster en de systeemstatus. De productieworkload wordt tijdelijk onderbroken wanneer een Windows-cluster of AlwaysOn-statusprobleem wordt geïdentificeerd.

Wanneer een statusconditie wordt gedetecteerd, vindt doorgaans de volgende reeks gebeurtenissen plaats. In deze probleemoplosser worden statusgebeurtenissen genoemd met verwijzing naar de volgende gebeurtenissen:

• Replica's en databases van beschikbaarheidsgroepen gaan van de primaire rol naar de resolverol.

• Databases van beschikbaarheidsgroepen worden offline geplaatst en zijn niet meer toegankelijk.

• Windows Cluster markeert de geclusterde resource van de beschikbaarheidsgroep als mislukt.

• Windows-cluster probeert de rol van de beschikbaarheidsgroep weer online te zetten (op de oorspronkelijke of automatische failoverpartnerreplica).

• De rol van de beschikbaarheidsgroep wordt succesvol online gebracht als door Always On en Windows Cluster-statuscontrole wordt vastgesteld dat de status ervan in orde is.

Als dit lukt, worden de replica's en databases van de beschikbaarheidsgroep overgestapt op de primaire rol en zijn de beschikbaarheidsgroepdatabases online en toegankelijk voor uw toepassing.

Toepassingen hebben geen toegang tot de databases van de beschikbaarheidsgroep

Wanneer het systeem een statusprobleem detecteert, gaan de replica en databases van de beschikbaarheidsgroep over naar de status Resolving en gaan de databases van de beschikbaarheidsgroep offline. Nadat de replica online is gekomen in de primaire rol (op de oorspronkelijke replicaserver of de replicaserver van de failoverpartner), worden de replica en databases weer online geplaatst. Hoewel de replica en databases worden hersteld en offline zijn, mislukken alle toepassingen die toegang proberen te krijgen tot die beschikbaarheidsgroepdatabases en wordt het bericht 'Fout 983' gegenereerd: Unable to access availability database.... Als SQL Server is geconfigureerd voor het vastleggen van mislukte aanmeldingspogingen, wordt deze fout ook vastgelegd in het Microsoft SQL Server foutenlogboek.

Logon Error: 983, Severity: 14, State: 1.

Logon Unable to access availability database '<databasename>' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

De periode waarin de beschikbaarheidsgroep zich in de rol Oplossen bevindt voordat deze weer online komt in de primaire rol, duurt doorgaans slechts een paar seconden of zelfs minder dan een seconde.

Statusgebeurtenissen of failover van een Always On-beschikbaarheidsgroep identificeren en diagnosticeren

Always On-gezondheidstrends vaststellen

U kunt één AlwaysOn-statusgebeurtenis onderzoeken, of er is mogelijk een recente of doorlopende trend van statusproblemen die de productie af en toe onderbreken. Met de volgende vragen kunt u recente wijzigingen in uw productieomgeving nader identificeren en in verband brengen met deze gezondheidsproblemen:

• Wanneer is de trend van de status van AlwaysOn of het cluster begonnen?
• Vinden de gezondheidsgebeurtenissen op een bepaalde dag plaats?
• Treden de gezondheidsgebeurtenissen op een bepaald tijdstip van de dag op?
• Vinden de gezondheidsevenementen plaats op een bepaalde dag of week van de maand?

Als u een trend signaleert, controleer dan het ingeplande onderhoud van het systeem (het hostsysteem in een virtuele omgeving), ETL-batches en andere taken die mogelijk samenvallen met deze statusmeldingen. Als het systeem een virtuele machine is, onderzoekt u het hostsysteem op wijzigingen die mogelijk zijn geïntroduceerd op het moment van de storingen.

Houd rekening met actieve ad-hoc productieworkloads die kunnen correleren met de tijd van de statusproblemen (bijvoorbeeld wanneer gebruikers zich voor het eerst aanmelden bij het systeem of nadat gebruikers na de lunch terugkeren).

Het clusterlogboek controleren

Het Windows-clusterlogboek is het meest uitgebreide logboek om het type Always On- of clusterstatusgebeurtenis en de gedetecteerde statusconditie vast te stellen die de gebeurtenis heeft veroorzaakt. Voer de volgende stappen uit om het clusterlogboek te genereren en te openen:

Gebruik Windows PowerShell om het Windows-clusterlogboek te genereren op het clusterknooppunt dat als host fungeert voor de primaire replica op het moment van de status gebeurtenis. Voer bijvoorbeeld de volgende cmdlet uit in een PowerShell-venster met verhoogde bevoegdheid met de sql19agn1 naam van de SQL Server-server:

get-clusterlog -Node sql19agn1 -UseLocalTime     

Zoek de gezondheidsgebeurtenis in het clusterlogboek

AlwaysOn maakt gebruik van verschillende mechanismen voor statuscontrole om de status van de beschikbaarheidsgroep te bewaken. Naast een Windows-clusterstatusgebeurtenis (waarbij windows-cluster een statusprobleem tussen de clusterknooppunten detecteert), heeft AlwaysOn vier verschillende soorten statuscontroles:

• De SQL Server-service wordt niet uitgevoerd
• Een time-out van een SQL Server-lease
• time-out van een SQL Server-gezondheidscontrole
• Een intern statusprobleem in SQL Server

U kunt elk van deze Always On-specifieke statusgebeurtenissen vinden door in het clusterlogboek naar de tekenreeks [hadrag] Resource Alive result 0 te zoeken. In het clusterlogboek wordt deze tekenreeks opgeslagen wanneer een van deze gebeurtenissen wordt gedetecteerd. Bijvoorbeeld:

00001334.00002ef4::2019/06/24-18:24:36.153 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0.

U kunt een hulpprogramma gebruiken om alle statusevenementen in het clusterlogboek te vinden, zodat u een overzichtsrapport van AlwaysOn-statusproblemen kunt genereren. Dit rapport kan u helpen chronologische trends te identificeren en te bepalen of een bepaalde AlwaysOn-status terugkerend is. In de volgende schermafbeelding ziet u hoe u een teksteditor (Kladblok++, in dit geval) gebruikt om alle regels in het clusterlogboek te vinden die de [hadrag] Resource Alive result 0 tekenreeks bevatten:

Identificeer en los het statusprobleem op dat de failover heeft veroorzaakt

Als u statusproblemen in het clusterlogboek van de primaire replica wilt identificeren, vergelijkt u deze met de problemen die in de volgende secties worden beschreven. Veelvoorkomende redenen voor ag-failover zijn:

• Status van clustergezondheid
• SQL Server-service is uitgevallen (een Always On-statusgebeurtenis)
• Time-out voor lease (een AlwaysOn-status gebeurtenis)
• Time-out voor statuscontrole (een AlwaysOn-status gebeurtenis)
• Status van SQL Server (een Always On-statusgebeurtenis)

Gebeurtenissen met betrekking tot de clusterstatus

Microsoft Windows Cluster bewaakt de status van de lidservers in het cluster. Als er een statusprobleem wordt gedetecteerd, wordt een clusterlidserver uit het cluster verwijderd. De clusterresources, inclusief de rol van de beschikbaarheidsgroep die wordt gehost op de verwijderde clusterlidserver, gaan naar de replica van de failoverpartner van de beschikbaarheidsgroep als deze is geconfigureerd voor automatische failover.

Symptomen

Hier is een voorbeeld van een statusgebeurtenis van het cluster in het clusterlogboek. Als u deze wilt vinden, zoekt u naar Lost quorum of Cluster service has terminated, omdat een van beide aanwezig kan zijn tijdens de rolwijziging of failover van de beschikbaarheidsgroep.

00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: Lost quorum (1)
00000fe4.00001628::2022/12/15-14:26:02.654 WARN [QUORUM] Node 1: goingAway: 0, core.IsServiceShutdown: 0
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925)
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [NETFT] Cluster Service preterminate succeeded.
00000fe4.00001628::2022/12/15-14:26:02.654 WARN lost quorum (status = 5925), executing OnStop
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM]: Shutting down, so unloading the cluster database.
00000fe4.00001628::2022/12/15-14:26:02.654 INFO [DM] Shutting down, so unloading the cluster database (waitForLock: false).
000019cc.000019d0::2022/12/15-14:26:02.654 WARN [RHS] Cluster service has terminated. Cluster.Service.Running.Event got signaled.

U kunt deze gebeurtenis ook identificeren door in het Windows systeemlogboek te zoeken.

Critical SQL19AGN1.CSSSQL 1135 Microsoft-Windows-FailoverClusterin Node Mgr NT AUTHORITY\SYSTEM Cluster node 'SQL19AGN2' was removed from the active failover cluster membership. The Cluster service on this node may have stopped. This could also be due to the node having lost communication with other active nodes in the failover cluster. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapters on this node. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.
Critical SQL19AGN1.CSSSQL 1177 Microsoft-Windows-FailoverClusterin Quorum Manager NT AUTHORITY\SYSTEM The Cluster service is shutting down because quorum was lost. This could be due to the loss of network connectivity between some or all nodes in the cluster, or a failover of the witness disk. Run the Validate a Configuration wizard to check your network configuration. If the condition persists, check for hardware or software errors related to the network adapter. Also check for failures in any other network components to which the node is connected such as hubs, switches, or bridges.

Een clusterstatus-gebeurtenis diagnosticeren

De fouten in het Windows-gebeurtenislogboek (gebeurtenissen 1135 en 1177) suggereren dat de netwerkverbinding een oorzaak van de gebeurtenis is. Deze toestand is de meest voorkomende oorzaak van het detecteren van een probleem met de clustergezondheid. In het volgende voorbeeld ziet u dat andere clusterlidservers niet konden communiceren met deze server die als host fungeert voor de primaire replica van de beschikbaarheidsgroep en dat dit probleem het verwijderen van het clusterknooppunt van het cluster heeft geactiveerd:

00000fe4.00001edc::2022/12/14-22:44:36.870 INFO [NODE] Node 1: New join with n3: stage: 'Attempt Initial Connection' status (10060) reason: 'Failed to connect to remote endpoint <endpoint address>'
00000fe4.00001620::2022/12/15-14:26:02.050 INFO [IM] got event: Remote endpoint <endpoint address> unreachable from <endpoint address>
00000fe4.00001620::2022/12/15-14:26:02.050 WARN [NDP] All routes for route (virtual) local <local address> to remote <remote address> are down
00000fe4.0000179c::2022/12/15-14:26:02.053 WARN [NODE] Node 1: Connection to Node 2 is broken. Reason GracefulClose(1226)' because of 'channel to remote endpoint <endpoint address> is closed'

U kunt in het clusterlogboek zoeken naar bewijs van een verbindingsfout met het knooppunt. Zoek vanaf de locatie in het clusterlogboek waar u hebt gevonden Lost quorum, achterwaarts naar tekenreeksen zoals Failed to connect to remote endpoint, unreachableen is broken.

Solution

Zorg ervoor dat de clusterstatuscontrole geschikt is voor de hostomgeving. Zie het overzicht van Windows Server-failoverclusters - SQL Server op Virtuele Azure-machines voor meer informatie over SQL Server AlwaysOn-beschikbaarheidsgroepen die worden gehost in Microsoft Azure.

Neem indien nodig contact op met Microsoft Windows ondersteuning voor hoge beschikbaarheid om een ondersteuningsincident te openen.

De SQL Server-service is uitgevallen: een Always On-gezondheidsgebeurtenis

AlwaysOn-statuscontrole kan detecteren of de SQL Server-service die als host fungeert voor de primaire replica van de beschikbaarheidsgroep, niet meer wordt uitgevoerd.

Symptomen

Hier volgt een voorbeeld van het clusterlogboekrapport voor de beschikbaarheidsgroepsrol 'ag' die een fout aangeeft omdat QueryServiceStatusEx een proces-id 0is geretourneerd:

00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] QueryServiceStatusEx returned a process id 0
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] SQL server service is not alive
00001898.0000185c::2023/02/27-13:27:41.121 ERR [RES] SQL Server Availability Group <ag>: [hadrag] Resource Alive result 0.
00001898.0000185c::2023/02/27-13:27:41.121 WARN [RHS] Resource ag IsAlive has indicated failure.

Afsluitende gebeurtenissen van SQL Service diagnosticeren

Controleer het Windows-gebeurtenislogboek en het SQL Server-foutenlogboek voor een onverwacht afsluiten van SQL Server.

Als SQL Server is afgesloten door een systeemafschakeling of een administratieve afsluiting, ziet u de volgende vermelding in het foutenlogboek van SQL Server:

2023-03-10 09:38:46.73 spid9s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.

In het Windows systeemlogboek ziet u de volgende foutvermelding:

Information 3/10/2023 9:41:06 AM Service Control Manager 7036 None The SQL Server (MSSQLSERVER) service entered the stopped state.

In het Windows-systeemlogboek wordt de volgende foutvermelding weergegeven als SQL Server onverwacht wordt afgesloten:

Error 3/10/2023 8:37:46 AM Service Control Manager 7034 None The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).

Controleer het einde van het SQL Server-foutenlogboek op aanwijzingen. Als het foutenlogboek plotseling eindigt, betekent deze voorwaarde dat er een geforceerde afsluiting is opgetreden. Als SQL Server bijvoorbeeld is beëindigd met behulp van Taakbeheer, bevat het SQL Server-foutrapport geen informatie over interne problemen die er mogelijk toe hebben geleid dat het proces werd afgesloten.

Solution

Zorg ervoor dat geautoriseerde database- en systeembeheerders toegang hebben tot het systeem om onverwachte beëindigingen van de SQL Server-service te minimaliseren. Nadat u de gebeurtenislogboeken hebt bekeken, onderzoekt u waarom een service onverwacht moest worden beëindigd.

Als een probleem met de interne sql Server-status ertoe heeft geleid dat SQL Server onverwacht werd beëindigd, kunnen er aanwijzingen zijn van een mogelijke fatale uitzondering (inclusief een diagnostisch geheugendumpbestand dat wordt gegenereerd) aan het einde van het SQL-foutenlogboek. Bekijk de aanwijzingen en voer de benodigde actie uit. Als u een dumpbestand vindt, kunt u contact opnemen met de ondersteuning van Microsoft SQL Server en het SQL Server-foutenlogboek en de inhoud van het dumpbestand opgeven voor verder onderzoek.

Time-out voor lease: een AlwaysOn-status-gebeurtenis

AlwaysOn maakt gebruik van een 'lease'-mechanisme om de status van de computer waarop SQL Server is geïnstalleerd te controleren. De standaardtime-out voor de leaseperiode is 20 seconden.

Symptomen

Hier ziet u een voorbeelduitvoer van een lease-time-out van Always On uit het clusterlogboek. U kunt deze tekenreeksen doorzoeken om een lease-time-out in het clusterlogboek te vinden.

00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Availability Group lease is no longer valid 
00001a0c.00001c5c::2023/01/04-15:36:54.762 ERR [RES] SQL Server Availability Group : [hadrag] Resource Alive result 0. 
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:35:57.0, 98.068572, 509227008.000000, 0.000395, 0.000350 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:7.0, 12.314941, 451817472.000000, 0.000278, 0.000266 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:17.0, 17.270742, 416096256.000000, 0.000376, 0.000292 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:27.0, 38.399895, 416301056.000000, 0.000446, 0.000304 00001a0c.00001c5c::2023/01/04-15:36:54.762 WARN [RES] SQL Server Availability Group: [hadrag] 1/4/2023 15:36:37.0, 100.000000, 417517568.000000, 0.001292, 0.000666

Zie voor meer informatie over de time-out van de lease de sectie Het leasemechanisme in Werking en richtlijnen voor lease-, cluster- en statuscontrole-time-outs voor Always On-beschikbaarheidsgroepen.

Lease-time-outgebeurtenissen van Always On diagnosticeren en oplossen

Twee hoofdoorzaken kunnen een lease-time-out veroorzaken:

• SQL Server-geheugendump: wanneer SQL Server bepaalde interne statusgebeurtenissen detecteert, zoals een toegangsfout, een assertie of een scheduler-deadlock, genereert het een diagnostisch dumpbestand (.mdmp) in de map SQL Server \LOG. Het proces voor het genereren van een geheugendump onderbreekt de uitvoering van SQL Server gedurende een korte periode. In die periode kan het leasemechanisme een gebrek aan servicerespons en triggeractie detecteren. Zie Impact van dumpgeneratie voor meer informatie.

• Een systeembreed prestatieprobleem: een lease-time-out wijst niet noodzakelijkerwijs op een gezondheidsprobleem met SQL Server. In plaats daarvan kan het duiden op een systeembreed statusprobleem dat ook van invloed is op de status van de SQL Server-server.

  • Hoog CPU-gebruik op het systeem (dicht bij 100%).
  • Onvoldoende geheugen: weinig virtueel geheugen en/of een van de processen wordt uitgepaginad.
  • WSFC gaat offline vanwege quorumverlies.
  • Throttling van de VM beïnvloedt de prestaties en leidt ertoe dat de lease verloopt.

Solution

Zie MSSQLSERVER_19407 voor gedetailleerde stappen voor probleemoplossing. Dit zijn de twee meest voorkomende problemen:

1. Diagnose van SQL Server-dumpbestand

SQL Server kan een probleem met de interne status detecteren, zoals een toegangsschending, assertie of impasseplanners. In dit geval genereert het programma een minidumpbestand (.mdmp) in de map SQL Server \LOG van het SQL Server-proces voor diagnose. Het SQL Server-proces wordt enkele seconden geblokkeerd terwijl het minidumpbestand naar de schijf wordt geschreven. Gedurende deze tijd hebben alle threads binnen het SQL Server-proces een geblokkeerde status, waaronder de leasethread die wordt bewaakt door AlwaysOn-statuscontrole. Daarom kan Always On een lease-time-out detecteren.

**Dump thread - spid = 0, EC = 0x0000000000000000
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   11/02/14 21:21:10 spid 1920
*
* Deadlocked Schedulers
*
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
Stack Signature for the dump is 0x00000000000002BA
Error: 19407, Severity: 16, State: 1.
The lease between availability group 'ag' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Als u dit probleem wilt oplossen, bekijkt u de diagnose van het geheugendumpbestand voor de hoofdoorzaak. Neem contact op met de ondersteuning van Microsoft SQL Server om het SQL Server-foutenlogboek en de inhoud van het dumpbestand te verstrekken voor verder onderzoek.

2. Hoog CPU-gebruik of ander probleem met systeemprestaties

Een lease time-out duidt op een prestatieprobleem dat van invloed is op het hele systeem, inclusief SQL Server. Om het systeemprobleem te diagnosticeren, rapporteert de statusdiagnose van Always On prestatiemonitorgegevens in het clusterlogboek en neemt deze de lease-time-outgebeurtenis op. De prestatiegegevens beslaan ongeveer 50 seconden voorafgaand aan de time-outgebeurtenis van de lease en bevatten informatie over CPU-gebruik, vrij geheugen en schijflatentie.

Hier is een voorbeeld van de gerapporteerde prestatiegegevens waaruit blijkt dat er een lease-time-out is in het clusterlogboek. In deze voorbeelduitvoer kan een hoog algemeen CPU-gebruik verband houden met de lease-time-out.

00000f90.000015c0::2020/08/07-14:16:41.378 WARN [RES] SQL Server Availability Group: [hadrag] Lease timeout detected, logging perf counter data collected so far
00000f90.000015c0::2020/08/07-14:16:41.382 WARN [RES] SQL Server Availability Group: [hadrag] Date/Time, Processor time(%), Available memory(bytes), Avg disk read(secs), Avg disk write(secs)
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:20.0, 83.266073, 31700828160.000000, 0.018094, 0.015752
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:30.0, 93.653224, 31697063936.000000, 0.038590, 0.026897
00000f90.000015c0::2020/08/07-14:16:41.431 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:40.0, 94.270691, 31696265216.000000, 0.166000, 0.038962
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:15:50.0, 90.272016, 31695409152.000000, 0.215141, 0.106084
00000f90.000015c0::2020/08/07-14:16:41.434 WARN [RES] SQL Server Availability Group: [hadrag] 8/7/2020 14:16:1.0, 99.991336, 31695892480.000000, 0.046983, 0.035440

Als de prestatiegegevens een hoog CPU-gebruik, een geheugentekort of een hoge schijflatentie laten zien op het moment van een leasetime-out, begin dan met het verzamelen van Performance Monitor-gegevens gedurende een volledige dag op de primaire replica om deze symptomen te onderzoeken. Door prestatiemetergegevens gedurende een langere periode vast te leggen, kunt u de basislijn- en piekwaarden voor deze resources beter identificeren en wijzigingen in deze resources bewaken wanneer er een time-out voor de lease optreedt. Wanneer u deze gegevens verzamelt, is het raadzaam na te gaan of er bepaalde geplande of ad-hocworkloads in SQL Server zijn die samenvallen met het moment waarop deze resourceproblemen en gezondheidsgebeurtenissen optreden.

U moet ook tellers vastleggen die hetzelfde systeemresourcegebruik rapporteren, waaronder het volgende:

• Processor Information::% Processor Time
• Memory::Available MBytes
• Logical Disk::Avg. Disk sec/Read
• Logical Disk::Avg. Disk sec/Write
• Logical Disk::Avg. Disk Read Queue Length
• Logical Disk::Avg. Disk Write Queue Length
• MSSQLServer:SQL Statistics::Batch Requests/sec

Time-out voor statuscontrole: een AlwaysOn-status gebeurtenis

AlwaysOn maakt gebruik van een statuscontrolemechanisme om de status van SQL Server te bewaken en de mogelijkheid voor clienttoepassingen om verbinding te maken.

Symptomen

Wanneer een replica van een beschikbaarheidsgroep overgaat naar de primaire rol, brengt AlwaysOn-statuscontrole een lokale ODBC-verbinding tot stand met het SQL Server-exemplaar. Hoewel AlwaysOn is verbonden en bewaakt, activeert Always On een time-out voor statuscontrole als SQL Server niet reageert via de ODBC-verbinding binnen de periode die u hebt ingesteld voor de time-out van de statuscontrole van de beschikbaarheidsgroep (de standaardwaarde is 30 seconden). In dit geval gaat de beschikbaarheidsgroep over van de primaire rol naar de rol Oplossen en start de failover, als deze is geconfigureerd om dit te doen.

Zie de sectie Time-outs voor statuscontrole in Mechanica en richtlijnen voor time-outs voor lease, cluster en statuscontrole voor AlwaysOn-beschikbaarheidsgroepen voor meer informatie over time-outs voor statuscontrole.

Hier volgt een time-out voor de statuscontrole van AlwaysOn, zoals gerapporteerd in het clusterlogboek:

0000211c.00002d70::2021/02/24-02:50:01.890 WARN [RES] SQL Server Availability Group: [hadrag] Failed to retrieve data column. Return code -1
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <AG>: [hadrag] Resource Alive result 0.
0000211c.00002594::2021/02/24-02:50:02.453 WARN [RHS] Resource AG IsAlive has indicated failure.
00001278.00002ed8::2021/02/24-02:50:02.453 INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'AG', gen(0) result 1/0.

Time-outs van alwaysOn-statuscontrole vaststellen en oplossen

In de volgende sectie kunt u de SQL Server-logboeken controleren op 'breadcrumb'-gebeurtenissen die u mogelijk aantreft en die samenhangen met gedetecteerde en gerapporteerde time-outs van Always On-statuscontroles. De logboeken die u hier bekijkt, bevatten het clusterlogboek (waarbij de time-out van de statuscontrole is bevestigd), de system_health uitgebreide gebeurtenislogboeken en SQL Server foutenlogboeken (beide gevonden in de map SQL Server \LOG) en het Windows gebeurtenislogboek van het systeem. Gebruik deze en andere logboeken om te zoeken naar correlerende gebeurtenissen die u kunnen helpen de oorzaak van de time-out van de statuscontrole te bepalen.

1. Controleren op niet-rendementende scheduler-gebeurtenissen

De time-out van de Always On-statuscontrole wordt vaak veroorzaakt door 'non-yielding'-gebeurtenissen in SQL Server. Wanneer SQL Server detecteert dat een thread niet heeft opgeleverd op een planner, wordt gerapporteerd dat er een niet-rendementende scheduler-gebeurtenis is opgetreden. Als u andere taken ziet op dezelfde planner die geen CPU-tijd ontvangt, is deze voorwaarde het primaire teken van een niet-rendementende planner. Dit gedrag kan leiden tot een vertraagde uitvoering van deze taken en 'starve' workloads die zijn toegewezen aan een bepaalde scheduler van CPU-tijd.

Ga als volgt te werk om te controleren op schedulergebeurtenissen die niet reageren:

1. Controleer de SQL Server system_health uitgebreide gebeurtenislogboeken om te bepalen of een niet-opleverende scheduler-gebeurtenis van een bepaald type rond de tijd van de time-outgebeurtenis van de Statuscontrole van AlwaysOn is gerapporteerd. Gebeurtenissen zonder resultaat die u kunt tegenkomen, zijn onder andere:

   • scheduler_monitor_non_yielding_ring_buffer_recorded
   • scheduler_monitor_non_yielding_iocp_ring_buffer_recorded
   • scheduler_monitor_stalled_dispatcher_ring_buffer_recorded
   • scheduler_monitor_non_yielding_rm_ring_buffer_recorded

2. Open op de primaire replica de Extended Events-logboeken van system_health rond het tijdstip van de vermoedelijke time-out van de statuscontrole.

3. Ga in SQL Server Management Studio (SSMS) naar Bestand>openen en selecteer Uitgebreide gebeurtenisbestanden samenvoegen.

4. Selecteer de knop Toevoegen.

5. Ga in het dialoogvenster Bestand openen naar de bestanden in de map SQL Server \LOG.

6. Houd Control ingedrukt en selecteer vervolgens de bestanden waarvan de namen beginnen.system_health_xxx.xel

7. Selecteer Openen>OK.

8. Filter de resultaten. Klik met de rechtermuisknop op een gebeurtenis onder de naamkolom en selecteer Filteren op deze waarde.

   

9. Definieer een filter om rijen te sorteren waarin de waarden in de naamkolom staan yield, zoals wordt weergegeven in de volgende schermopname. Dit filter geeft alle soorten gebeurtenissen zonder resultaat weer die mogelijk zijn vastgelegd in de system_health-logboeken.

   

10. Vergelijk de tijdstempels om te zien of er niet-opleverende gebeurtenissen waren op het moment van de time-out van de statuscontrole. Dit is de time-out voor statuscontrole, zoals gerapporteerd in het clusterlogboek:

    0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 0000211c.00002594::2021/02/24-21:50:02.452 ERR [RES] SQL Server Availability Group < SQL19AGN1: [hadrag] Resource Alive result 0.
    

    U kunt zien dat er geen gebeurtenissen zijn opgetreden op het moment van de time-out van de statuscontrole.

    

Als er niet-opbrengstgebeurtenissen worden gedetecteerd, controleert u de oorzaak van de niet-opbrengstgebeurtenis. Overweeg contact op te nemen met het SQL Server-ondersteuningsteam om de non-yielding-gebeurtenissen te onderzoeken.

2. Controleer het SQL Server-foutenlogboek

Controleer het foutenlogboek van SQL Server op gebeurtenissen die verband houden met het moment waarop de statuscontrole een time-out gaf. Deze gebeurtenissen kunnen aanwijzingen bieden die wijzen op verdere stappen om de hoofdoorzaak van de time-outs van de statuscontrole af te bakenen.

In de volgende logboekvermelding ziet u bijvoorbeeld dat er een time-out voor statuscontrole is opgetreden in het clusterlogboek:

0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, diagnostics heartbeat is lost 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel 
0000211c.00002594::2021/02/24-02:50:02.452 ERR [RES] SQL Server Availability Group <SQL19AGN1>: [hadrag] Resource Alive result 0.

In het SQL Server foutenlogboek, binnen enkele seconden na de time-out van de statuscontrole, rapporteert SQL Server dat er ernstige I/O-latentie is gedetecteerd:

2021-02-23 20:49:54.64 spid12s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\agdb_log.ldf] in database id 12. The OS file handle is 0x0000000000001594. The offset of the latest long I/O is: 0x000030435b0000. The duration of the long I/O is: 26728 ms.

Bekijk het gebeurtenislogboek van het systeem voor mogelijke systeem-aanwijzingen die betrekking kunnen hebben op de time-out van de statuscontrole. Wanneer u het systeemgebeurtenislogboek van Windows controleert, kunt u mogelijk een I/O-probleem aantreffen dat op hetzelfde moment wordt gemeld voor dezelfde time-out van de statuscontrole:

02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"Reset to device, \Device\<device ID>, was issued."
02/23/2021,08:50:16 PM,Warning,SQL19AGN1.CSSSQL.local.local,<...>,"The IO operation at logical block address <block address> for Disk 6 (PDO name: \Device\<device ID>) was retried."

SQL Server-status: een Always On-statusgebeurtenis

AlwaysOn bewaakt verschillende soorten SQL Server-statusevenementen. Wanneer SQL Server als host fungeert voor een primaire replica van een beschikbaarheidsgroep, voert SQL Server continu sp_server_diagnostics uit, waarmee de status van SQL Server aan de hand van verschillende onderdelen wordt gerapporteerd. Wanneer er gezondheidsproblemen worden gedetecteerd, rapporteert sp_server_diagnostics een fout voor dat specifieke onderdeel en stuurt vervolgens de resultaten terug naar het Always On-statusdetectieproces. Wanneer er een fout wordt gerapporteerd, geeft de rol Beschikbaarheidsgroep de status Mislukt en mogelijke failover weer als de beschikbaarheidsgroep hiervoor is geconfigureerd.

Symptomen

Hier is een voorbeeld van een statusprobleem met SQL Server, zoals gemeld door sp_server_diagnostics in het clusterlogboek. SQL Server rapporteert een foutstatus in het systeemonderdeel aan AlwaysOn-statuscontrole en de beschikbaarheidsgroep contoso-ag wordt overgezet naar een mislukte status.

INFO [RES] SQL Server Availability Group: [hadrag] SQL Server component 'system' health state has been changed from 'warning' to 'error' at 2019-06-20 15:05:52.330
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Availability Group is not healthy with given HealthCheckTimeout and FailureConditionLevel
ERR [RES] SQL Server Availability Group <contoso-ag>: [hadrag] Resource Alive result 0.
ERR [RES] SQL Server Availability Group: [hadrag] Failure detected, the state of system component is error
WARN [RHS] Resource contoso-ag IsAlive has indicated failure.
INFO [RCM] HandleMonitorReply: FAILURENOTIFICATION for 'contoso-ag', gen(0) result 1/0.

Sql Server-statusevenementen diagnosticeren

Het type statusprobleem dat door SQL Server-status wordt gerapporteerd, moet de richting van de hoofdoorzaakanalyse dicteren.

Wanneer u een beschikbaarheidsgroep implementeert, wordt deze FAILURE_CONDITION_LEVEL standaard ingesteld als drie. Hiermee wordt bewaking van sommige, maar niet alle SQL Server-statusprofielen geactiveerd. Op het standaardniveau activeert Always On een gezondheidsgebeurtenis wanneer SQL Server te veel dumpbestanden genereert, een schrijftoegangsfout veroorzaakt of een verweesde spinlock produceert. Als u de beschikbaarheidsgroep instelt op niveau vier of vijf, wordt het aantal typen SQL Server-statusproblemen dat wordt bewaakt, uitgebreid. Zie Een flexibel automatisch failoverbeleid configureren voor een beschikbaarheidsgroep - SQL Server Always On voor meer informatie over de SQL Server Always On-statusmonitors.

Volg deze stappen om het specifieke statusprobleem van Always On te identificeren:

1. Open de diagnostische uitgebreide gebeurtenislogboeken van het SQL Server-cluster op de primaire replica tot het tijdstip waarop de vermoedelijke SQL Server-statusgebeurtenis is opgetreden.

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

3. Selecteer Toevoegen.

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

5. Houd Control ingedrukt, selecteer de bestanden waarvan de namen overeenkomen met <servername>_<instance>_SQLDIAG_xxx.xel, en selecteer vervolgens Open>OK.

   

   U ziet een nieuw venster met tabbladen in SSMS met de uitgebreide gebeurtenissen, zoals wordt weergegeven in de volgende schermopname.

6. Als u een statusprobleem van SQL Server wilt onderzoeken, zoekt u de component_health_result waarvan de waarde van state_descerror is. Hier is een voorbeeld van een gebeurtenis van een systeemonderdeel waarbij een fout is gemeld aan de statusbewaking van Always On:

   

7. Dubbelklik op de kolom data in het onderste deelvenster. Met deze actie worden de gedetailleerde onderdeelgegevens geopend in een nieuw venster van SSMS voor revisie. De systeemonderdeelgegevens zien er als volgt uit:

   

   De totalDumprequests=186 gegevens geven aan dat er te veel diagnostische gebeurtenissen voor dumpbestanden zijn gegenereerd op deze SQL Server. Deze voorwaarde zorgt ervoor dat het systeemonderdeel een foutstatus rapporteert. Wanneer de Always On-statusbewaking deze foutstatus ontvangt, activeert deze een statusgebeurtenis voor een beschikbaarheidsgroep. U kunt ook controleren of er in de gegevens van systeemcomponenten geen schrijftoegangsfouten of verweesde spinlocks worden gedetecteerd.

Solution

Afhankelijk van het type probleem dat u detecteert, kunt u dit dienovereenkomstig oplossen. Zoals in het artikel Een flexibel beleid voor automatische failover configureren voor een beschikbaarheidsgroep - SQL Server Always On wordt besproken, kunnen verschillende oorzaken tot deze situatie leiden. Enkele voorbeelden:

• De SQL Server-service is niet beschikbaar.
• Time-out voor lease.
• De beschikbaarheidsreplica heeft de status Mislukt.
• Geheugendumps die zijn gegenereerd door verweesde spinlocks, toegangsfouten of doordat er binnen korte tijd te veel geheugendumps zijn gegenereerd.
• Aanhoudend geheugentekort in de interne resourcepool van SQL Server.
• Detectie van een deadlock in de scheduler.
• Detectie van een onoplosbare impasse.

Neem indien nodig contact op met SQL Server ondersteuning om een ondersteuningsincident te openen voor verdere hulp bij het vinden van de hoofdoorzaak voor deze interne SQL Server gezondheidsproblemen.