In this article we will discuss about SQL Server Merge Replication Parameterized row filter issues while replicating incremental data changes post initial snapshot.
We had merge replication configured on one of our database and used parameterized row filters to replicate rows that match the filter condition of the subscriber. While validating data on the subscribers we found that there were a few records that did not match the filter condition. First, we looked at bulk copy files in snapshot. The data looked normal in snapshot files and matched with the filter condition.
So, we determined that the filters did not work properly only on incremental changes after snapshot and this filter condition did not work for the rows that were modified at publisher database. Let us see how filters work in merge replication and why they got replicated to the subscriber even though the filter condition did not match.
Below are my publisher and subscriber databases.
Test1: Publisher database
Test2: Subscriber database with partition ORG1
On the publisher database, let us create sample tables and add these tables to merge replication in SQL Server.
- Note: This is to just illustrate the scenario. actual tables are different in structure with lot of columns and a trigger to update Org value if the row is inserted with NULL UserOrg or wrong UserOrg.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
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') |
Now add these tables to merge replication with parametrized row filters. Follow the steps in the referenced article to configure merge publication and adding tables to the publication: SQL Server Replication (Merge) – What gets replicated and what doesn’t
At Add filter step add below filter.
|
1 |
SELECT <published_columns> FROM [dbo].[UserDetails] WHERE [UserOrg] = HOST_NAME() |
Similarly add the filter for user tables as well.
|
1 |
SELECT <published_columns> FROM [dbo].[Users] WHERE [UserOrg] =HOST_NAME() |
Once the publication setup is completed, generate the snapshot for merge replication. After generating the snapshot navigate to publication, right click and click on Properties -> Data Partitions
Click on Add and enter the partition value i.e. ORG1 and click ok. As of now I am adding only one partition ORG1. We can add more based on subscribers. For example, if I have a subscriber with partition ORG2 then we can add partition for ORG2 here and generate a partitioned snapshot which will be applied on specific subscriber.
Now click on generate the selected snapshot now and click OK.
Right click on publication in merge replication, launch SQL Server replication monitor and make sure the partitioned snapshot is also completed.
Now add subscription. Please follow steps in the following article to add subscriber: Merge Replication – What gets replicated and what doesn’t
While adding the subscriber specify the HOST_NAME() value of the subscriber, so that the rows with UserOrg value ORG1 only will be replicated to the subscriber.
After adding the subscriber in merge replication, apply the initial snapshot on subscriber. We can see the row with org value ORG1 got replicated to subscriber with partition ORG1.
Now let us discuss internal tables used for data partitions filters in Merge Replication.
When precompute partitions is set to true on subscription options in publication properties, all the inserts, updates and deletes will be evaluated for filter condition at the time of changes and meta data is saved in internal tables listed below.
- sysmergepartitioninfo
- MSmerge_partition_groups
- MSmerge_current_partition_mappings
- MSmerge_past_partition_mappings
Sysmergepartitioninfo: This table stores the information of partition rules for each table which has parametrized row filter in merge replication.
MSmerge_partition_groups: This table stores one row for each pre-computed partition i.e. In our case we will have one row for ORG1 partition. If any new data partitions were added then entries related to partition are also inserted in this table.
As we know when a table is added to merge replication three system triggers will be created. One for insert, one for update and one for delete. All the inserts, deletes and updates on the replicated database will be tracked by these triggers and store the information in merge SQL Server replication internal tables.
Pre-compute Enabled in merge replication
Now when data is inserted into the table, the Msmerge insert trigger is fired which evaluates the filter condition and store the information in MSmerge_current_partition_mappings table. i.e. this table maintains the information of which row belongs to which partitions. So that when the subscriber syncs with publisher it will get the information from this table which is already pre- computed and starts downloading changes directly instead of evaluating the filter condition while syncing.
Pre-compute Disabled in merge replication
If pre-compute partitions option is disabled the subscriber evaluates the filter condition while syncing and this process must be repeated for every subscriber that synchronizes with the publisher.
Pre-computed partitions are enabled by default. Let us insert a few rows into these tables and check the rowguid mappings in MSmerge_current_partition_mappings table.
|
1 2 3 |
INSERT INTO Users (UserID,UserOrg) VALUES (6,'ORG1') INSERT INTO UserDetails (UserID,Add1,Add2,UserOrg) VALUES (6,'Lane1','Lane2','ORG1') |
Now we can see two entries in MSmerge_current_partition_mappings with rowguid’s of inserted rows mapped with ORG1.
Insert few rows which are related to ORG2 and check for the rowguid mappings in MSmerge_current_partition_mappings table in merge replication.
|
1 2 3 |
INSERT INTO Users (UserID,UserOrg) VALUES (7,'ORG2') INSERT INTO UserDetails (UserID,Add1,Add2,UserOrg) VALUES (7,'Lane1','Lane2','ORG2') |
We can see there were no entries in table for the above rows as we have not specified the data partition for ORG2 in publication properties.
Now let us update the UserOrg of UserID 6 and check the data in the MSmerge_current_partition_mappings and MSmerge_past_partition_mappings
If we update data of an existing row, the filter conditions are evaluated in merge replication internal MSmerge update trigger and delete the entries for existing partition in MSmerge_current_partition_mappings and insert updated mapping data in MSmerge_current_partition_mappings.
For example, if we update the UserOrg of Userid 6 to ORG2 then the existing partition information from MSmerge_current_partition_mappings is deleted and move the old partition data to MSmerge_past_partition_mappings table.
|
1 2 |
UPDATE Users set UserOrg = 'ORG2' WHERE UserID =6 UPDATE Users set UserDetails = 'ORG2' WHERE UserID =6 |
Now we will check the filters by inserting wrong org values or NULL into the UserDetails table.
|
1 2 3 |
INSERT INTO Users (UserID,UserOrg) VALUES (12,'ORG2') INSERT INTO UserDetails (UserID,Add1,Add2,UserOrg) VALUES (12,'Lane1','Lane2','ORG1') |
The trigger TR_INS_UserDetails on UserDetails table is fired and updates the UserOrg value correctly.
So, the UserOrg for UserID 12 will be ORG2 as the trigger updated correct org value while inserting data and the row is not replicated to subscriber as per filter condition in Merge Replication.
Here the execution order of user trigger and MSmerge insert trigger is very important in case of UserDetails. If the SQL Server replication MSmerge insert trigger is fired first, the sequence of steps that happens next is as follows:
- The MSmerge insert trigger is fired first and maps the rowguid with ORG1 partition in MSmerge_current_partition_mappings
- TR_INS_UserDetails is fired which updates UserOrg to ORG2. Here MSmerge update trigger is fired and deletes the previous partition mapping information in MSmerge_current_partition_mappings and insert mapping with ORG2 if ORG2 partition exist
- Now when subscriber with hostname ORG1 syncs with publisher then row with UserID 12 is not replicated
Failure case for Merge Replication
In my case somehow the trigger TR_INS_UserDetails is fired first and MSmerge insert trigger is fired later.
Let us illustrate the same scenario by forcing the user trigger to fire first. On publisher database execute below script to force the user trigger to fire first.
|
1 2 3 4 |
EXEC sys.sp_settriggerorder @triggername = 'TR_INS_UserDetails', @order = 'FIRST', @stmttype = 'INSERT', @namespace = NULL |
Below are the steps that happen when user trigger is fired first.
|
1 2 3 |
INSERT INTO Users (UserID,UserOrg) VALUES (13,'ORG2') INSERT INTO UserDetails (UserID,Add1,Add2,UserOrg) VALUES (13,'Lane1','Lane2','ORG1') |
- The user trigger is fired first and updates UserOrg with correct value i.e. ORG2. Here the MSmerge update trigger is also fired and check for existing partition and does not find any partition as the MSmerge insert trigger is not fired yet. So, it just maps the rowguid with ORG2 partition if partition for ORG2 exist
- The MSmerge insert trigger is fired later which maps the rowguid with old org value i.e. with ORG1
- When a subscriber syncs with the publisher it gets information from the MSmerge_current_partition_mappings table and the row in UserDetails table with UserID 13 got replicated to subscriber with partition ORG1 even though the filter condition does not match
Please check the following:
- That the execution order of triggers in case if you are updating the column used in filter condition using triggers and make sure the MSmerge_current_partition_mappings table is being filled with correct mappings between rowguid’s and partitions in Merge Replication
- Database collation is used rather than the table collation when evaluating filter condition
Table of contents
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019