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.
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.
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:
In such a situation there is no definite logical order of article nicknames.
(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.
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.
Paul Ibison, Copyright © 2013