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

Question

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

Answer 1

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

2. 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

Answer 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.

Answer 3

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.

Answer 4

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