Short description of the problem:
- Initial synchronisation to the subscriber works fine.
- Subsequent synchronisations fail to download new rows.
- Re-initialising the subscription "fixes" the issue and the subscriber gets the new rows.
- GOTO step 2
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
*) 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.