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

Cet article vous aide à diagnostiquer les délais d’attente de connexion intermittents signalés entre les réplicas de groupe de disponibilité.

Symptômes et effets des délais d’attente de connexion de réplica de groupe de disponibilité intermittents

L’interrogation des réplicas principaux et secondaires retourne 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 délais de connexion de réplica intermittents se produisent, les modifications apportées aux données sur la base de données réplica principale ne sont pas encore reflétées dans la base de données secondaire lorsque vous interrogez les mêmes données. Pour plus d’informations, consultez la section Latence des données sur le réplica secondaire.

Groupe de disponibilité du rapport de diagnostic non synchronisé

Le tableau de bord Always On dans SQL Server Management Studio peut signaler un groupe de disponibilité non sain qui a des réplicas à afficher dans un état non synchronisant . Vous pouvez également observer les réplicas de rapport du tableau de bord Always On dans l’état Non synchronisant .

Lorsque vous passez en revue les journaux d’erreurs SQL Server de ces réplicas, vous pouvez observer des messages tels que les suivants qui indiquent qu’il y a eu un délai de connexion entre les réplicas dans le groupe de disponibilité :

Journal des erreurs à partir du réplica principal

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.

Journal des erreurs à partir du réplica 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.

Les problèmes de connexion intermittentes peuvent affecter la préparation du 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 est déconnecté par intermittence du serveur principal, le basculement automatique peut échouer.

Vous pouvez interroger sys.dm_hadr_database_replia_cluster_states pour déterminer si la base de données du groupe de disponibilité est prête pour le basculement à ce moment-là. Voici un exemple des résultats si le point de terminaison de mise en miroir a été arrêté sur le réplica 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 un délai d’expiration de connexion de réplica.

Quelles sont les erreurs de délai d’attente de connexion qui indiquent ?

La valeur par défaut est de 10 secondes pour le paramètre de réplica du groupe de disponibilité. SESSION_TIMEOUT Ce paramètre est configuré pour chaque réplica. Il détermine la durée pendant laquelle le réplica attend la réception d’une réponse de son réplica partenaire avant qu’il signale un délai d’attente de connexion. Si un réplica n’obtient aucune réponse du réplica partenaire, il signale un délai de connexion dans le journal des erreurs Microsoft SQL Server et le journal des applications Windows. Le réplica qui signale le délai d’attente tente immédiatement de se reconnecter et continue d’essayer toutes les cinq secondes.

En règle générale, le délai d’expiration de la connexion est détecté et signalé par un seul réplica. Toutefois, le délai d’expiration de la connexion peut être signalé par les deux réplicas en même temps. Il existe différentes versions de ce message, selon que le délai d’expiration de la connexion s’est produit à l’aide d’une connexion établie précédemment ou d’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’attente. 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’attente 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, le réplica principal n’a détecté aucun délai d’expiration de connexion, car il pouvait toujours communiquer avec le serveur secondaire, et il a signalé le message 35267 pour chaque base de données de groupe de disponibilité (dans cet exemple, 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 peut être occupé pour plusieurs raisons et ne traite pas la connexion de point de terminaison de mise en miroir au cours de la période du groupe SESSION_TIMEOUT de disponibilité. Cela provoque l’expiration du délai de connexion. Voici quelques-unes des raisons suivantes :

• SQL Server rencontre 100 % d’utilisation du processeur. Cela signifie que SQL Server ou une autre application pilote le processeur pendant des secondes à la fois.

• SQL Server rencontre des événements de planificateur sans rendement. Les threads SQL Server sont responsables du rendement du planificateur (PROCESSEUR) à d’autres threads pour terminer leur travail si un thread ne génère pas en temps voulu.

• 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

Cela nécessite que vous collectiez les journaux de suivi réseau sur les réplicas principaux et secondaires lorsque l’erreur est déclenchée. Pour ce faire, vous pouvez examiner la latence réseau et les paquets supprimés.

Guide pratique pour diagnostiquer les délais d’attente de connexion du réplica

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

Évaluer le minutage et l’emplacement des délais d’expiration de connexion du réplica

Passez en revue l’historique, la fréquence et les tendances des délais d’attente de connexion. L’utilisation des messages que vous trouvez dans le journal des erreurs SQL Server est un excellent moyen de le faire. Où sont signalés les délais d’attente de connexion ? Sont-ils signalés de manière cohérente sur le réplica principal ou le réplica 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’attente de connexion sont observés ? Cette évaluation peut vous aider à étendre et à mettre en corrélation les délais d’attente de connexion pour identifier la cause racine.

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

La AlwaysOn_health session d’événements étendue 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 partenaire. Cela peut être utile lors de la résolution des problèmes de délai d’attente de connexion.

Note

L’événement ucs_connection_setup étendu a été ajouté aux dernières mises à jour cumulatives SQL Server. Vous devez exécuter les dernières mises à jour cumulatives pour observer cet événement étendu.

Interroger des vues de gestion distribuée Always On (DMV)

Vous pouvez interroger les vues de gestion dynamique Always On pour 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 de 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é soutenu, car le point de terminaison de mise en miroir sur le réplica principal a été arrêté. En interrogeant le réplica principal, la DMV Always On peut signaler sur le réplica principal et tous les réplicas secondaires (le point de terminaison est désactivé sur le réplica principal).

En interrogeant le réplica secondaire, les vues de gestion dynamique Always On indiquent uniquement le réplica secondaire.

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

1. Connectez-vous à chaque réplica à l’aide de l’Explorateur d’objets SQL Server Management Studio (SSMS) et ouvrez les AlwaysOn_health fichiers d’événements étendus.

2. Dans SSMS, accédez à Fichier>Ouvert, 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. Appuyez sur Ctrl, puis sélectionnez les fichiers dont le nom commence par « AlwaysOn_healthxxx.xel ».

6. Sélectionnez Ouvrir, puis OK.

   Vous devez voir une nouvelle fenêtre à onglets dans SSMS qui affiche les événements AlwaysOn.

   La capture d’écran suivante montre les AlwaysOn_health données du réplica secondaire. La première zone décrite montre la perte de connexion après l’arrêt du point de terminaison sur le réplica principal. La deuxième zone décrite montre l’échec de connexion qui se produit la prochaine fois que le réplica secondaire tente de se connecter au réplica principal.

   

Vérifiez si les événements sans rendement provoquent des expirations de délai d’attente 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 effectue le suivi des événements du planificateur sans rendement qui sont aussi courts que 5 à 10 secondes. Il signale ces événements dans le TrackingNonYieldingScheduler point de données dans la sortie du sp_server_diagnostics query_processing composant.

Pour rechercher les événements sans rendement susceptibles d’entraîner des délais d’expiration de connexion de réplica, 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’attente de connexion. Autrement dit, si le serveur A signale le délai d’expiration de la connexion du réplica dans son journal des erreurs, configurez le travail SQL Agent sur le réplica partenaire, le serveur B. Sinon, si vous voyez des délais de connexion sur les deux réplicas, créez le travail sur les deux réplicas.

3. Exécutez le fichier de commandes 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. La commande de l’exemple suivant s’exécute sp_server_diagnostics 5 toutes les cinq secondes. Par conséquent, il n’est pas nécessaire de planifier l’exécution de ce travail toutes les cinq secondes, il suffit de démarrer le travail et il s’exécute jusqu’à ce qu’il s’arrête, toutes les cinq secondes :

   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’un délai d’attente de connexion est signalé, notez l’horodatage de l’événement de délai d’attente affiché dans le journal des erreurs SQL Server. Pour les réplicas de l’exemple suivant, SQL19AGN1 signalait les délais d’expiration de la connexion du réplica. Par conséquent, un travail SQL Agent a été créé sur SQL19AGN2, le réplica partenaire. Ensuite, un délai d’attente de connexion a été signalé dans le SQL19AGN1 journal des erreurs à 07:24:31.

Ensuite, la sortie du travail SQL Agent qui s’exécute sp_server_diagnostics est vérifiée à l’heure signalée, en examinant spécifiquement le TrackingNonYieldingScheduler point de données dans la sortie du query_processing composant. La sortie indique qu’un planificateur sans rendement a été suivi (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’attente de connexion du réplica a été signalé sur SQL19AGN1 (à 07:24:31).

Note

La sortie suivante sp_server_diagnostics est concaténée pour afficher à la fois l’horodatage et query_processing TrackingNonYieldingScheduler les create_time résultats.

Examiner un événement de planificateur sans rendement

Si vous avez vérifié à partir des étapes de diagnostic antérieures qu’un événement sans rendement a provoqué l’expiration du délai de connexion du réplica :

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

2. À l’instar des délais d’attente de connexion du réplica, recherchez les tendances de ces événements au cours du mois, du jour ou de la semaine qu’ils se produisent.

3. Collectez le suivi de l’analyseur de performances sur le système sur lequel l’événement sans rendement a été détecté.

4. Collectez les compteurs de performances clés pour les ressources système, notamment le processeur ::% le temps processeur, la mémoire ::Octets Moctets disponibles, la longueur de file d’attente de disque moyenne et le disque logique ::Disque avg sec/transfert.

5. S’il est nécessaire, ouvrez un incident de support SQL Server pour obtenir une assistance supplémentaire pour trouver la cause racine de ces événements sans rendement. Partagez les journaux que vous avez collectés pour une analyse plus approfondie.

Collecte avancée des données : collecter la trace réseau pendant le délai d’attente de connexion

Si le diagnostic précédent de l’application SQL Server n’a pas généré de cause racine, vous devez vérifier le réseau. L’analyse réussie du réseau nécessite que vous collectiez une trace réseau qui couvre le temps d’expiration de la connexion.

La procédure suivante démarre un suivi réseau Windows netsh sur les réplicas sur lesquels les délais de connexion sont signalés dans les journaux d’erreurs SQL Server. Une tâche d’événement 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 supposent également un chemin d’accès de *F :* pour les journaux de traitement et de suivi. Ajustez ce chemin d’accès à votre environnement.

1. Démarrez une trace réseau, comme indiqué dans l’extrait de code suivant, sur les deux réplicas sur lesquels les délais de connexion se produisent :

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

2. Créez des tâches planifiées Windows qui arrêtent la netsh trace sur les événements 35206 ou 35267. Vous pouvez créer ces tâches sur une ligne de commande administrative :

   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
   

3. Une fois que l’événement se produit et que les traces réseau sont arrêtées et capturées, vous pouvez supprimer les ONEVENT tâches :

   PS C:\Users\sqladmin> Schtasks /Delete /tn Event35206Task /F
   PS C:\Users\sqladmin> 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.

Que puis-je faire d’autre pour atténuer les délais d’attente de connexion ?

Le groupe de disponibilité par défaut, est SESSION_TIMEOUTconfiguré pendant 10 secondes. Vous pouvez peut-être atténuer les délais d’attente de connexion en ajustant la propriété de réplica SESSION_TIMEOUT du groupe de disponibilité. Ce paramètre est par réplica. Ajustez-le pour le réplica principal et chaque réplica secondaire affecté. Voici un exemple de syntaxe. La valeur par défaut SESSION_TIMEOUT est 10. Par conséquent, vous pouvez utiliser 15 comme valeur suivante.

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