11

What's the difference between peer-to-peer replication and merge replication using SQL Server?

Guy
  • 65,082
  • 97
  • 254
  • 325

4 Answers4

8
  1. Peer-to-Peer Transactional Replication is typically used to support applications that distribute read operations across a number of server nodes.

Although peer-to-peer replication enables scaling out of read operations, write performance for the topology is like that for a single node, this is because ultimately all inserts, updates, and deletes are propagated to all nodes. If one of the nodes in the system fails, an application layer can redirect the writes for that node to another node, this is not a requirement but does maintain availability if a node fails.

See: Peer-To-Peer Replicaiton

  1. Merge Replication is bi-directional i.e. read and wrtie operations are propogated to and from all nodes. Merge replication often requires the implementation of conflict resolution.

See: How Merge Replication Works

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
John Sansom
  • 41,005
  • 9
  • 72
  • 84
  • What you say on Peer-to-Peer does not match the source you quote. You imply that applications need to only write to a single server always, the source you reference recommends "that write operations for **each row** be performed at only node". Your answer implies that all nodes except one in a Peer-to-peer network are read-only and one is read/write, that simply is not true; as the source you quote indicates. Peer-to-peer is closely related to bi-directional transactional replication, and not based on snapshot replication. – Seph Sep 10 '12 at 10:29
  • To clarify, look at the two diagrams in the reference. Each represents a different implementation of a P2P topology but the method of how a write operation is distributed through the topology remains the same, that is once it has been applied to a given node. A given write first occurs on a single node(which is dependant on the topology implemented) and from there the write is propagated out to the remaining nodes. I have no idea why you are even mentioning Snapshot replication in the context of the discussion. – John Sansom Sep 17 '12 at 16:17
  • 1
    I just feel that your answer implies that "write operations are applied to a single node" implies that only a single node in the entire network is writeable. Your wording implies that "The application needs to be aware of the network topology in order to direct your write activity." is a requirement of Peer-to-Peer replication and that is not the case. You only ever write to a single server and it's replicated out to all other servers (otherwise you're doing the replication manually and not sql), the application doesn't need to always write to the single specific server. – Seph Sep 17 '12 at 16:44
  • Sounds like semantics are being debated TBH. A "given write" issued by the app tier cannot be applied to two nodes, this would result in a conflict and hence the discussions of a write request being sent to a single node (how that's achieved is implementation specific whether app logic/load balancer etc.) and then SQL Server manages the write distribution to the remaining nodes via Replication. Consider that when a Data Professional says App Tier, that means everything above the database in the stack ;-) I hope that clears things up. – John Sansom Sep 22 '12 at 15:08
2

The main difference is that for merge replication there is only one publisher and one or more subscribers, but in peer-to-peer replication all nodes are both publishers and subscribers(though original node is highlighted with green arrow).

Secondly peer-to-peer replication is transactional which means it transmits transactionally consistent changes. In contrast, merge replication is trigger based. In the background implementation they also use different agents.

Merge replication has conflict resolution(you can specify conflict resolution priority), peer-to-peer doesn't. During a conflict peer-to-peer generates an alert if conflict resolution is enabled, stops replication while allowing both instances to work independently till the conflict is solved. In production, it is advisable to do schema changes only from the original node.

In peer-to-peer replication all nodes are identical while in merge they can differ. I mean that subscribers can get different data from the publisher.

They both are basically doing the same job - providing scale-out, disaster recovery, and in some cases where updates are rare and locks do not bother that much, also high availability by providing data redundancy. Sometimes, peer-to-peer is related as the replacement for the merge replication.

igelr
  • 1,491
  • 2
  • 10
  • 25
0

EDIT Peer to Peer replicaiton is of two types - Transactional and Snapshot. Both of these are one way - from publisher to subscriber.

Transactional and Snapshot replication move data from publisher to subscriber. They are used primarily for editing in a single place and viewing / reporting data in multiple places. Transactional is almost instantaneous, while snapshot has to be scheduled. Transactional has a heavy initial resource requirement because it creates an initial snapshot and then it reads subsequent transactions from the transaction log to send data over. Snapshot is resource intensive every time it runs because it generates a new snapshot every time.

Merge replication lets you have multiple places where you can edit the data, and have it synchronized in near-real-time with the peers. Merge replications essentially runs a transactional replication engine to distribute the transactions, and additional logic to apply the transactions at the destination(s).

Here is some reading material http://technet.microsoft.com/en-us/library/ms152531.aspx

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • Are you certain that Peer to Peer Snapshot Replication actually exists? Not to be confused with creating a snapshot to initiate replication. http://technet.microsoft.com/en-us/library/ms151196.aspx – John Sansom Sep 16 '09 at 19:30
  • @John Sanson: I edited based on the usage of "Peer to Peer". My view was that both Transactional and Snapshot worked on peer to peer principles or through a distribution principle. – Raj More Sep 16 '09 at 20:04
0

Updateable subscribers are designed for scenarios where the majority of your changes occur at the publisher but you want to be able to have some small number of changes originate at the subscriber.

P2p does not have such a limit.

P2P is designed to scale out reads, although many people wrongly use them as an update anywhere topology. p2p is also an Enterprise Edition only feature, where as updateable subscribers work on the Standard Edition of SQL Server and above.

Chandan Gupta
  • 1