Merge Article Processing Order and using the "Not for Replication" Attribute

This article outlines answers to the question: 'Why is the NOT FOR REPLICATION attribute sometimes recommended on foreign keys in merge articles?'. I must add at this stage that understanding these issues might be crucial to your application. put simply, if the NFR is not set you might start seeing constraint violations. The other reason I consider this important is for the users of SQL CE. SQL Server Compact Edition does not support the NOT FOR REPLICATION option and although you can happily set it on the publisher it doesn't work on the subscriber. This means that you must understand which parts of teh application might break and what to do about it.

Background

The merge process assigns tables referenced by a FOREIGN KEY constraint (a parent) a smaller article nickname than that of the referencing table (the child table, or the table on which the FOREIGN KEY constraint is defined). If a table does not participate in such DRI constraints, the merge setup process assigns the article nickname based on the order in which it adds the article to the publication (in ascending order). After that, the Merge Agent processes INSERTs and UPDATEs in ascending article nickname order (PK first), and processes DELETEs in descending article nickname order (FK first). To understand more details about this mechanism, have a look here.

Issues

So, how can there be a problem, and why is the NOT FOR REPLICATION attribute sometimes necessary in merge replication? There are various possibilities, each outlined below:

(1) You might have created the tables without FK at first, then added the tables as merge articles in the order of child first, then parent. This would have led replication to assign a lower nickname to child and higher to parent. You might have later added the FK via ALTER TABLE, but this has no effect on the order any more because the nicknames would have already been assigned. One easy way of checking whether this is what you are running into is to run

select nickname,* from dbo.sysmergearticles
order by nickname

In other words, to get the proper order you need to have the FKs defined before the articles are added.

(2) Another way this can happen is with circular relationships:

In such a situation there is no definite logical order of article nicknames.

(3) If parent/child nicknames appear to be correct, then another possibility is that rows are processed in the correct order, but the parent fails due to deadlock, timeout, etc. and so the child fails with FK error. If the parent goes through in the retry phase, the child should also eventually go through.

(4) There is also the possibility of multiple batches, with child coming before parent. For interest's sake, to reproduce this behaviour, insert parent and child rows and then make a lot of other updates in your database and after every few changes run "exec sp_MSmakegeneration". By running sp_MSmakegeneration, you are mimicking running a merge. They both have the effect of packaging all pending changes into generations ready to be replicated - future changes go into new generations. Keep doing this until you have about 150 new rows in MSmerge_genhistory. Now update the parent row. This makes the parent row go to a generation that will go in a later batch, while the child stays in a generation that will go in an earlier batch (default generation batch size = 100). Running merge now should repro the problem. This is just a simple reproduction, and this article intricately describes the circumstances in which parent and child records can end up in different batches.

(5) I've recently come across another variation. Suppose we have 2 tables linked by a simple PK-FK relationship: TablePK and TableFK. The column which links the tables is "col1" and the FK column is nullable. We might perform the changes below:

update TableFK set col1 = null where col1 = 'somevalue'
update TablePK set col1 = 'xxx' where col1 = 'somevalue'
update TableFK set col1 = 'xxx' where col1 is null

This all seems quite simple? Remember that updates are done in ascending nickname order meaning that the PK table gets done first. This means that the "set col1 = 'xxx' where col1 = 'somevalue'" statement is run. This is a big problem though as there will be a constraint violation on the other node for the TableFK record which relies on the "somevalue" existing. Hence the NFR is used.

Conclusion

In some circumstances the NOT FOR REPLICATION attribute may be necessary and hopefully if you find PK/FK errors preventing the merge agent from running you'll be able to better understand why this setting is required.

If you are a SQL CE user then you might see some issues of concern in this article - especially with (5). This is because the NFR setting won't work on the subscribers. You have some options:

(a) don't use FKs on the subscriber for certain tables and have the application enforce the integrity

(b) ensure that synchronization occurs before an entire batch is completed eg syncing after each update in the example above will succeed as planned.

For standard subscribers (versions > SQL CE) with custom article processing order (@processing_order in sp_addmergearticle) and session level retry functionality, things are massively improved in SQL Server 2005/8.