Ranga Babu

Merge SQL Server replication parameterized row filter issues

February 19, 2019 by

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.

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.

ADD Filter in Merge Replication

Similarly add the filter for user tables as well.

Filter statements and condtions

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

Publication Properties

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.

Add data partition in merge replication

Now click on generate the selected snapshot now and click OK.

Snapshot properties

Right click on publication in merge replication, launch SQL Server replication monitor and make sure the partitioned snapshot is also completed.

replication Monitor

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.

New Subscription

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.

Data verification

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.

subscription filtering options

  • 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.

Now we can see two entries in MSmerge_current_partition_mappings with rowguid’s of inserted rows mapped with ORG1.

GUID mappings

Insert few rows which are related to ORG2 and check for the rowguid mappings in MSmerge_current_partition_mappings table in merge replication.

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.

Publication data in merge replication

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.

output of MSmerge_current_partition_mappings

Now we will check the filters by inserting wrong org values or NULL into the UserDetails table.

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:

  1. The MSmerge insert trigger is fired first and maps the rowguid with ORG1 partition in MSmerge_current_partition_mappings
  2. 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
  3. 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.

Below are the steps that happen when user trigger is fired first.

  1. 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
  2. The MSmerge insert trigger is fired later which maps the rowguid with old org value i.e. with ORG1
  3. 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

    failure cases

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

SQL Server Replication with a table with more than 246 columns
Foreign key issues while applying a snapshot in SQL Server merge replication
SQL Server Replication (Merge) – What gets replicated and what doesn’t
SQL Server Replication (Merge) – Performance Issues in replicating schema changes
Merge SQL Server replication parameterized row filter issues
Log shipping on a mirrored database
Ranga Babu

About Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies