please help me in evaluating new possibilities with MS SQL Server 2008. For the last 3 years I have had an MS SQL 2000 Server Merge Replication with a Publisher and two Subscribers on duty, the backup of the database on one instance is as big as 19 GB. On each location data is inserted, updated and deleted. Everything works like a charm through a WAN with something like 10 mbit/s.

Now we change the platforms to win2008 and sql2008 and so arise new questions. The first: Is Peer-to-Peer replication a replacement for a merge-replication?

peace Ice

Update: In front of the answers, let me be more precise: We have on the publisher some tables which are not member of the replication. We also have more than one merge-replication on this database, a second separate one on some tables which were added later. (How many replicatons make sense; for each table her own replication?) There are no selections on a table so when a table is replicated all rows are delivered. Each table has an identity column. An additional Question: Are columns with type timestamp allowed with replication?


It can be, but be aware of some big differences between the approaches:

  • Merge replication involves one publisher and multiple subscribers. In peer-to-peer, all nodes are both publishers and subscribers.
  • In merge replication, the subscribers can have different partitions of the data. In peer-to-peer, all nodes are identical.
  • Merge replication has conflict resolution. Peer-to-peer does not.
  • Peer-to-peer is only available in Enterprise Edition.

Books Online has more details on both approaches.

  • Does it requires witness or any other server to monitor the changes ? – Senior Systems Engineer Jun 2 '11 at 6:13

Yes Peer to Peer can be a replacement for merge replication.

  • So how does this P2P detects or avoid any synch conflicts when the link between sites suddenly up again ? – Senior Systems Engineer Jun 2 '11 at 6:13
  • 1
    If there are conflicts on P2P replication the replication will shut down until the conflict is fixed. As I understand it the replication simply compares the old values on the changed partner to the values on the unchanged partner. There is a flag to ignore conflicts and simply make the change. – mrdenny Jun 15 '11 at 0:07
  • oh, so the replication will be stopped and both DB instance will be stand alone running by itself ? – Senior Systems Engineer Jun 16 '11 at 0:34
  • 1
    Yes, if replication is stopped due to conflict detection the instances would just be left by themselves until the issue was resolved. – mrdenny Jun 21 '11 at 20:32
  • ah, IC so in this case it is not what I'm looking for, so I'll move on with MERGE then. – Senior Systems Engineer Jun 21 '11 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.