Peer-to-Peer Transaction replication is a kind of bidirectional replication. It operates in a way that transaction replication works on each instance. In this article, I will explain how Peer-to-Peer replication works.
Prerequisites
In Peer-to-Peer replication, the following two jobs are working. In Peer-to-Peer replication, reinitialization from a snapshot file is not possible; instead, initialization must be done from a backup. Therefore, the Snapshot Agent does not exist.
Distribution Agent (exists on each node)
The Distribution Agent is transferring the replicated data from the Distributor to the Subscriber. It ensures that the transactions are applied to the Subscriber database in the correct order.
Log Reader Agent (exists on each node)
The Log Reader Agent monitors the transaction log of the Publisher database and identifies the changes that need to be replicated. It reads these changes and moves them to the distribution database for further processing.
Limitation
There are several limitations in Peer-to-Peer replication. The main limitations are as follows:
– Peer-to-Peer replication is available only in SQL Server Enterprise Edition.
– Each node must use its own distribution database.
– Initialization and reinitialization using snapshots are not supported.
More detailed information about limitations are follows:
Overview
Peer-to-Peer replication works as illustrated in the following diagram.
1. Setup
In Peer-to-Peer replication, each node must have its own distribution database. Therefore, the first step is to enable local distribution on each node.
2. Initial Synchronization
Peer-to-Peer replication does not support initialization using snapshots. Therefore, initialization must be done using a backup file. First, take a backup on one node and restore the backup on the other nodes.
3. Configuration
When setting up Peer-to-Peer replication, a “Distribution Agent” and a “Log Reader Agent” are created on each node.
4. Ongoing Synchronization
After configuring Peer-to-Peer replication, the Log Reader Agent and Distributor Agent run on each node, ensuring continuous synchronization.
How to resolve conflict
Due to the bidirectional synchronization of changes, conflicts can occur. I believe that when using Peer-to-Peer replication, it is better to prevent conflicts at the application level rather than relying on replication to resolve them. For example, configuring the system to change the update server based on time intervals or restricting specific tables or partitions to be modified by designated servers can help avoid conflicts.
If conflict resolution cannot be handled at the application level, Peer-to-Peer replication provides two built-in conflict resolution methods.
- Enable p2p_continue_onconflict = ‘true’ option.
- Enable p2p_conflictdetection_policy = ‘lastwriter’ option.
1. Enable @p2p_continue_onconflict = ‘true’ option.
This conflict resolution prior to SQL Server 2019 (15.x) CU 13. When configuring Peer-to-Peer replication, each node is assigned an originator ID, and in the event of a conflict, the change from the node with the larger originator ID takes priority.
You can check whether p2p_continue_onconflict is enabled using the following query. The status of p2p_continue_onconflict can be verified from the “enabled_for_p2p_conflictdetection” column.
DECLARE @publication AS sysname;
SET @publication = N'Publication Name';
USE [Publication Database Name]
EXEC sp_helppublication @publication = @publication;
GO2. Enable p2p_conflictdetection_policy = ‘lastwriter’ option.
This parameter is introduced in SQL Server 2019 (15.x) CU 13. p2p_conflictdetection_policy is an option that can only be set when creating a publication. In case of a conflict, it prioritizes the most recently modified data. Information about the update timestamp is recorded in the hidden column $sys_md_cd_id.
Reference