Pourquoi votre validation SQL synchrone attend l'aller-retour VPN

La latence d'écriture a bondi après le déplacement d'une réplique vers le second site, et le tunnel est le composant le plus récent, alors il prend le blâme. Le tunnel peut être innocent : dans un groupe de disponibilité à validation synchrone, le primaire ne peut accuser réception d'une validation tant qu'une écriture de journal distante n'est pas terminée, si bien que chaque transaction paie un aller-retour, quel que soit le nombre de mégabits que la liaison peut transporter. Le débit mesure les octets par seconde, la latence mesure la durée d'un échange, et une validation n'a besoin que d'un petit échange pour aboutir. Un seul chiffre tranche le débat : la durée de validation d'une petite transaction, mesurée dans la base de disponibilité puis de nouveau face à une table locale sur la même instance.

Ce que le primaire attend réellement

Sous validation synchrone, le primaire retient l'accusé de validation jusqu'à ce que le partenaire confirme que ses enregistrements de journal sont durcis. Durci signifie écrit durablement dans le journal des transactions du secondaire, non rejoué dans ses pages de données. Le redo peut prendre des minutes de retard sans ajouter une seule milliseconde à la validation du primaire.

La documentation Microsoft sur les modes de disponibilité décrit cette poignée de main et ce que fait la plateforme lorsqu'un partenaire décroche. Une réplique marquée synchrone est une règle sur l'attente, pas une promesse permanente qu'une copie distante saine existe pour chaque validation. Le chemin déconnecté est défini séparément, et ce n'est pas simplement que la validation échoue.

L'attente que vous observez est donc une somme : transport du journal, ordonnancement sur les deux hôtes et écriture sur le stockage distant. Un secondaire dont le volume de journal est saturé ajoute un temps qu'aucun test de vitesse ne montrera. Ce disque est rarement mesuré quand la plainte nomme le VPN.

La bande passante n'est pas un délai d'aller-retour

Un test de vitesse inonde brièvement le chemin et rapporte un débit. Une validation demande quelque chose de plus étroit : combien de temps un petit message met à atteindre l'autre côté, à être persisté et à revenir. La longueur du chemin, les files d'attente et la perte de paquets fixent ce chiffre, et aucun d'eux ne s'améliore quand le fournisseur relève le plafond.

Un tunnel « rapide » peut donc ajouter une pénalité visible à une charge de travail gourmande en écriture faite de nombreuses petites transactions séquentielles. Le même total d'octets réparti sur mille validations paie l'aller-retour mille fois ; un transfert en masse le paie une fois.

Demandez au propriétaire du réseau le délai et la perte mesurés sur la route de la réplique elle-même sous une charge représentative. Un ping depuis un portable de bureau vers la passerelle VPN, ou un test de vitesse vers un nœud proche, n'attribue presque rien à la route que le journal emprunte réellement. Faites coïncider la fenêtre de mesure avec les heures où l'écriture est lente.

Lisez l'état de la réplique avant de changer quoi que ce soit

Partez de ce que les instances rapportent d'elles-mêmes, car les deux côtés peuvent être en désaccord sur la santé du partenariat et une moyenne de tableau de bord cache quelle file avance. Les requêtes ci-dessous répondent à des questions différentes : l'une lit l'état de réplication par base de données, l'autre lit la configuration de chaque réplique. Exécutez les deux avant de toucher à un mode.

SELECT ag.name AS ag_name, drs.database_id, drs.synchronization_state_desc,
       drs.log_send_queue_size, drs.redo_queue_size, drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id;

SELECT ar.replica_server_name, ar.availability_mode_desc, ar.failover_mode_desc
FROM sys.availability_replicas AS ar;

Le premier résultat sépare le transport du journal du redo. log_send_queue_size qui monte tandis que redo_queue_size reste plat signifie que le journal n'est pas arrivé ; l'inverse signifie qu'il est arrivé et que la relecture est en retard. Seul le premier cas se trouve sur le chemin de la validation.

Le second résultat confirme quelles répliques sont réellement configurées comme vous le supposez, car un mode modifié lors d'un incident antérieur a tendance à rester modifié et non documenté. Lisez la ligne qui vous intéresse plutôt que la valeur par défaut du groupe : availability_mode_desc et failover_mode_desc sont par réplique. secondary_lag_seconds n'existe que sur SQL Server 2016 et ultérieur, donc une version plus ancienne ne vous laisse que les colonnes de file d'envoi et de redo.

Chronométrez ensuite une écriture représentative au lieu de vous fier à une moyenne de tableau de bord. Exécutez le bloc ci-dessous dans la base de disponibilité, puis exécutez-le de nouveau contre la même forme de table dans une base hors du groupe sur la même instance. L'écart entre les deux durées est un prix approximatif du journal distant, et il tient bien mieux face à l'équipe réseau qu'un graphique. Changez ensuite une variable à la fois, sinon l'écart ne veut plus rien dire.

IF OBJECT_ID('dbo.CommitProbe') IS NULL
    CREATE TABLE dbo.CommitProbe (probe_id int PRIMARY KEY, touched_at datetime2(3));
IF NOT EXISTS (SELECT 1 FROM dbo.CommitProbe WHERE probe_id = 1)
    INSERT INTO dbo.CommitProbe (probe_id) VALUES (1);
SET STATISTICS TIME ON;
BEGIN TRANSACTION;
UPDATE dbo.CommitProbe SET touched_at = SYSUTCDATETIME() WHERE probe_id = 1;
COMMIT TRANSACTION;
SET STATISTICS TIME OFF;

Ce que la validation asynchrone achète et ce qu'elle coûte

Avec la validation asynchrone, le primaire cesse d'attendre la confirmation du durcissement distant, si bien que l'aller-retour quitte le chemin de la validation. La latence de validation baisse et le secondaire est libre de prendre du retard dans une mesure que personne ne contrôle. C'est un vrai correctif pour l'attente et un vrai changement de la promesse.

Le prix est une décision sur le point de récupération. Si le primaire meurt avant que son journal n'atteigne une cible de basculement que vous pouvez réellement utiliser, pousser le service sur la copie asynchrone jette des transactions que l'application a déjà signalées comme validées. Aucune boîte de dialogue de configuration ne le présente ainsi, car c'est une question métier sur la perte acceptable, pas un réglage de performance. Écrivez la fenêtre de perte tolérée avant l'incident, pas pendant.

Convenez du mode avec le propriétaire des données, par écrit, avec la procédure de basculement pour le cas où la cible préférée est indisponible. Le synchrone dans un document de conception n'est pas un manuel d'exploitation, et l'asynchrone n'est pas une licence pour cesser de surveiller les files.

Choisissez un correctif que vous pourrez défendre ensuite

Il existe quatre options honnêtes. Coupez les allers-retours de validation évitables là où la sémantique de l'application permet le regroupement ; corrigez un goulot d'étranglement de transport ou de stockage que vous avez mesuré, ce qui peut signifier un volume de journal plus rapide sur le secondaire plutôt qu'un tuyau plus gros ; rapprochez la réplique pour que l'aller-retour se réduise ; ou assouplissez la politique de protection et acceptez une fenêtre de perte plus large. Chacune achète de la latence en dépensant autre chose, et la quatrième dépense la durabilité. Nommez la dépense avant de choisir.

Décidez avec l'objectif de latence de la charge de travail et son exigence de durabilité devant vous en même temps, car optimiser l'un dépense l'autre en silence. Réexécutez ensuite la même transaction que vous avez mesurée avant le changement. Un correctif qui ne peut pas bouger ce chiffre est une supposition portant un ticket de changement.

Conservez les relevés avant et après dans l'enregistrement du changement. Sinon, la personne suivante qui voit des écritures lentes refait l'enquête depuis le début et recommence par blâmer le tunnel.