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

Article
06/27/2023

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

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

Résumé

SQL Server Always On groupes de disponibilité peuvent être configurés pour le basculement automatique. Si un problème d’intégrité est détecté sur le 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 que vous pouvez suivre pour diagnostiquer la cause de ces échecs.

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

Lorsqu’un basculement automatique est déclenché sur le 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 soit réussi, les entrées d’erreur sont consignées dans le rapport de 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.

Remarque

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

Symptômes en cas d’échec d’un basculement automatique

Si un événement de basculement automatique échoue, le réplica secondaire ne passe pas correctement au rôle principal. Par conséquent, le réplica de disponibilité signale que cette 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 faire passer l’réplica secondaire au 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 échouer et expliquent 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é a 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 de l’échec du basculement, passez en revue le journal du cluster Windows (Cluster.log), puis case activée 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 le 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 qui ressemble au message suivant :

Pas de basculement nom de 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 la valeur Nombre maximal d’échecs dans la période spécifiée .

Remarque

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é à 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 de groupe de disponibilité sur le partenaire de basculement.

Solution

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

Remarque

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é de nombreuses fois dans une courte période. 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 agressives pour déterminer pourquoi le basculement automatique continue de se produire.

Cas 2 : Autorisations de compte NT insuffisantes\SYSTEM

La DLL de ressource moteur de base de données SQL Server se connecte au instance de SQL Server qui héberge le réplica principal à l’aide d’ODBC pour surveiller l’intégrité. Les informations d’identification de connexion utilisées pour cette connexion sont le compte de connexion local SQL ServerNT AUTHORITY\SYSTEM. Par défaut, ce compte de connexion local dispose des autorisations suivantes :

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

Si le NT AUTHORITY\SYSTEM compte de connexion n’a pas ces autorisations sur le partenaire de basculement automatique (le 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, consultez le journal du cluster Windows. Pour cela, 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 le instance de SQL Server qui héberge le réplica secondaire qui n’a pas été transféré 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 diagnostics commande. 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 Afficher l’état du serveur du NT AUTHORITY\SYSTEM compte de connexion sur le 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 des autorisations suffisantes au NT AUTHORITY\SYSTEM compte de connexion pour la détection d’intégrité de la DLL de ressource moteur de base de données SQL Server.

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

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 pas de perte de données. Par conséquent, si une base de données de disponibilité du groupe de disponibilité est en phase de synchronisation ou NOT SYNCHRONIZED d’état, le basculement automatique ne permet pas de transférer correctement l’réplica secondaire vers le 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 à validation synchrone prennent en charge deux sections de paramètres des modes de basculement et de basculement (Always On groupes de disponibilité).

Pour examiner et diagnostiquer s’il s’agit de la cause de l’échec du basculement, consultez le journal des erreurs SQL Server. Vous devez trouver une entrée d’erreur qui ressemble 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 case activée si les bases de données de disponibilité étaient dans l’étatSYNCHRONIZED, procédez comme suit :

Connectez-vous au réplica secondaire.
Exécutez le script SQL suivant pour case activée la is_failover_ready valeur de toutes les bases de données de disponibilité du groupe de disponibilité qui n’ont pas basculé.

Remarque

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 à l’état SYNCHRONIZED . Pour plus d’informations sur les modes de disponibilité, consultez Modes de disponibilité dans Always On groupes de disponibilité.

Cas 4 : la configuration « Forcer le chiffrement du protocole » est sélectionnée pour les protocoles clients sur les réplica secondaires (cible principale), bien que le réplica ne soit pas configuré pour le chiffrement

Pendant le 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 à un réplica local pour lancer la surveillance de l’intégrité. Cela fait 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 case activée pour 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, case activée le paramètre Forcer le chiffrement du protocole. S’il est défini sur Oui, remplacez la valeur par Non.
Retestez le basculement.

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

Conclusion

SQL Server Always On analyse de l’intégrité utilise une connexion ODBC locale pour surveiller l’intégrité 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 le SQL Server section Configuration réseau. 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 nœud ou l’réplica secondaire entraînent l’échec des contrôles d’intégrité Always On

Avant de basculer de l’réplica primaire vers le réplica secondaire, SQL Server DLL de ressource moteur de base de données 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 si c’est la cause, procédez comme suit :

Passez en revue le journal de cluster sur le réplica secondaire pour case activée le message d’erreur « Impossible de terminer le processus de connexion en raison d’un retard dans l’ouverture de la connexion au 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 une SQL Server réplica secondaire 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 si les planificateurs système sont sous pression, 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	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

Les valeurs élevées signalées pour WorkersWaitingForCpu et RequestWaitingForThreads indiquent qu’une 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é de la nouvelle réplica principale pendant le basculement, vous devez connecter localement la surveillance de l’intégrité AlwaysOn au SQL Server instance qui effectue la transition vers le 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 plus en détail une tentative de basculement ayant échoué, consultez le journal du 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 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 le instance de SQL Server qui héberge le réplica secondaire qui n’a pas été transféré vers le rôle principal. Un journal de cluster sera généré pendant les 60 dernières minutes d’activité.
```
Get-ClusterLog -Node <SQLServerNodeName> -TimeSpan 60
```
Pour consulter 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 pour « Se connecter à SQL Server ». Il montre également l’utilisation de l’ID de thread (côté gauche) pour localiser l’autre diagnostics qui décrivent la raison de l’échec de la tentative de connexion.

Les exemples suivants illustrent les échecs de connexion à la nouvelle réplica principale.

Exemple d’ensemble 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 du client SQL Native.

Exemple d’ensemble 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 du client SQL Native.

Exemple d’ensemble 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 de compte NT insuffisantes\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 en cas d’échec d’un basculement automatique

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 de compte NT insuffisantes\SYSTEM

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

Cas 4 : la configuration « Forcer le chiffrement du protocole » est sélectionnée pour les protocoles clients sur les réplica secondaires (cible principale), bien que le réplica ne soit pas configuré pour le chiffrement

Cas 5 : Les problèmes de performances sur le nœud ou l’réplica secondaire entraînent l’échec des contrôles d’intégrité Always On

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

Exemple d’ensemble 1

Exemple d’ensemble 2

Exemple d’ensemble 3

Commentaires

Ressources supplémentaires