Symptoms

After (re-)creating a subscription to a pull merge replication publication from SQL Server Express 2005, the subscription fails to appear in Synchronization Manager. After further investigation, the symptom was determined to be restricted to non-Administrators.

Things To Check

• Make sure sp_addmergepullsubscription_agent was run with @enabled_for_syncmgr = 'TRUE'. This requirement differs from previous SQL Server versions where this was the default. When this parameter is not set to 'TRUE', the subscription will not appear in Synchronization Manager
• Make sure the subscription can be synchronized outside of Synchronization Manager (to confirm that it is a problem when run through replsync.dll - in Synchronization Manager). The easiest way to do this is using replmerg from the command-line.
• Make sure the user has permissions to write to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Replication\Subscriptions. Without write permission, SQL Server will silently fail to appear in Synchronization Manager. Also, by default in many configurations, non-Administrators do not have write access to this key so it must be adjusted manually.

The last item is particularly important and required quite a bit of my time to determine... which resulted in the need for this post. Hopefully one of the above suggestions will help you avoid spending the same amount of time that I did to solve this problem.

Initial Note/Warning

I hope that there is a more elegant, simple, and robust method for dealing with the issues described in this post than the one that I present herein. My method is a rather ugly kludge that relies on undocumented features of SQL Server and is not nearly as parallelizable as I would like. If you are aware of a better method (which I hope exists), please don't hesitate to post it in the comments. Until a better method is posted, I invite you to use and/or learn from the method presented in this post.

Background

Consider an SQL Server 2005 database which is published for merge replication from a single publisher with multiple subscribers. The database contains 2 tables which we are considering: Forms and InboxForms. Forms contains some sort of form data and InboxForms references all forms which are present in a user's "inbox". Each of the tables contains an INT IDENTITY primary key column and several other columns of data that are not relevant to the problem at hand. The publication is filtered based on the Forms that each user is able to view (determined by some separate mechanism not described here). When a new row is inserted into Forms, a trigger is used to route the forms into the appropriate inbox(es).

The Problem

The routing can not occur (exclusively) at the subscribers, because the filter for the subscriber will not necessarily include user information for the recipient of the form and the form can not be placed into InboxForms if it recipient user does not exist on the subscriber. So, the trigger must run on the publisher during synchronization when inserts are performed by the merge agent (i.e. the trigger must not be marked NOT FOR REPLICATION). However, in this configuration, when the merge agent runs, the following error message is produced:

The problem is that the INT IDENTITY column in InboxForms was marked NOT FOR REPLICATION when the publication was created in order to facilitate automatic identity range management, which is described in the Replicating Identity Columns article in the SQL Server Books Online. NOT FOR REPLICATION behaves very similarly to IDENTITY_INSERT (as hinted at in the error message), such that when a row is inserted by the merge agent, the ideneity seed value is not incremented and the value of the identity column must be explicitly specified. Note, however, that it is not the same mechanism as IDENTITY_INSERT, so changing IDENTITY_INSERT in the trigger will not remove the requirement for explicitly specified identity values.

The Solution

One method to solve this problem is to disable NOT FOR REPLICATION, as suggested in KB908711 (which specifically addresses this issue). However, using this option will interfere with automatic identity range management, since the identity values generated on the subscribers can not be copied to the publisher, and other steps will need to be taken to manually manage identity values. For me, this was an unacceptably high price to pay and another solution was required.

A solution which does not interfere with automatic identity range management is to calculate values for the identity columns and explicitly specify those values when they are required. In order to accomplish this, understanding several features of T-SQL is required: In order to determine when the values are required, the trigger needs to test if it is being run from the merge replication agent. This can be done by testing for the 'replication_agent' SESSIONPROPERTY. In order to determine appropriate values for the identity column, use IDENT_CURRENT and IDENT_INCR. Note that using the maximum value for the identity column is not necessarily correct because the maximum identity range will not necessarily be allocated to the publisher. DBCC CHECKIDENT can be used to update the identity seed value (which is not affected by explicitly inserted identity values).

One other complicating factor in our implementation is that there is no way to atomically insert explicit identity values and update the identity seed value. Therefore, locking is required to prevent multiple connections from simultaneously updating the values and causing collisions. (Or collision errors must be caught and retried) In the following implementation, an exclusive table lock is acquired which prevents any inserts from occurring on the table when the trigger is running. This is a serious performance problem as it prevents any other operations on the locked table from completing while the trigger is executing. Keep this in mind when designing the queries that will run while the lock is held.

Now, without further ado, here's the trigger: