Peer-to-Peer replication topology – Conflict Detection

Overview

Peer-to-peer transactional replication lets you synchronize data at multiple nodes/servers. In other words, it allows inserting, updating or deleting data at nay node in the topology and having data changes propagated to other nodes. In such topology, if a record is modified more than one node/server, it can cause conflict or even lost updates when the record is propagated to other nodes, which can cause potential data inconsistency. To use conflict detection, you must have configured all nodes in SQL server 2008 as this option is available in SQL Server 2008 and later versions.

To overcome such situations, SQL server 2008 SQL Server 2008 introduces the new option called Conflict Detection in Peer-to-Peer Replication topology.

How does it work?

By enabling this option, by default a conflicting change is treated as a critical error that causes the failure of the Distribution Agent. In the event of a conflict, Distributor Agent stops applying the changes to that node.

Peer-to-peer replication detects the following types of conflicts:

  • Insert-insert: All rows in each table participating in peer-to-peer replication are uniquely identified by using primary key values. An insert-insert conflict occurs when a row with the same key value was inserted at more than one node.
  • Update-update: Occurs when the same row was updated at more than one node.
  • Insert-update: Occurs if a row was updated at one node, but the same row was deleted and then reinserted at another node.
  • Insert-delete: Occurs if a row was deleted at one node, but the same row was deleted and then reinserted at another node.
  • Update-delete: Occurs if a row was updated at one node, but the same row was deleted at another node.
  • Delete-delete: Occurs when a row was deleted at more than one node.

For more information log on to MSDN.

Reference: Microsoft MSDN