Sample Header Ad - 728x90

Infinite Loop in SQL Server Merge Replication When Using Delete + Insert for Detail Records

0 votes
0 answers
23 views
I have a typical master-detail table relationship (e.g., Orders and OrderDetails). My application updates the detail records by: 1. Deleting all existing detail records for a given master record. 2. Inserting the new detail records. This update pattern is used because it's easier to replace all details than track row-level changes. I want that the subscriber can update the record in the publisher too. It's why I am using Merge Replication. The Problem:\ Once Merge Replication is enabled: The DELETE + INSERT operations at the Publisher get replicated to the Subscriber. The Subscriber, having already synced the previous detail set, treats this as a conflict or change, and re-applies its own version. The replication engine then thinks the Publisher has changed again and pushes the data back. This cycle continues infinitely, causing either: Endless insertions of the same data, or Continuous merge conflict resolution. The only way to stop it is to delete the subscription, which is not sustainable. Questions: Is this an expected behavior in Merge Replication when using Delete-Insert update logic? Is there a recommended best practice for updating detail records in Merge Replication scenarios to avoid this loop? Would using UPDATE statements instead of DELETE + INSERT be a reliable workaround? Are there settings (e.g., conflict resolution rules, rowguid column issues, or tracking settings) that can help avoid this behavior? Environment: SQL Server 2017 Merge Replication with bi-directional sync (Publisher Subscriber) Replicated tables have rowguid and conflict tracking enabled
Asked by Filbert Umbawa (1 rep)
Jun 12, 2025, 02:26 AM
Last activity: Jun 12, 2025, 04:31 AM