When administering Merge Replication, sometimes we need to mark foreign key constraints NOT FOR REPLICATION. In some cases, we might require replication agent activity to be treated differently from user activity since we cannot guarantee the order of changes that are replicated. To be more specific, sometimes parent and child records are sent in different batches, with the children getting sent first, resulting in constraint violations and conflicts.
There are a few scenarios that cause this behavior to occur and some of the details are covered in the following links.
- How to understand Merge Replication article processing order
- Merge Article Processing Order and using the "Not for Replication" Attribute
- All about “Not for Replication”
- Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
- PRB: Non-Convergence When SQL Server Processes Child and Parent Generations in Separate Generation Batches
By marking foreign key constraints NOT FOR REPLICATION, we can alleviate constraint violations from occurring when changes are made by replication agents. With that said, it takes a fair amount of faith to trust that Merge will in fact deliver the parent records in the next batch of changes. That's why it's wise to take a closer look when this happens and verify that after dropping and adding the constraints NOT FOR REPLICATION, that the parent records do arrive.
Dropping and adding foreign key constraints NOT FOR REPLICATION can be time consuming if there are a large number of them. To speed this process up, I've been using variations of the following SELECT to generate my ALTER statements:
This has been tested on SQL Server 2008 R2 against the AdventureWorks database. It does not handle composite foreign keys, among other things, but should provide a good starting point. Please verify your constraints are defined correctly before deploying to production. If you have any questions, feel free to get in touch.