La réplication est un outil très puissant et plutôt facile à implémenter, mais il faut le reconnaitre, elle peut parfois poser des problèmes complexes à analyser et à résoudre. C’est pour l’un d’entre eux que j’ai été appelé récemment chez l’un de nos client, je vais le partager ici.

Description de l’incident

Nous sommes en présence d’une topologie de réplication très basique, dans laquelle un publisher (SQL Server 2014) envoi quelques tables en réplication transactionnelle sur un abonné (SQL Server) distant. Le distributeur est installé sur la même instance que le publisher, l’abonné est dans le même VLAN. Seule particularité, le publisher est membre d’un groupe de disponibilité AlwaysON, solution de HA/DR chez ce client. Rien de très complexe néanmoins, et pourtant…. il s’avère impossible d’ajouter une colonne dans une des tables publiées sur le publisher, l’erreur suivante apparaît systématiquement :
Msg 3933, Level 16, State 1, Line 1
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction
Et ce, quelque soit la table…

L’analyse

Ce n’est pas la première fois que je fais face à ce type d’erreur, aussi je commence par les points dont j’ai l’habitude. Et le message d’erreur parlant de « distributed query », le premier réflexe est effectivement de vérifier la configuration du composant Windows MS-DTC, qui gère justement les transactions distribuées. Mais rien à signaler de ce côté là, MS-DTC est correctement configuré sur le publisher et sur l’abonné :

Je teste également l’option server ‘remote proc transaction promotion‘, mais qui est plutôt utilisée dans le cadre de Linked Server, sans succès…
Mes soupçons se portent sur la partie AlwaysON : le problème vient peut-être du groupe de disponibilité. Je tente de reproduire le problème sur un autre environnement similaire à la production, avec une base de test nouvellement créée : aucun problème (je modifie sans difficulté les tables publiées). AlwaysON n’est à priori pas en cause, le problème est donc spécifique à l’environnement de production.
En creusant d’avantage, je m’aperçois que la base de données contient un trigger de niveau base de données, (DDL Trigger) dont le rôle est d’inscrire sur un serveur distant toutes les modifications de schéma (DDL) qui sont faites dans la base, à des fins d’audit. Ce trigger cherche donc à inscrire sur le serveur d’audit la tentative d’ajout de colonne dans la table publiée. Et la désactivation du trigger permet de résoudre le problème et d’ajouter (enfin !) la colonne à la table publiée :

Explication

La réplication est entièrement gérée par des procédures stockées, qui englobent les différentes instruction SQL dans des transactions spécifiques utilisant en particulier les ACTIVE SAVE POINT (SAVE TRANSACTION). Or la documentation nous apprend que les ACTIVE SAVE POINT sont incompatibles avec les transactions distribuées :
« L’instruction SAVE TRANSACTION n’est pas prise en charge dans les transactions distribuées démarrées soit explicitement à l’aide de BEGIN DISTRIBUTED TRANSACTION, soit issues d’une transaction locale. »  : https://docs.microsoft.com/fr-fr/sql/t-sql/language-elements/save-transaction-transact-sql
Et dans notre cas, le fait que le trigger aille écrire dans un serveur distant oblige la promotion de la transaction en transaction distribuée, incompatible avec le SAVE POINT.

Démonstration

L’idée est de suivre le déroulement des instructions T-SQL qui se déclenchent pendant l’ajout de la colonne publiée, et de mettre en évidence la création du SAVE POINT et de la transaction distribuée.
Le plus simple et rapide pour monitorer l’activité en direct reste à mon sens l’utilisation du Profiler, bien qu’il soit deprecated et de plus en plus remplacé par les XEvent, plus performants et complets.

Effectivement, le profiler met en évidence la création du SAVE POINT suivi quelque lignes plus bas du déclenchement du trigger qui lance l’écriture sur un serveur distant AVANT que l’Active Save Point n’ait été validé (COMMIT) : CQFD 😉
O.