Résolution des problèmes de basculement automatique dans les environnements SQL Server Always On

Article
12/14/2024

Cet article vous aide à résoudre les problèmes qui se produisent pendant le basculement automatique dans Microsoft SQL Server.

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 2833707

Résumé

Les groupes de disponibilité Always On SQL Server peuvent être configurés pour le basculement automatique. Si un problème d’intégrité est détecté sur l’instance de SQL Server qui héberge le réplica principal, le rôle principal peut être transféré vers le partenaire de basculement automatique (réplica secondaire). Toutefois, le réplica secondaire ne peut pas toujours être transféré vers le rôle principal. Dans certains cas, il peut être transféré uniquement vers le RESOLVING rôle. Dans ce cas, aucun réplica n’aura le rôle principal, sauf si le réplica principal revient à un état sain. En outre, les bases de données de disponibilité seront inaccessibles.

Cet article répertorie certaines causes courantes d’échec du basculement automatique et décrit les étapes à suivre pour diagnostiquer la cause de ces défaillances.

Symptômes si un basculement automatique est déclenché avec succès

Lorsqu’un basculement automatique est déclenché sur l’instance de SQL Server qui héberge le réplica principal, le réplica secondaire passe au RESOLVING rôle, puis au rôle principal. Bien que le processus réussisse, les entrées d’erreur sont consignées dans le rapport du journal SQL Server qui ressemble au texte suivant :

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'

Capture d’écran du journal des erreurs si un basculement automatique est déclenché avec succès.

Note

Le réplica secondaire passe correctement d’un RESOLVING_NORMAL état à un PRIMARY_NORMAL état.

Symptômes si un basculement automatique échoue

Si un événement de basculement automatique n’a pas réussi, le réplica secondaire ne passe pas correctement au rôle principal. Par conséquent, le réplica de disponibilité signale que ce réplica est dans un RESOLVING état. En outre, les bases de données de disponibilité signalent qu’elles sont dans un NOT SYNCHRONIZING état et que les applications ne peuvent pas accéder à ces bases de données.

Par exemple, dans l’image suivante, SQL Server Management Studio signale que le réplica secondaire est dans un RESOLVING état, car le processus de basculement automatique n’a pas pu transférer le réplica secondaire vers le rôle principal.

Capture d’écran des réplicas de disponibilité dans SQL Server Management Studio.

Les sections suivantes décrivent plusieurs raisons possibles pour lesquelles le basculement automatique peut ne pas réussir et comment diagnostiquer chaque cause.

Cas 1 : La valeur « Nombre maximal d’échecs dans la période spécifiée » est épuisée

Le groupe de disponibilité possède des propriétés de ressource de cluster Windows, telles que la propriété Nombre maximal d’échecs dans la propriété Période spécifiée. Cette propriété est utilisée pour éviter le déplacement indéfini d’une ressource en cluster lorsque plusieurs défaillances de nœud se produisent.

Pour examiner et diagnostiquer s’il s’agit de la cause d’un basculement non réussi, passez en revue le journal du cluster Windows (Cluster.log), puis vérifiez la propriété.

Étape 1 : Passer en revue les données dans le journal du cluster Windows (Cluster.log)

Utilisez Windows PowerShell pour générer le journal de cluster Windows sur le nœud de cluster qui héberge le réplica principal. Pour ce faire, exécutez l’applet de commande suivante dans une fenêtre PowerShell avec élévation de privilèges sur l’instance de SQL Server qui héberge le réplica principal :
```
Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
```
[!REMARQUES]
- Le -TimeSpan 15 paramètre de cette étape suppose que le problème en cours de diagnostic s’est produit au cours des 15 minutes précédentes.
- Par défaut, le fichier journal est créé dans %WINDIR%\cluster\reports.
Ouvrez le fichier Cluster.log dans le Bloc-notes pour consulter le journal du cluster Windows.
Dans le Bloc-notes, sélectionnez Modifier la recherche, puis recherchez> la chaîne « failoverCount » à la fin du fichier. Dans les résultats, vous devez trouver un message semblable au message suivant :

Ne pas basculer le nom> de la ressource de groupe<, failoverCount 3, failoverThresholdSetting <Number>, computedFailoverThreshold 2

Étape 2 : Vérifier le nombre maximal d’échecs dans la propriété Période spécifiée

Démarrez le Gestionnaire du cluster de basculement.
Dans le volet de navigation, sélectionnez Rôles.
Dans le volet Rôles , cliquez avec le bouton droit sur la ressource en cluster, puis sélectionnez Propriétés.
Sélectionnez l’onglet Basculement , puis sélectionnez le nombre maximal d’échecs dans la valeur période spécifiée.

Note

Le comportement par défaut spécifie que si la ressource en cluster échoue trois fois dans les six heures, elle doit rester dans l’état d’échec. Pour un groupe de disponibilité, cela signifie que le réplica est laissé dans l’état RESOLVING .

Conclusion

Après avoir analysé le journal, vous constatez que la valeur failoverCount de 3 est supérieure à la valeur computedFailoverThreshold de 2. Par conséquent, le cluster Windows ne peut pas terminer l’opération de basculement de la ressource du groupe de disponibilité vers le partenaire de basculement.

Solution

Pour résoudre ce problème, augmentez le nombre maximal d’échecs dans la valeur période spécifiée.

Note

L’augmentation de cette valeur peut ne pas résoudre le problème. Il peut y avoir un problème plus critique qui provoque l’échec du groupe de disponibilité plusieurs fois dans un court délai. Par défaut, cette période est de 15 minutes. L’augmentation de cette valeur peut simplement entraîner l’échec du groupe de disponibilité plus de fois et rester dans un état d’échec. Nous vous recommandons d’utiliser la résolution des problèmes agressifs pour déterminer pourquoi le basculement automatique continue de se produire.

Cas 2 : Autorisations insuffisantes du compte NT Authority\SYSTEM

Sql Server Moteur de base de données DLL de ressource se connecte à l’instance de SQL Server qui héberge le réplica principal à l’aide d’ODBC pour surveiller l’intégrité. Les informations d’identification d’ouverture de session utilisées pour cette connexion sont le compte de connexion SQL Server NT AUTHORITY\SYSTEM local. Par défaut, ce compte de connexion local reçoit les autorisations suivantes :

Modifier n’importe quel groupe de disponibilité
Connecter SQL
Afficher l’état du serveur

Si le NT AUTHORITY\SYSTEM compte de connexion ne dispose pas de ces autorisations sur le partenaire de basculement automatique (réplica secondaire), SQL Server ne peut pas démarrer la détection d’intégrité lorsqu’un basculement automatique se produit. Par conséquent, le réplica secondaire ne peut pas passer au rôle principal. Pour examiner et diagnostiquer si c’est la cause, passez en revue le journal du cluster Windows. Pour ce faire, procédez comme suit :

Utilisez Windows PowerShell pour générer le journal de cluster Windows sur le nœud du cluster. Pour ce faire, exécutez l’applet de commande suivante dans une fenêtre PowerShell avec élévation de privilèges sur l’instance de SQL Server qui héberge le réplica secondaire qui n’a pas effectué la transition vers le rôle principal :
```
Get-ClusterLog -Node <SQL Server node name> -TimeSpan 15
```
Ouvrez le fichier Cluster.log dans le Bloc-notes pour consulter le journal du cluster Windows.
Recherchez l’entrée d’erreur qui ressemble au texte suivant :

Échec de l’exécution de la commande diagnostics. L'utilisateur n'est pas autorisé à effectuer cette action.

Conclusion

Le fichier Cluster.log signale qu’un problème d’autorisation existe quand SQL Server exécute la commande diagnostics. Dans cet exemple, l’échec a été provoqué par la suppression de l’autorisation d’état du serveur d’affichage du compte de NT AUTHORITY\SYSTEM connexion sur l’instance de SQL Server qui héberge le réplica secondaire d’une paire de basculement automatique.

Solution

Pour résoudre ce problème, accordez suffisamment d’autorisations au NT AUTHORITY\SYSTEM compte de connexion pour la détection d’intégrité de la DLL de ressource SQL Server Moteur de base de données.

Cas 3 : Les bases de données de disponibilité ne sont pas dans un état SYNCHRONE

Pour basculer automatiquement, toutes les bases de données de disponibilité définies dans le groupe de disponibilité doivent être dans un SYNCHRONIZED état entre le réplica principal et le réplica secondaire. Lorsqu’un basculement automatique se produit, cette condition de synchronisation doit être remplie pour vous assurer qu’il n’y a aucune perte de données. Par conséquent, si une base de données de disponibilité dans le groupe de disponibilité se trouve dans la synchronisation ou NOT SYNCHRONIZED l’état, le basculement automatique ne fait pas passer le réplica secondaire au rôle principal.

Pour plus d’informations sur les conditions requises pour un basculement automatique, consultez les conditions requises pour un basculement automatique et les réplicas de validation synchrone prennent en charge deux sections de paramètres des modes de basculement et de basculement (groupes de disponibilité Always On) .

Pour examiner et diagnostiquer s’il s’agit de la cause d’un basculement non réussi, passez en revue le journal des erreurs SQL Server. Vous devez trouver une entrée d’erreur semblable au texte suivant :

Une ou plusieurs bases de données ne sont pas synchronisées ou n’ont pas rejoint le groupe de disponibilité.

Capture d’écran du journal des erreurs SQL Server dans le cas 3.

Pour vérifier si les bases de données de disponibilité étaient dans l’état SYNCHRONIZED , procédez comme suit :

Connectez-vous au réplica secondaire.
Exécutez le script SQL suivant pour vérifier la is_failover_ready valeur de toutes les bases de données de disponibilité du groupe de disponibilité qui ne basculent pas.

Note

La valeur zéro pour l’une des bases de données de disponibilité peut empêcher le basculement automatique. Cette valeur indique que la base de données de disponibilité n’était pas SYNCHRONIZED.
```
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)
```

Conclusion

Un basculement automatique réussi du groupe de disponibilité nécessite que toutes les bases de données de disponibilité soient dans l’état SYNCHRONIZED . Pour plus d’informations sur les modes de disponibilité, consultez Les modes de disponibilité dans les groupes de disponibilité Always On.

Cas 4 : La configuration « Forcer le chiffrement du protocole » est sélectionnée pour les protocoles clients sur le réplica secondaire (réplica principal cible) même si le réplica n’est pas configuré pour le chiffrement

Lors du basculement, lorsque le serveur principal détecte un problème d’intégrité, la DLL de cluster sur le partenaire de basculement (réplica secondaire) tente de se connecter au réplica local pour lancer la surveillance de l’intégrité. Il s’agit d’une partie de la transition vers le rôle principal. Si le réplica secondaire n’est pas configuré pour le chiffrement, mais que le paramètre Forcer le chiffrement du protocole est défini par inadvertance dans la configuration du client, la connexion échoue et le basculement ne peut pas se produire.

Pour vérifier cette configuration :

Démarrez le Gestionnaire de configuration SQL Server.
Dans le volet gauche , cliquez avec le bouton droit sur la configuration sql Native Client 11.0, puis sélectionnez Propriétés.
Dans la boîte de dialogue, cochez le paramètre Forcer le chiffrement du protocole. Si elle est définie sur Oui, remplacez la valeur Non.
Retestez le basculement.

Capture d’écran des propriétés de configuration sql Native Client 11.0 dans Gestionnaire de configuration SQL Server.

Conclusion

La surveillance de l’intégrité Always On de SQL Server utilise une connexion ODBC locale pour surveiller l’intégrité de SQL Server. Forcer le chiffrement du protocole doit être activé dans la section Configuration du client de Gestionnaire de configuration SQL Server uniquement si SQL Server lui-même a été configuré pour forcer les chiffrements dans Gestionnaire de configuration SQL Server dans la section Configuration du réseau SQL Server. Pour plus d’informations, consultez Activer les connexions chiffrées au moteur de base de données.

Cas 5 : Les problèmes de performances sur le réplica secondaire ou le nœud provoquent l’échec des vérifications d’intégrité Always On

Avant de basculer du réplica principal vers le réplica secondaire, SQL Server Moteur de base de données DLL de ressource se connecte au réplica secondaire pour vérifier l’intégrité du réplica. Si cette connexion échoue en raison de problèmes de performances sur le réplica secondaire, le basculement automatique ne se produit pas.

Pour examiner et diagnostiquer s’il s’agit de la cause, procédez comme suit :

Passez en revue le journal du cluster sur le réplica secondaire pour vérifier le message d’erreur « Impossible d’effectuer le processus de connexion en raison d’un retard dans l’ouverture de la connexion du serveur ».

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.

Cette situation peut se produire si le basculement est effectué vers un réplica secondaire SQL Server qui a une charge de travail existante occupée. Cela peut retarder la réponse de SQL Server à la tentative de demande de connexion d’intégrité HADR et empêcher une tentative de basculement réussie.

Pour déterminer s’il existe une pression sur les planificateurs système, utilisez SQL Server Management Studio pour exécuter le script suivant sur le réplica secondaire :

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

Voici un exemple de sortie de la requête précédente :

CurrentDate	TotalThreads	CurrentThreads	AvailableThreads	Workers WaitingForCpu	Request WaitingForThreads
2020-10-06 01:27:01.337	1 216	361	855	33	0
2020-10-06 01:27:08.340	1 216	1412	-196	22	76
2020-10-06 01:27:15.340	1 216	1304	-88	2	161
2020-10-06 01:27:22.340	1 216	1242	26	21	185
2020-10-06 01:27:29.343	1 216	13:46	-130	19	476
2020-10-06 01:27:36.350	1 216	1 350	-134	9	630
2020-10-06 01:27:43.353	1 216	13:46	-130	13	539
2020-10-06 01:27:50.360	1 216	1378	-162	5	328
2020-10-06 01:27:57.360	1 216	197	1019	0	0

Des valeurs élevées signalées pour WorkersWaitingForCpu et RequestWaitingForThreads indiquent que la contention de planification se produit et que SQL Server ne peut pas traiter la charge de travail actuelle en temps voulu.

Solution

Si vous rencontrez ce problème, rééquilibrez la charge de travail sur le réplica secondaire ou envisagez d’augmenter la puissance de traitement (ajouter des processeurs) sur les ordinateurs exécutant ces charges de travail.

Résoudre les autres événements de basculement ayant échoué

Pour surveiller l’intégrité du nouveau réplica principal pendant le basculement, vous devez connecter localement la surveillance de l’intégrité AlwaysOn à l’instance SQL Server qui passe au rôle principal.

Outre les raisons les plus courantes décrites dans cet article, il existe de nombreuses autres raisons pour lesquelles cette tentative de connexion peut échouer. Pour examiner une tentative de basculement ayant échoué, passez en revue le journal de cluster sur le partenaire de basculement (le réplica vers lequel vous n’avez pas pu basculer) :

Utilisez Windows PowerShell pour générer le journal du cluster Windows sur le nœud du cluster. Pour ce faire, exécutez l’applet de commande suivante dans une fenêtre PowerShell avec élévation de privilèges sur l’instance de SQL Server qui héberge le réplica secondaire qui n’a pas effectué la transition vers le rôle principal. Un journal de cluster est généré pendant les 60 dernières minutes d’activité.
```
Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
```
Pour passer en revue le journal du cluster Windows, ouvrez le fichier Cluster.log dans le Bloc-notes.
Recherchez la chaîne « Se connecter à SQL Server » qui tombe pendant l’événement de basculement ayant échoué.
Passez en revue les messages de connexion suivants à l’aide de l’ID de thread (voir la capture d’écran suivante) pour mettre en corrélation les événements liés à l’événement de connexion. L’exemple suivant montre une recherche de « Se connecter à SQL Server ». Il montre également l’utilisation de l’ID de thread (côté gauche) pour localiser les autres diagnostics qui décrivent pourquoi la tentative de connexion a échoué.

Les exemples suivants montrent les échecs de connexion au nouveau réplica principal.

Exemple de jeu 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)

Solution

Démarrez Gestionnaire de configuration SQL Server, puis vérifiez que la mémoire partagée ou TCP/IP est activée sous protocoles clients pour la configuration sql Native Client.

Exemple de jeu 2

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

Solution

Démarrez Gestionnaire de configuration SQL Server, puis vérifiez que la mémoire partagée ou TCP/IP est activée sous protocoles clients pour la configuration sql Native Client.

Exemple de jeu 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)

Solution

Passez en revue le cas 2 : Autorisations insuffisantes du compte NT Authority\SYSTEM.

Partager via

Résolution des problèmes de basculement automatique dans les environnements SQL Server Always On

Résumé

Symptômes si un basculement automatique est déclenché avec succès

Symptômes si un basculement automatique échoue

Cas 1 : La valeur « Nombre maximal d’échecs dans la période spécifiée » est épuisée

Étape 1 : Passer en revue les données dans le journal du cluster Windows (Cluster.log)

Étape 2 : Vérifier le nombre maximal d’échecs dans la propriété Période spécifiée

Cas 2 : Autorisations insuffisantes du compte NT Authority\SYSTEM

Cas 3 : Les bases de données de disponibilité ne sont pas dans un état SYNCHRONE

Cas 4 : La configuration « Forcer le chiffrement du protocole » est sélectionnée pour les protocoles clients sur le réplica secondaire (réplica principal cible) même si le réplica n’est pas configuré pour le chiffrement

Cas 5 : Les problèmes de performances sur le réplica secondaire ou le nœud provoquent l’échec des vérifications d’intégrité Always On

Résoudre les autres événements de basculement ayant échoué

Exemple de jeu 1

Exemple de jeu 2

Exemple de jeu 3

Commentaires

Ressources supplémentaires