Résoudre les problèmes liés aux délais d’attente intermittents de connexion entre les réplicas de groupes de disponibilité

Résumé

Dans un groupe de disponibilité Always On de SQL Server, une expiration du délai de connexion se produit lorsqu’un réplica ne reçoit pas de réponse du réplica partenaire dans le délai de SESSION_TIMEOUT. Le journal des erreurs SQL Server signale ces délais d’attente intermittents en tant qu’erreurs 35201, 35206 et 35267. Ces délais d’expiration peuvent laisser le groupe de disponibilité dans un état non synchronisé. Les causes courantes incluent un problème d’application, tel qu’une utilisation élevée du processeur ou un planificateur sans rendement, ou un problème réseau, tel que la latence ou les paquets supprimés. Cet article vous aide à interpréter les erreurs de délai d’expiration de connexion et à diagnostiquer la cause racine à l’aide des journaux d’erreurs SQL Server, des vues de gestion dynamique Always On (DMV), des événements étendus et des traces réseau. Il explique également comment atténuer les délais d’expiration, notamment comment ajuster le paramètre de réplica SESSION_TIMEOUT du groupe de disponibilité.

Symptômes et effets des délais d’attente de connexion intermittents

Les réplicas primaires et secondaires renvoient des résultats différents

Les charges de travail en lecture seule qui interrogent des réplicas secondaires peuvent interroger des données obsolètes. Si des expirations intermittentes des connexions à la réplique se produisent, les modifications apportées aux données dans la base de données de la réplique principale ne sont pas encore répercutées dans la base de données secondaire lorsque vous interrogez ces mêmes données. Pour plus d’informations, consultez la section Latence des données sur le réplica secondaire.

Le groupe de disponibilité du rapport de diagnostic n’est pas synchronisé

Le tableau de bord Always On dans SQL Server Management Studio (SSMS) peut signaler un groupe de disponibilité non sain qui a des réplicas dans l’état non synchronisant.

Lorsque vous consultez les journaux des erreurs de SQL Server pour ces réplicas, il se peut que vous voyiez des messages tels que ceux-ci, indiquant un dépassement du délai de connexion entre les réplicas du groupe de disponibilité.

Voici le journal des erreurs de la réplique principale.

2023-02-15 07:10:55.500 spid43s Always On availability groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Voici le journal d’erreurs de la réplique secondaire.

2023-02-15 07:11:03.100 spid31s A connection time-out has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Des problèmes de connexion intermittente peuvent affecter la préparation au basculement d’un réplica secondaire

Si vous configurez le groupe de disponibilité pour le basculement automatique et que le partenaire de basculement de validation synchrone se déconnecte par intermittence du serveur principal, le basculement automatique peut échouer.

Vous pouvez interroger sys.dm_hadr_database_replica_cluster_states pour vérifier si la base de données du groupe de disponibilité est prête pour le basculement. Voici un exemple de résultats si le point de terminaison de mise en miroir est arrêté sur la réplique secondaire.

SELECT drcs.database_name, drcs.is_failover_ready, ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON ar.replica_id=drcs.replica_id
WHERE ars.role_desc='SECONDARY'

Le basculement automatique peut ne pas mettre en ligne le groupe de disponibilité dans le rôle principal sur l’ordinateur partenaire de basculement si le basculement coïncide avec l’expiration du délai de connexion d’un réplica.

Signification des erreurs de délai d’expiration de la connexion

La valeur par défaut du paramètre de réplica du groupe de disponibilité SESSION_TIMEOUT est de 10 secondes. Vous configurez ce paramètre pour chaque réplique. Il détermine le temps pendant lequel le réplica attend de recevoir une réponse de la part de son réplica partenaire avant de signaler un dépassement du délai de connexion. Si une réplique ne reçoit aucune réponse de la réplique partenaire, elle consigne un délai d’expiration de connexion dans le journal d’erreurs de Microsoft SQL Server et dans le journal des applications de Windows. Le réplica qui signale une expiration du délai d’attente tente immédiatement de se reconnecter, puis renouvelle la tentative toutes les cinq secondes.

En règle générale, un seul réplica détecte et signale le délai d’expiration de la connexion. Mais les deux répliques peuvent signaler le délai d’expiration de la connexion en même temps. Différentes versions de ce message existent, selon que le délai d’expiration de la connexion s’est produit sur une connexion établie précédemment ou sur une nouvelle connexion.

Message 35206 A connection timeout has occurred on a previously established connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Message 35201 A connection timeout has occurred while attempting to establish a connection to availability replica '<replicaname>' with id [<replicaid>]. Either a networking or firewall issue exists, or the endpoint address provided for the replica is not the database mirroring endpoint of the host server instance.

Le réplica partenaire peut ne pas détecter un délai d’expiration. Si c’est le cas, il peut signaler le message 35201 ou 35206. Si ce n’est pas le cas, il signale une perte de connexion à chacune des bases de données du groupe de disponibilité.

Message 35267 Always On Availability Groups connection with primary/secondary database terminated for primary/secondary database '<databasename>' on the availability replica '<replicaname>' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Voici un exemple de ce que SQL Server signale au journal des erreurs. Si vous arrêtez le point de terminaison de mise en miroir sur le réplica principal, le réplica secondaire détecte un délai d’expiration de connexion et les messages 35206 et 35267 sont signalés dans le journal des erreurs du réplica secondaire.

2023-02-15 07:11:03.100 spid31s A connection timeout has occurred on a previously established connection to availability replica 'SQL19AGN1' with id [<replicaid>]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

2023-02-15 07:11:03.100 spid31s Always On Availability Groups connection with primary database terminated for secondary database 'agdb' on the availability replica 'SQL19AGN1' with Replica ID:[<replicaid>]. This is an informational message only. No user action is required.

Dans cet exemple, la réplique principale n’a pas détecté d’expiration du délai de connexion, car elle pouvait toujours communiquer avec la réplique secondaire. Il a signalé le message 35267 pour chaque base de données du groupe de disponibilité. Ici, il n’y a qu’une seule base de données, « agdb ».

2023-02-15 07:10:55.500 spid43s Always On Availability Groups connection with secondary database terminated for primary database 'agdb' on the availability replica 'SQL19AGN2' with Replica ID: {<replicaid>}. This is an informational message only. No user action is required.

Causes des délais d’expiration de connexion du réplica

Problème d’application

SQL Server est peut-être trop occupé pour prendre en charge la connexion au point de terminaison de mise en miroir dans le délai du groupe de disponibilité SESSION_TIMEOUT, ce qui entraîne l’expiration du délai de connexion. Voici quelques raisons courantes :

• SQL Server rencontre 100 % d’utilisation du processeur. Cette condition signifie que SQL Server ou une autre application consomme tout le processeur disponible pendant des secondes à la fois.

• SQL Server rencontre des événements de planificateur sans rendement. Les threads SQL Server cèdent le processeur (CPU) afin que d’autres threads puissent terminer leur travail. Si un thread ne génère pas en temps voulu, il peut retarder la connexion de point de terminaison de mise en miroir et provoquer un délai d’expiration.

• SQL Server rencontre des problèmes d’épuisement des threads de travail, des problèmes de mémoire insuffisante ou des problèmes d’application qui affectent sa capacité à traiter la connexion de point de terminaison de mise en miroir.

Problème réseau

Collectez les traces réseau sur les réplicas principal et secondaire lorsque l’erreur se produit, puis analysez-les afin de détecter une latence réseau et des paquets perdus.

Diagnostiquer les délais d’expiration de la connexion du réplica

Cette section explique comment analyser les journaux d’activité SQL Server pour diagnostiquer les problèmes d’application qui empêchent SQL Server de gérer la connexion avec le réplica partenaire. Ces conseils peuvent vous aider à identifier la cause première des expirations du délai de connexion du réplica. Il se termine par des instructions plus avancées sur la collecte des traces réseau lorsque les délais d’expiration de connexion se produisent afin de pouvoir vérifier l’état du réseau.

Évaluer le moment et la localisation des expirations de délai de connexion de la réplique

Passez en revue l’historique, la fréquence et les tendances des délais d’expiration de connexion. Les messages dans le journal des erreurs SQL Server sont une bonne source pour cette révision. Où sont signalés les délais d’expiration de la connexion ? Sont-ils rapportés de manière cohérente sur la réplique principale ou sur la réplique secondaire ? Quand les erreurs se sont-elles produites ? Est-ce qu’ils se sont produit dans une certaine semaine du mois, le jour de la semaine ou l’heure de la journée ? D’autres traitements planifiés de maintenance ou de traitement par lots correspondent-ils aux heures auxquelles les délais d’expiration de connexion sont observés ? Cette évaluation peut vous aider à délimiter et à corréler les délais d’expiration des connexions afin d’identifier la cause première.

Passez en revue la session d’événements étendue AlwaysOn_health

La session d’événements étendus AlwaysOn_health a été améliorée pour inclure l’événement ucs_connection_setup, qui est déclenché lorsqu’un réplica établit une connexion avec son réplica associé. Cet événement peut vous aider quand vous résolvez les problèmes de délai d’expiration de connexion.

Interroger des vues de gestion dynamique Always On (DMV)

Vous pouvez interroger les vues de gestion dynamique Always On pour obtenir plus d’informations sur l’état connecté du réplica. Cette requête signale uniquement l’état connecté et toutes les erreurs associées au délai d’expiration de la connexion au moment où les problèmes se produisent. Si les problèmes de connexion sont intermittents, la requête risque de ne pas capturer facilement l’état déconnecté.

SELECT ar.replica_server_name, ars.role_desc, ars.connected_state_desc,
ars.last_connect_error_description, ars.last_connect_error_number, ar.endpoint_url
FROM sys.dm_hadr_availability_replica_states ars JOIN sys.availability_replicas ar ON ars.replica_id=ar.replica_id

L’exemple suivant montre un état déconnecté persistant, car le point de terminaison de la mise en miroir sur le réplica principal a été arrêté. Lorsque vous interrogez le réplica principal, la vue de gestion dynamique (DMV) Always On peut fournir des informations sur le réplica principal ainsi que sur tous les réplicas secondaires (le point de terminaison est désactivé sur le réplica principal).

Lorsque vous interrogez le réplica secondaire, le DMV Always On signale uniquement le réplica secondaire.

Passez en revue la session d’événements étendue Always On

1. Connectez-vous à chaque réplique à l’aide de l’Explorateur d’objets de SSMS, puis ouvrez les fichiers d’événements étendus AlwaysOn_health.

2. Dans SSMS, accédez à Fichier>Ouvrir, puis sélectionnez Fusionner les fichiers d’événements étendus.

3. Cliquez sur le bouton Ajouter.

4. Dans la boîte de dialogue Ouvrir le fichier, accédez aux fichiers du répertoire SQL Server \LOG.

5. Sélectionnez et maintenez la touche Ctrl enfoncée, puis sélectionnez les fichiers dont le nom commence par AlwaysOn_healthxxx.xel.

6. Sélectionnez Ouvrir, puis OK.

   Une nouvelle fenêtre à onglets s’affiche dans SSMS qui affiche les événements AlwaysOn.

   La capture d’écran suivante montre AlwaysOn_health les données de la réplique secondaire. La première zone encadrée montre la perte de connexion une fois le point de terminaison du réplica principal arrêté. Le deuxième encadré montre l’échec de connexion qui se produit lors de la tentative suivante du réplica secondaire de se connecter au réplica principal.

   

Vérifier si des événements bloquants provoquent des expirations de connexion

L’une des raisons les plus courantes pour lesquelles un réplica de disponibilité ne peut pas traiter la connexion de réplica partenaire est un planificateur sans rendement. Pour plus d’informations sur les planificateurs sans rendement, consultez Résolution des problèmes liés à la planification et au rendement SQL Server.

SQL Server détecte des événements de non-réponse du planificateur d’une durée de seulement 5 à 10 secondes. Il signale ces événements dans le point de données TrackingNonYieldingScheduler de la sortie du composant sp_server_diagnostics query_processing.

Pour vérifier s’il existe des événements non coopératifs susceptibles de provoquer des délais d’expiration de connexion de réplique, procédez comme suit.

1. Créez un travail SQL Agent qui enregistre sp_server_diagnostics toutes les cinq secondes.

2. Planifiez ce travail sur le serveur qui ne signale pas le délai d’expiration de la connexion. C’est-à-dire, supposons que la réplique du serveur A indique le délai d’expiration de la connexion dans son journal d’erreurs. Dans ce cas, configurez le travail de l’Agent SQL sur le réplica partenaire, sur le serveur B. Autrement, si vous constatez des délais d’expiration de connexion sur les deux réplicas, créez le travail sur les deux réplicas.

3. Exécutez le script T-SQL suivant pour créer un travail qui s’exécute sp_server_diagnostics toutes les cinq secondes, ajoute la sortie à un fichier texte, puis démarre le travail. Dans l’exemple suivant, la sp_server_diagnostics 5 commande s’exécute toutes les cinq secondes. Il n’est donc pas nécessaire de planifier l’exécution de ce travail toutes les cinq secondes. Démarrez simplement la tâche et elle s’exécute toutes les cinq secondes jusqu’à ce qu’elle soit arrêtée.

   USE [msdb]
   GO
   DECLARE @ReturnCode INT
   SELECT @ReturnCode = 0
   DECLARE @jobId BINARY(16)
   EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Run sp_server_diagnostics',
   @owner_login_name=N'sa', @job_id = @jobId OUTPUT
   /****** Object: Step [Run SP_SERVER_DIAGNOSTICS] Script Date: 2/15/2023 4:20:41 PM ******/
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run SP_SERVER_DIAGNOSTICS',
   @subsystem=N'TSQL',
   @command=N'sp_server_diagnostics 5',
   @database_name=N'master',
   @output_file_name=N'D:\cases\2423\sp_server_diagnostics_output.out',
   @flags=2
   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
   EXEC sp_start_job 'Run sp_server_diagnostics'
   

   Note

   Dans ces commandes, passez @output_file_name à un chemin d’accès valide et fournissez un nom de fichier.

Analyser les résultats

Lorsqu’une expiration du délai de connexion se produit, notez l’horodatage de l’événement d’expiration qui apparaît dans le journal d’erreurs de SQL Server. Pour les réplicas de l’exemple suivant, SQL19AGN1 indique les délais d’expiration de connexion des réplicas. Par conséquent, vous créez une tâche SQL Server Agent sur la réplique partenaire SQL19AGN2. Ensuite, une expiration du délai de connexion est signalée dans le journal des erreurs SQL19AGN1 à 07:24:31.

Ensuite, vérifiez la sortie de la tâche de l’Agent SQL qui exécute sp_server_diagnostics vers l’heure signalée. Plus précisément, examinez le point de données TrackingNonYieldingScheduler dans la sortie du composant query_processing. La sortie signale qu’un planificateur non réactif a été détecté (sous la forme d’une valeur hexadécimale non nulle) sur le serveur SQL19AGN2 (à 07:24:33), à peu près au moment où le délai d’expiration de la connexion du réplica a été signalé sur SQL19AGN1 (à 07:24:31).

Examiner un événement de planificateur sans rendement

Si les étapes de diagnostic précédentes vous ont permis de confirmer qu’un événement non coopératif a provoqué l’expiration du délai de connexion de la réplique, procédez comme suit.

1. Identifiez les charges de travail qui s’exécutent dans SQL Server au moment où les événements non générés se produisent.

2. Comme pour les délais d’expiration des connexions au réplica, recherchez les tendances de survenue de ces événements en fonction du mois, du jour ou de la semaine où ils se produisent.

3. Recueillez la trace du Moniteur de performances sur le système où l’événement de non-réponse a été détecté.

4. Collectez les principaux compteurs de performances des ressources système, notamment Processeur::% temps processeur, Mémoire::Mégaoctets disponibles, Disque logique::Longueur moyenne de la file d’attente du disque et Disque logique::Secondes moyennes/transfert.

5. Si nécessaire, ouvrez un incident de support SQL Server pour obtenir une aide supplémentaire dans la recherche de la cause racine de ces événements sans rendement. Partagez les logs que vous avez collectés pour une analyse approfondie.

Collecter une trace réseau pendant un délai d’expiration de connexion

Si le diagnostic précédent de l'application SQL Server ne génère pas de cause racine, vérifiez le réseau. Pour analyser correctement le réseau, vous devez collecter une trace réseau qui couvre l’heure du délai d’expiration de la connexion.

La procédure suivante démarre une trace réseau Windows netsh sur les réplicas pour lesquels des délais de connexion sont signalés dans les journaux d’erreurs de SQL Server. Une tâche planifiée Windows est déclenchée lorsque l’une des erreurs de connexion SQL Server est enregistrée dans le journal des applications. La tâche planifiée exécute une commande pour arrêter la netsh trace réseau afin que les données de trace réseau clés ne soient pas remplacées. Ces étapes présupposent également un chemin d’accès *F :* pour les journaux de traitement et de traçage. Adaptez ce chemin d’accès en fonction de votre environnement.

1. Démarrez une trace réseau sur les deux réplicas sur lesquelles des expirations du délai de connexion se produisent, comme indiqué dans l’extrait de code suivant.

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

2. Créez stoptrace.bat. Vous pouvez créer ce fichier depuis une invite de commandes avec privilèges administrateur.

   echo netsh trace stop > F:\stoptrace.bat
   

3. Créez des tâches planifiées Windows qui arrêtent le netsh traçage lors des événements 35206 ou 35267. Vous pouvez créer ces tâches à partir d’une invite de commandes avec privilèges administrateur.

   schtasks /Create /tn Event35206Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35206] /f /RL HIGHEST
   schtasks /Create /tn Event35267Task /tr F:\stoptrace.bat /SC ONEVENT /EC Application /MO *[System/EventID=35267] /f /RL HIGHEST
   

4. Une fois l’événement effectué et les traces réseau arrêtées et capturées, supprimez les ONEVENT tâches.

   schtasks /Delete /tn Event35206Task /F
   schtasks /Delete /tn Event35267Task /F
   

L’analyse de la trace réseau est en dehors de l’étendue de cet utilitaire de résolution des problèmes. Si vous ne pouvez pas interpréter la trace réseau, contactez l’équipe du support technique microsoft SQL Server et fournissez la trace avec d’autres fichiers journaux demandés pour l’analyse de la cause racine.

Atténuer les délais d’expiration de la connexion

Vous pouvez peut-être atténuer les délais d’expiration de connexion en ajustant la propriété SESSION_TIMEOUT du réplica du groupe de disponibilité. Ce paramètre s’applique à chaque réplica ; ajustez donc ce paramètre pour le réplica principal et pour chaque réplica secondaire concerné. La valeur par défaut est de 10 secondes. Vous pouvez donc essayer 15 secondes comme valeur suivante. Voici un exemple de syntaxe.

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

Contenu connexe

• Qu’est-ce qu’un groupe de disponibilité Always On ?
• Résoudre les problèmes de basculement des groupes de disponibilité Always On
• Le réplica de disponibilité d’un groupe de disponibilité est déconnecté
• Utiliser AGDiag pour diagnostiquer les événements d’état de santé du groupe de disponibilité