How to: Configure Conflict Detection for Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming)
Peer-to-peer replication includes the option to enable conflict detection across a topology. For more information, see Conflict Detection in Peer-to-Peer Replication.
To use conflict detection, all nodes must be running SQL Server 2008 or a later version; and detection must be enabled for all nodes. You can enable conflict detection when you are configuring a topology or after the topology is configured. It is not required to quiesce the topology to enable conflict detection for an existing topology. However, if conflicting changes occur before all the nodes are enabled for conflict detection, replication cannot guarantee detection of those conflicts. For more information about how to configure a topology, see How to: Configure Peer-to-Peer Transactional Replication (Replication Transact-SQL Programming).
Note
When you configure peer-to-peer replication, you specify an ID for each node. This ID, which must be unique across all nodes in the topology, is stored in the originator_id column in the MSpeer_originatorid_history system table. If a node is removed from the topology, the ID is still retained in the history table. The ID is retained to prevent false conflicts from occurring if there are changes from the removed node that are still being replicated across the topology. If you want to reuse the ID for a new node, you must first manually delete the ID from the MSpeer_originatorid_history table at all nodes. Before you delete an ID for a node, execute sp_requestpeerresponse to verify that all changes that originated from that node have been replicated.
To enable conflict detection during topology configuration
When you create a publication at each node by using sp_addpublication:
Specify a value of true for @p2p_conflictdetection.
Specify a positive, nonzero integer for @p2p_originator_id. The ID must never have been used in the topology. For a list of IDs that have already been used, execute sp_help_peerconflictdetection.
Specify a value for @p2p_continue_onconflict. We recommend that you specify a value of false. For more information, see "Handling Conflicts" in Conflict Detection in Peer-to-Peer Replication.
To enable conflict detection after a topology is configured
These steps assume a topology that consists of three nodes: A, B and C. On Node A execute sp_configure_peerconflictdetection:
Specify the publication name for @publication and a value of enable for @action.
Specify a positive, nonzero integer for @p2p_originator_id. The ID must never have been used in the topology. For a list of IDs that have already been used, execute sp_help_peerconflictdetection.
Specify a value for @continue_onconflict. We recommend that you specify a value of false. For more information, see "Handling Conflicts" in Conflict Detection in Peer-to-Peer Replication.
Repeat step 1 for Node B and Node C.
Execute sp_help_peerconflictdetection from one of the nodes; specify the publication name for @publication. Check the result set to make sure that all of the nodes have conflict detection enabled and that each has a unique originator ID value.
To disable conflict detection
- Execute sp_configure_peerconflictdetection for each node in the topology. Specify the publication name for @publication and a value of disable for @action.