Problemen met automatische failover in SQL Server AlwaysOn-omgevingen oplossen

Dit artikel helpt u bij het oplossen van problemen die optreden tijdens automatische failover in Microsoft SQL Server.

Originele productversie: SQL Server
Origineel KB-nummer: 2833707

Samenvatting

SQL Server AlwaysOn-beschikbaarheidsgroepen kunnen worden geconfigureerd voor automatische failover. Als er een statusprobleem wordt gedetecteerd op het exemplaar van SQL Server dat als host fungeert voor de primaire replica, kan de primaire rol worden overgezet naar de automatische failoverpartner (secundaire replica). De secundaire replica kan echter niet altijd worden overgezet naar de primaire rol. In sommige gevallen kan deze alleen worden overgezet naar de RESOLVING rol. In deze situatie heeft geen enkele replica de primaire rol, tenzij de primaire replica weer in orde is. Bovendien zijn de beschikbaarheidsdatabases niet toegankelijk.

In dit artikel worden enkele veelvoorkomende oorzaken van mislukte automatische failover beschreven en worden de stappen beschreven die u kunt uitvoeren om de oorzaak van deze fouten vast te stellen.

Symptomen als een automatische failover wordt geactiveerd

Wanneer een automatische failover wordt geactiveerd op het exemplaar van SQL Server dat als host fungeert voor de primaire replica, gaat de secundaire replica over naar de RESOLVING rol en vervolgens naar de primaire rol. Hoewel het proces is geslaagd, worden foutvermeldingen vastgelegd in het SQL Server logboekrapport dat lijkt op de volgende tekst:

The state of the local availability replica in availability group '\<Group name>' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'  
The state of the local availability replica in availability group '\<Group name>' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'

Opmerking

De secundaire replica wordt overgezet van een RESOLVING_NORMAL status naar een PRIMARY_NORMAL status.

Symptomen als een automatische failover mislukt

Als een automatische failover-gebeurtenis niet lukt, wordt de secundaire replica niet overgezet naar de primaire rol. Daarom meldt de beschikbaarheidsreplica dat deze replica zich in een RESOLVING status bevindt. Daarnaast geven de beschikbaarheidsdatabases aan dat ze zich in een NOT SYNCHRONIZING status bevinden en dat toepassingen geen toegang hebben tot deze databases.

In de volgende afbeelding meldt SQL Server Management Studio bijvoorbeeld dat de secundaire replica een RESOLVING status heeft omdat het automatische failoverproces de secundaire replica niet kan overzetten naar de primaire rol.

In de volgende secties worden verschillende mogelijke redenen besproken waarom automatische failover mogelijk niet lukt en hoe u elke oorzaak kunt vaststellen.

Case 1: de waarde 'Maximumfouten in de opgegeven periode' is uitgeput

De beschikbaarheidsgroep heeft eigenschappen van Windows-clusterresources, zoals de eigenschap Maximumfouten in de eigenschap Opgegeven periode . Deze eigenschap wordt gebruikt om de onbepaalde verplaatsing van een geclusterde resource te voorkomen wanneer er meerdere knooppuntfouten optreden.

Als u wilt onderzoeken en vaststellen of dit de oorzaak is van mislukte failover, controleert u het Windows-clusterlogboek (Cluster.log) en controleert u vervolgens de eigenschap.

Stap 1: de gegevens in het Windows-clusterlogboek controleren (Cluster.log)

1. Gebruik Windows PowerShell om het Windows-clusterlogboek te genereren op het clusterknooppunt dat als host fungeert voor de primaire replica. Voer hiervoor de volgende cmdlet uit in een PowerShell-venster met verhoogde bevoegdheid op het exemplaar van SQL Server dat als host fungeert voor de primaire replica:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

   [! NOTITIES]

   • Bij de -TimeSpan 15 parameter in deze stap wordt ervan uitgegaan dat het probleem dat is gediagnosticeerd, is opgetreden in de afgelopen 15 minuten.
   • Het logboekbestand wordt standaard gemaakt in %WINDIR%\cluster\reports.

2. Open het bestand Cluster.log in Kladblok om het Windows-clusterlogboek te bekijken.

3. Selecteer in Kladblok Zoeken bewerken> en zoek vervolgens naar de tekenreeks failoverCount aan het einde van het bestand. In de resultaten ziet u een bericht dat lijkt op het volgende bericht:

   Geen failover uitvoeren van de resourcenaam> van de groep<, failoverCount 3, failoverThresholdSetting <Number>, computedFailoverThreshold 2

   

Stap 2: Controleer de maximale fouten in de eigenschap Opgegeven periode

1. Failoverclusterbeheer starten.

2. Selecteer Rollen in het navigatiedeelvenster.

3. Klik in het deelvenster Rollen met de rechtermuisknop op de geclusterde resource en selecteer vervolgens Eigenschappen.

4. Selecteer het tabblad Failover en selecteer het maximum aantal fouten in de waarde Opgegeven periode .

   

   Opmerking

   Het standaardgedrag geeft aan dat als de geclusterde resource drie keer binnen zes uur mislukt, deze de status Mislukt moet blijven. Voor een beschikbaarheidsgroep betekent dit dat de replica in de RESOLVING status blijft.

Conclusie

Nadat u het logboek hebt geanalyseerd, ziet u dat de failoverCount-waarde van 3 groter is dan de waarde computedFailoverThreshold van 2. Daarom kan het Windows-cluster de failoverbewerking van de resource van de beschikbaarheidsgroep naar de failoverpartner niet voltooien.

Oplossing

U kunt dit probleem oplossen door de waarde Maximumfouten in de opgegeven periode te verhogen.

Opmerking

Het verhogen van deze waarde lost het probleem mogelijk niet op. Er is mogelijk een meer kritiek probleem waardoor de beschikbaarheidsgroep vaak binnen een korte periode mislukt. Deze periode is standaard 15 minuten. Het verhogen van deze waarde kan ertoe leiden dat de beschikbaarheidsgroep vaker mislukt en de status Mislukt blijft. We raden u aan agressieve probleemoplossing te gebruiken om te bepalen waarom automatische failover blijft plaatsvinden.

Geval 2: Onvoldoende NT-instantie\SYSTEM-accountmachtigingen

De resource-DLL van de SQL Server Database Engine maakt verbinding met het exemplaar van SQL Server dat als host fungeert voor de primaire replica door ODBC te gebruiken om de status te bewaken. De aanmeldingsreferenties die voor deze verbinding worden gebruikt, zijn de lokale SQL Server NT AUTHORITY\SYSTEM aanmeldingsaccount. Standaard krijgt dit lokale aanmeldingsaccount de volgende machtigingen:

• Een beschikbaarheidsgroep wijzigen
• SQL aansluiten
• Serverstatus weergeven

Als het NT AUTHORITY\SYSTEM aanmeldingsaccount geen van deze machtigingen heeft voor de automatische failoverpartner (de secundaire replica), kan SQL Server de statusdetectie niet starten wanneer er een automatische failover plaatsvindt. Daarom kan de secundaire replica niet worden overgezet naar de primaire rol. Als u wilt onderzoeken en vaststellen of dit de oorzaak is, raadpleegt u het Windows-clusterlogboek. Ga hiervoor als volgt te werk:

1. Gebruik Windows PowerShell om het Windows-clusterlogboek op het clusterknooppunt te genereren. Voer hiervoor de volgende cmdlet uit in een PowerShell-venster met verhoogde bevoegdheid op het exemplaar van SQL Server dat als host fungeert voor de secundaire replica die niet is overgestapt naar de primaire rol:

   Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
   

   

2. Open het bestand Cluster.log in Kladblok om het Windows-clusterlogboek te bekijken.

3. Zoek foutvermelding die lijkt op de volgende tekst:

   Kan de diagnostische opdracht niet uitvoeren. De gebruiker is niet gemachtigd om deze actie uit te voeren.

   

Conclusie

Het Cluster.log-bestand meldt dat er een machtigingsprobleem bestaat wanneer SQL Server de diagnostische opdracht uitvoert. In dit voorbeeld is de fout veroorzaakt door het verwijderen van de machtiging Serverstatus weergeven uit het NT AUTHORITY\SYSTEM aanmeldingsaccount op het exemplaar van SQL Server dat als host fungeert voor de secundaire replica van een automatisch failoverpaar.

Oplossing

U kunt dit probleem oplossen door voldoende machtigingen toe te kennen aan het NT AUTHORITY\SYSTEM aanmeldingsaccount voor de statusdetectie van de SQL Server Database Engine-resource-DLL.

Case 3: De beschikbaarheidsdatabases hebben niet de status GESYNCHRONISEERD

Als u automatisch een failover wilt uitvoeren, moeten alle beschikbaarheidsdatabases die zijn gedefinieerd in de beschikbaarheidsgroep, een SYNCHRONIZED status hebben tussen de primaire replica en de secundaire replica. Wanneer er een automatische failover plaatsvindt, moet aan deze synchronisatievoorwaarde worden voldaan om ervoor te zorgen dat er geen gegevens verloren gaan. Als één beschikbaarheidsdatabase in de beschikbaarheidsgroep de synchronisatie- of NOT SYNCHRONIZED status heeft, wordt de secundaire replica niet door automatische failover omgezet in de primaire rol.

Zie voor meer informatie over de vereiste voorwaarden voor een automatische failover de secties Voorwaarden vereist voor een automatische failover en de replica's voor synchrone doorvoer ondersteunen twee instellingen van failover- en failovermodi (AlwaysOn-beschikbaarheidsgroepen).

Als u wilt onderzoeken en vaststellen of dit de oorzaak is van mislukte failover, raadpleegt u het SQL Server foutenlogboek. U zou een foutvermelding moeten vinden die lijkt op de volgende tekst:

Een of meer databases worden niet gesynchroniseerd of zijn niet toegevoegd aan de beschikbaarheidsgroep.

Voer de volgende stappen uit om te controleren of de beschikbaarheidsdatabases de SYNCHRONIZED status hadden:

1. Maak verbinding met de secundaire replica.

2. Voer het volgende SQL-script uit om de is_failover_ready waarde te controleren voor alle beschikbaarheidsdatabases in de beschikbaarheidsgroep die geen failover heeft uitgevoerd.

   Opmerking

   Een waarde van nul voor een van de beschikbaarheidsdatabases kan automatische failover voorkomen. Deze waarde geeft aan dat de beschikbaarheidsdatabase niet SYNCHRONIZEDwas.

   SELECT database_name, is_failover_ready FROM sys.dm_hadr_database_replica_cluster_states WHERE replica_id IN (SELECT replica_id FROM sys.dm_hadr_availability_replica_states)
   

   

Conclusie

Een geslaagde automatische failover van de beschikbaarheidsgroep vereist dat alle beschikbaarheidsdatabases de SYNCHRONIZED status hebben. Zie Beschikbaarheidsmodi in AlwaysOn-beschikbaarheidsgroepen voor meer informatie over beschikbaarheidsmodi.

Case 4: De configuratie 'Protocolversleuteling forceren' is geselecteerd voor de clientprotocollen op de secundaire replica (doel primair), hoewel de replica niet is geconfigureerd voor versleuteling

Wanneer tijdens een failover de primaire server een statusprobleem detecteert, probeert de cluster-DLL op failoverpartner (secundaire replica) verbinding te maken met de lokale replica om statusbewaking te starten. Dit maakt deel uit van de overgang naar de primaire rol. Als de secundaire replica niet is geconfigureerd voor versleuteling, maar de instelling Geforceerde protocolversleuteling per ongeluk is ingesteld in de clientconfiguratie, mislukt de verbinding en kan de failover niet plaatsvinden.

Ga als volgt te werk om te controleren op deze configuratie:

1. Start SQL Server Configuration Manager.
2. Klik in het linkerdeelvenster met de rechtermuisknop op de SQL Native Client 11.0-configuratie en selecteer vervolgens Eigenschappen.
3. Schakel in het dialoogvenster de instelling Protocolversleuteling forceren in. Als deze is ingesteld op Ja, wijzigt u de waarde in Nee.
4. Test de failover opnieuw.

Conclusie

SQL Server AlwaysOn-statusbewaking maakt gebruik van een lokale ODBC-verbinding om SQL Server status te bewaken. Versleuteling van geforceerde protocollen moet alleen worden ingeschakeld in de sectie Clientconfiguratie van SQL Server Configuration Manager als SQL Server zelf is geconfigureerd om versleuteling afdwingen in SQL Server Configuration Manager in de SQL Server Sectie Netwerkconfiguratie. Zie Versleutelde verbindingen met de database-engine inschakelen voor meer informatie.

Case 5: Prestatieproblemen op secundaire replica of knooppunt zorgt ervoor dat AlwaysOn-statuscontroles mislukken

Voordat u een failover uitvoert van de primaire replica naar de secundaire replica, maakt SQL Server database-engine-resource-DLL verbinding met de secundaire replica om de status van de replica vast te stellen. Als deze verbinding mislukt vanwege prestatieproblemen op de secundaire replica, vindt er geen automatische failover plaats.

Voer de volgende stappen uit om te onderzoeken of dit de oorzaak is:

1. Controleer het clusterlogboek op de secundaire replica om te controleren op het foutbericht 'Kan het aanmeldingsproces niet voltooien vanwege vertraging bij het openen van de serververbinding'.

   0000110c.00002bcc::2020/08/06-01:17:54.943 INFO  [RCM] move of group AOCProd01AG from CO2ICMV3SQL09(1) to CO2ICMV3SQL10(2) of type MoveType::Manual is about to succeed, failoverCount=3, lastFailoverTime=2020/08/05-02:08:54.524 targeted=true 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection (0) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Could not connect to SQL Server (rc -1) 
   00002a54.0000610c::2020/08/06-01:18:44.929 INFO  [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] SQLDisconnect returns following information 
   00002a54.0000610c::2020/08/06-01:18:44.929 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0) 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RES] SQL Server Availability Group <AOCProd01AG>: [hadrag] Failed to connect to SQL Server 
   00002a54.0000610c::2020/08/06-01:18:44.931 ERR   [RHS] Online for resource AOCProd01AG failed. 
   

   Deze situatie kan optreden als de failover wordt uitgevoerd naar een SQL Server secundaire replica met een drukke bestaande workload. Dit kan de reactie van SQL Server op de HADR-statusverbindingsaanvraag vertragen en een geslaagde failoverpoging voorkomen.

2. Als u wilt bepalen of er druk is op systeemplanners, gebruikt u SQL Server Management Studio om het volgende script uit te voeren op de secundaire replica:

   USE MASTER 
   GO  
   WHILE 1=1 
   BEGIN 
   PRINT convert(varchar(20), getdate(),120) 
   DECLARE @max INT; 
   SELECT @max = max_workers_count 
   FROM sys.dm_os_sys_info; 
   SELECT GETDATE() AS 'CurrentDate',  
          @max AS 'TotalThreads',  
          SUM(active_Workers_count) AS 'CurrentThreads',  
          @max - SUM(active_Workers_count) AS 'AvailableThreads',  
          SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',  
          SUM(work_queue_count) AS 'RequestWaitingForThreads' 
          --SUM(current_workers_count) AS 'AssociatedWorkers' 
   FROM sys.dm_os_Schedulers 
   WHERE STATUS = 'VISIBLE ONLINE'; 
   wait for delay '0:0:15' 
   END
   

   Hier volgt een voorbeeld van de uitvoer van de voorgaande query:

   Currentdate	TotalThreads	CurrentThreads	AvailableThreads	WorkersWaitingForCpu	RequestWaitingForThreads
   2020-10-06 01:27:01.337	1216	361	855	33	0
   2020-10-06 01:27:08.340	1216	1412	-196	22	76
   2020-10-06 01:27:15.340	1216	1304	-88	2	161
   2020-10-06 01:27:22.340	1216	1242	-26	21	185
   2020-10-06 01:27:29.343	1216	1346	-130	19	476
   2020-10-06 01:27:36.350	1216	1350	-134	9	630
   2020-10-06 01:27:43.353	1216	1346	-130	13	539
   2020-10-06 01:27:50.360	1216	1378	-162	5	328
   2020-10-06 01:27:57.360	1216	197	1019	0	0

   Hoge waarden die worden gerapporteerd voor WorkersWaitingForCpu en RequestWaitingForThreads geven aan dat er sprake is van een planningsconflict en dat SQL Server de huidige workload niet tijdig kan verwerken.

Oplossing

Als u dit probleem ondervindt, kunt u de werkbelasting op de secundaire replica opnieuw verdelen of de verwerkingskracht (processors toevoegen) op de computers waarop deze workloads worden uitgevoerd, vergroten.

Problemen met andere mislukte failovergebeurtenissen oplossen

Als u de status van de nieuwe primaire replica tijdens een failover wilt bewaken, moet u lokaal verbinding maken met de SQL Server instantie die overgaat naar de primaire rol.

Naast de meest voorkomende redenen die in dit artikel worden besproken, zijn er veel andere redenen waarom deze verbindingspoging kan mislukken. Als u een mislukte failoverpoging verder wilt onderzoeken, raadpleegt u het clusterlogboek op de failoverpartner (de replica waarnaar u geen failover kunt uitvoeren):

1. Gebruik Windows PowerShell om het Windows-clusterlogboek op het clusterknooppunt te genereren. Voer hiervoor de volgende cmdlet uit in een PowerShell-venster met verhoogde bevoegdheid op het exemplaar van SQL Server dat als host fungeert voor de secundaire replica die niet is overgezet naar de primaire rol. Er wordt een clusterlogboek gegenereerd voor de afgelopen 60 minuten van activiteit.

   Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
   

2. Als u het Windows-clusterlogboek wilt bekijken, opent u het bestand Cluster.log in Kladblok.

3. Zoek naar de tekenreeks 'Verbinding maken met SQL Server' die valt tijdens de mislukte failover-gebeurtenis.

4. Controleer de volgende aanmeldingsberichten met behulp van de thread-id (zie de volgende schermopname) om de gebeurtenissen te correleren die betrekking hebben op de aanmeldings gebeurtenis. In het volgende voorbeeld ziet u een zoekopdracht naar 'Verbinding maken met SQL Server'. U ziet ook dat u de thread-id (links) gebruikt om de andere diagnostische gegevens te vinden die beschrijven waarom de verbindingspoging is mislukt.

   

In de volgende voorbeelden ziet u verbindingsfouten met de nieuwe primaire replica.

Voorbeeldset 1

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: No client protocols are enabled and no protocol was specified in the connection
string [xFFFFFFFF]. (268435455)

Oplossing

Start SQL Server Configuration Manager en controleer of Gedeeld geheugen of TCP/IP is ingeschakeld onder Clientprotocollen voor de SQL Native Client Configuratie.

Voorbeeldset 2

[hadrag] ODBC Error: [08001] [Microsoft][SQL Server Native Client 11.0]SQL Server Network
Interfaces: Server doesn't support requested protocol [xFFFFFFFF]. (268435455)

Oplossing

Start SQL Server Configuration Manager en controleer of Gedeeld geheugen of TCP/IP is ingeschakeld onder Clientprotocollen voor de SQL Native Client Configuratie.

Voorbeeldset 3

000010b8.00001764::2020/12/02-16:52:49.808 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot alter the availability
group 'ag', because it does not exist or you do not have permission. (15151)
000010b8.00000fd0::2020/12/02-17:01:14.821 ERR [RES] SQL Server Availability Group: [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The user does not have permission to perform this action. (297)
000010b8.00001838::2020/12/02-17:10:04.427 ERR [RES] SQL Server Availability Group : [hadrag]
ODBC Error: [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user
'SQLREPRO\NODE2$'. Reason: The account is disabled. (18470)

Oplossing

Bekijk case 2: onvoldoende NT-instantie\SYSTEM-accountmachtigingen.