21 Feb 2007

SQL Server 2005 Merge Replication not replicationing changes to the subscribers

I have been using SQL Server 2005 merge replication within my current project.  It is a centralized Merge push topology using a custom RMO application with over 350 subscribers but that is the subject for another post…

While we have been testing there have been some issues raised by our testers and one of these in particular is what I’d like to write about today.

We were seeing data being inserted by the application into the publisher database but this data was not being replicated down the to the targeted subscriber (parameterized filters were being used).  Bizarrely, all the changes made at the target subscriber were replicated up to the subscriber. 

The data which was not being replicated turned out to be new child data organized by a join filter within the replication publication.  The application is a .net smart client which is used at both publisher and subscriber database and uses a SQL Server login account to access the database. After some investigation by me and my colleague, James Rowland-Jones, we found the issue.

Using SQL Server profiler to track all the SQL statements we traced the problem to the insert merge trigger.  The trigger was not assigning the child data to the correct partition i.e. no row being inserted into MSMERGE_CURRENT_PARTITION_MAPPINGS.  The problem was with the way the trigger checks for associated child records.  The trigger uses a generated replication view MSMERGE_REPL_VIEW_<PUBGUID>_<ARTGUID>, which rather than just performing a select on the base table to see if the data qualifies for a partition, it additionally performs a security check using an IsPalUser function. Our application user was not in the PAL and so consequently the view would return 0 results rather than the expected data.  This was certainly not what we were expecting!  The PAL is for controlling access to the publication not influencing what data is ultimately replicated.

So to get the join filter to work as expected we had to add the application database user to the PAL.

More on merge replication to follow….