Replication consistently fails to bring new rows to subscriber

Question

Short description of the problem:

1. Initial synchronisation to the subscriber works fine.
2. Subsequent synchronisations fail to download new rows.
3. Re-initialising the subscription "fixes" the issue and the subscriber gets the new rows.
4. GOTO step 2

More details:

We've set up a new merge replication, on SQL Server 2008 R2 (on both the publisher and subscriber). Most tables use a filter for which rows they're going to replicate, similar to this example:

SELECT <published_columns> FROM [dbo].[Table1]
WHERE table1_id in (select table1_id from [dbo].[fn_GetTable1IDsForUser] (SUSER_SNAME()))

The fn_GetTable1IDsForUser function brings the relevant Table1 IDs for each subscriber based on pre-set criteria. Running the above query on the publisher (after replacing <published_columns> with *) returns the expected rows correctly.

Each synchronisation that is not done immediately after a (re)initialisation of the subscription seems to have succeeded, as it produces no error message, however no new rows are downloaded to the subscriber. Reinitialising the subscription allows the new rows to be downloaded, but only for the first synchronisation.

If I try to validate the subscription (in Management Studio; it only seems to work if I do it for all subscriptions, trying to do it for one has no effect), the next synchronisation produces the following error message:

Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.

I have followed the advice in this article: http://technet.microsoft.com/en-us/library/ms152532%28v=sql.105%29.aspx However none of the suggested solutions do anything.

• The table is filtered, and there are no changes to deliver to a given Subscriber.

This basically suggests to test the filtering query, which is working correctly, as I've described above.

• One or more agents are not running or are failing with an error.

There are no errors, except when I ask for the subscription to be validated. There are no conflicts either.

• A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.
• Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.
• The INSERT stored procedure used by a transactional article includes a condition that is not met.

This is a merge replication, not transactional.

• Data is being deleted by a user, a replication script, or another application.

Nothing acts on the data immediately after synchronisation.

• Data is being deleted by a trigger, or a trigger includes a ROLLBACK statement.

There are no triggers except for the ones set by replication itself (MSMerge_*).

In the end we've not been able to figure out why this happens. We'd welcome any suggestions.

Answer 1

I believe you are running into the issue described in Subqueries that you define on merge articles are not re-evaluated.

From the article the cause is:

This behavior occurs because the query is not re-evaluated and the row is not propagated as part of replication when you update a row in a table that is referenced by a subquery. Although you can place a subquery in a row filter, it is not a join filter. Although you can define a subquery that is based on data from another table, this too can cause unexpected results at the Subscriber.

And the resolution is:

To resolve this behavior, use a join filter when you want the filter definition to be re-evaluated during every merge synchronization process.