Dans cet article, nous explorerons le concept de filtre de ligne paramétré de la réplication de fusion SQL Server et discuterons des problèmes qui peuvent survenir lors de la réplication des modifications de données incrémentielles après l’instantané initial.
Lors de la configuration de la réplication de fusion sur une base de données, il est courant d’utiliser des filtres de ligne paramétrés pour répliquer uniquement les lignes correspondant à une condition de filtre spécifique chez l’abonné. Cependant, lors de notre processus de validation, nous avons découvert que certains enregistrements ne correspondaient pas à la condition de filtre chez l’abonné.
Après une enquête plus approfondie, nous avons constaté que les filtres ne fonctionnaient pas correctement pour les modifications incrémentielles après l’instantané initial. Plus précisément, la condition de filtre ne fonctionnait pas pour les lignes modifiées dans la base de données de l’éditeur.
Examinons de plus près le fonctionnement des filtres dans la réplication de fusion et les raisons pour lesquelles ils peuvent ne pas répliquer les données attendues chez l’abonné.
Tout d’abord, nous devons comprendre la structure des bases de données de l’éditeur et de l’abonné. Dans notre exemple, nous avons une base de données de l’éditeur appelée “Test1” et une base de données de l’abonné appelée “Test2” avec une partition appelée “ORG1”.
Dans la base de données de l’éditeur, nous créons des tables d’exemple et les ajoutons à la réplication de fusion dans SQL Server. À des fins d’illustration, nous avons simplifié les tables et ajouté un déclencheur pour mettre à jour la valeur “UserOrg” si la ligne est insérée avec une valeur NULL “UserOrg” ou une valeur incorrecte “UserOrg”.
CREATE TABLE Users (
UserID INT,
UserOrg VARCHAR(50)
)
CREATE TABLE UserDetails (
UserID INT,
Add1 VARCHAR(50),
Add2 VARCHAR(50),
UserOrg VARCHAR(50)
)
CREATE TRIGGER TR_INS_UserDetails ON UserDetails FOR INSERT NOT FOR REPLICATION
AS
BEGIN
DECLARE @ParentOrg VARCHAR(50), @Userid INT
DECLARE UpdateOrg CURSOR FOR SELECT UserID FROM inserted
OPEN UpdateOrg
FETCH NEXT FROM UpdateOrg INTO @Userid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ParentOrg = UserOrg FROM Users WHERE userid = @Userid
UPDATE UserDetails SET UserOrg = @ParentOrg WHERE userid = @Userid
FETCH NEXT FROM UpdateOrg INTO @Userid
END
CLOSE UpdateOrg
DEALLOCATE UpdateOrg
END
INSERT INTO Users VALUES (1, 'ORG1')
INSERT INTO Users VALUES (2, 'ORG1')
INSERT INTO Users VALUES (3, 'ORG1')
INSERT INTO Users VALUES (4, 'ORG2')
INSERT INTO Users VALUES (5, 'ORG2')
INSERT INTO UserDetails VALUES (1, 'Lane1', 'Lane2', 'ORG1')
INSERT INTO UserDetails VALUES (2, 'Lane1', 'Lane2', 'ORG1')
INSERT INTO UserDetails VALUES (3, 'Lane1', 'Lane2', 'ORG1')
INSERT INTO UserDetails VALUES (4, 'Lane1', 'Lane2', 'ORG2')
INSERT INTO UserDetails VALUES (5, 'Lane1', 'Lane2', 'ORG2')
Ensuite, nous ajoutons ces tables à la réplication de fusion avec des filtres de ligne paramétrés. Nous suivons les étapes décrites dans l’article de référence pour configurer la publication de fusion et ajouter les tables à la publication.
À l’étape “Ajouter un filtre”, nous spécifions la condition de filtre pour chaque table. Par exemple, nous utilisons la condition de filtre suivante pour la table “UserDetails” :
SELECT <published_columns> FROM [dbo].[UserDetails] WHERE [UserOrg] = HOST_NAME()
Une fois la configuration de la publication terminée, nous générons l’instantané pour la réplication de fusion. Après avoir généré l’instantané, nous accédons à la publication, cliquons avec le bouton droit de la souris et cliquons sur “Propriétés” -> “Partitions de données”. Ici, nous ajoutons la valeur de partition “ORG1” et générons l’instantané sélectionné.
Maintenant, nous ajoutons l’abonné en spécifiant la valeur HOST_NAME() de l’abonné. Cela garantit que seules les lignes avec une valeur “UserOrg” de “ORG1” seront répliquées chez l’abonné. Après avoir ajouté l’abonné, nous appliquons l’instantané initial chez l’abonné.
À ce stade, nous pouvons voir que les lignes avec une valeur “UserOrg” de “ORG1” ont été répliquées avec succès chez l’abonné avec la partition “ORG1”.
Maintenant, plongeons dans les tables internes utilisées pour les filtres de partition de données dans la réplication de fusion.
Lorsque l’option “Précalculer les partitions” est activée dans les options d’abonnement dans les propriétés de la publication, toutes les insertions, mises à jour et suppressions sont évaluées pour la condition de filtre au moment des modifications, et les métadonnées sont enregistrées dans les tables internes suivantes :
- sysmergepartitioninfo
- MSmerge_partition_groups
- MSmerge_current_partition_mappings
- MSmerge_past_partition_mappings
La table “sysmergepartitioninfo” stocke des informations sur les règles de partition pour chaque table ayant un filtre de ligne paramétré dans la réplication de fusion.
La table “MSmerge_partition_groups” stocke une ligne pour chaque partition précalculée. Dans notre cas, nous aurons une ligne pour la partition “ORG1”. Si de nouvelles partitions de données sont ajoutées, des entrées liées à la partition sont également insérées dans cette table.
Lorsqu’une table est ajoutée à la réplication de fusion, trois déclencheurs système sont créés : un pour l’insertion, un pour la mise à jour et un pour la suppression. Ces déclencheurs suivent toutes les insertions, suppressions et mises à jour sur la base de données répliquée et stockent les informations dans les tables internes de la réplication de fusion.
Si l’option “Précalculer les partitions” est activée, le déclencheur “MSmerge insert” est déclenché lorsque des données sont insérées dans la table. Ce déclencheur évalue la condition de filtre et stocke les informations dans la table “MSmerge_current_partition_mappings”. Cette table contient des informations sur quelle ligne appartient à quelle partition. Lorsque l’abonné se synchronise avec l’éditeur, il récupère les informations de cette table, qui ont déjà été précalculées, et commence à télécharger les modifications directement au lieu d’évaluer la condition de filtre lors de la synchronisation.
Si l’option “Précalculer les partitions” est désactivée, l’abonné évalue la condition de filtre lors de la synchronisation, et ce processus doit être répété pour chaque abonné qui se synchronise avec l’éditeur. Par défaut, les partitions précalculées sont activées.
Insérons quelques lignes dans ces tables et vérifions les correspondances de rowguid dans la table “MSmerge_current_partition_mappings” :
INSERT INTO Users (UserID, UserOrg) VALUES (6, 'ORG1')
INSERT INTO UserDetails (UserID, Add1, Add2, UserOrg) VALUES (6, 'Lane1', 'Lane2', 'ORG1')
Nous pouvons voir que deux entrées ont été ajoutées dans la table “MSmerge_current_partition_mappings”, faisant correspondre les rowguids des lignes insérées avec la partition “ORG1”.
Maintenant, insérons quelques lignes liées à “ORG2” et vérifions les correspondances de rowguid dans la table “MSmerge_current_partition_mappings” :
INSERT INTO Users (UserID, UserOrg) VALUES (7, 'ORG2')
INSERT INTO UserDetails (UserID, Add1, Add2, UserOrg) VALUES (7, 'Lane1', 'Lane2', 'ORG2')
Nous pouvons voir qu’il n’y a aucune entrée dans la table pour les lignes ci-dessus car nous n’avons pas spécifié la partition de données pour “ORG2” dans les propriétés de la publication.
Ensuite, mettons à jour le “UserOrg” de l’UserID 6 et vérifions les données dans les tables “MSmerge_current_partition_mappings” et “MSmerge_past_partition_mappings” :
UPDATE Users SET UserOrg = 'ORG2' WHERE UserID = 6
UPDATE UserDetails SET UserOrg = 'ORG2' WHERE UserID = 6
Lorsque nous mettons à jour les données d’une ligne existante, les conditions de filtre sont évaluées dans le déclencheur “MSmerge update” interne de la réplication de fusion. Ce déclencheur supprime les informations de mappage de partition précédentes dans la table “MSmerge_current_partition_mappings” et insère les données de mappage mises à jour. Par exemple, si nous mettons à jour le “UserOrg” de l’UserID 6 en “ORG2”, les informations de partition existantes sont supprimées et les anciennes données de partition sont déplacées vers la table “MSmerge_past_partition_mappings”.
Enfin, testons les filtres en insérant des valeurs incorrectes ou NULL pour “UserOrg” dans la table “UserDetails” :
INSERT INTO Users (UserID, UserOrg) VALUES (12, 'ORG2')
INSERT INTO UserDetails (UserID, Add1, Add2, UserOrg) VALUES (12, 'Lane1', 'Lane2', 'ORG1')
Le déclencheur “TR_INS_UserDetails” sur la table “UserDetails” est déclenché et met à jour correctement la valeur “UserOrg”. Par conséquent, la ligne avec l’UserID 12 aura “UserOrg” comme “ORG2”. Cependant, cette ligne n’est pas répliquée chez l’abonné selon la condition de filtre dans la réplication de fusion.
Il est important de noter l’ordre d’exécution du déclencheur utilisateur et du déclencheur “MSmerge insert” dans le cas de la table “UserDetails”. Si le déclencheur “MSmerge insert” est déclenché en premier, la séquence des étapes qui se produisent ensuite est la suivante :
- Le déclencheur “MSmerge insert” est déclenché en premier et fait correspondre le rowguid avec la partition “ORG1” dans la table “MSmerge_current_partition_mappings”.
- Le déclencheur “TR_INS_UserDetails” est déclenché, ce qui met à jour “UserOrg” en “ORG2”.
- Le déclencheur “MSmerge update” est déclenché et supprime les informations de mappage de partition précédentes dans la table “MSmerge_current_partition_mappings” et insère le mappage avec “ORG2” si la partition “ORG2” existe.
- Lorsqu’un abonné avec le nom d’hôte “ORG1” se synchronise avec l’éditeur, la ligne avec l’UserID 12 n’est pas répliquée.
Dans certains cas, le déclencheur “TR_INS_UserDetails” peut être déclenché en premier, suivi du déclencheur “MSmerge insert”. Cela peut entraîner des résultats de réplication inattendus. Pour illustrer ce scénario, nous pouvons forcer le déclencheur utilisateur à être déclenché en premier en exécutant le script suivant sur la base de données de l’éditeur :
EXEC sys.sp_settriggerorder @triggername = 'TR_INS_UserDetails', @order = 'FIRST', @stmttype = 'INSERT', @namespace = NULL
En comprenant les subtilités du filtre de ligne paramétré de la réplication de fusion SQL Server et des tables internes utilisées pour les filtres de partition de données, nous pouvons résoudre efficacement les problèmes qui peuvent survenir lors de la réplication.
N’oubliez pas de prêter attention à l’ordre d’exécution des déclencheurs, en particulier lors de la mise à jour des colonnes utilisées dans les conditions de filtre à l’aide de déclencheurs. De plus, assurez-vous que la table “MSmerge_current_partition_mappings” est remplie avec les correspondances correctes entre les rowguids et les partitions dans la réplication de fusion.
En suivant ces meilleures pratiques, vous pouvez garantir la réplication réussie des données dans la réplication de fusion SQL Server.