Peer to peer replication in SQL Server 2005/08

Question

Has anyone had any experience in setting up peer to peer replication using SQL Server 2005 or 2008?

Specifically, I'm interested in whether other options/alternatives where considered and why P2P replication was ultimately chosen.

If you have used P2P replication:

Did you encounter any issues during synchronization and was it easy to monitor?

How easy was/is it to do conflict resolution?

Did you have to make schema changes (i.e. replace identity columns, etc)?

Alternatively, if you considered P2P replication and went with a different option, why did you rule it out?

Answer 1

(Disclaimer: I'm a developer, not a DBA)

We have SQL Server 2005 merge replication set up to replicate between two active/active geographically-separated nodes for resilience in a legacy system.

I don't know whether it's easy to monitor; outside of my remit.

It creates triggers on every table to do the publish/subscribe mechanism, each of which calls its own stored procedure.

In our case, it was set up to use identities 1-1bn in node 0, 1bn-2bn in node 1 to avoid identity collisions (rather than use a composite key of NodeId + EntityId for each table, or change keys to be GUIDs, for example).

I think the replication latency is around 15s (between London and New York over dedicated bandwidth).

It is a huge pain to work with:

• It took a highly paid contractor a year to set it up (granted, part of this was due to the legacy nature of the DB design)
• We lack anyone in-house with the expertise to support it (the in-house DBA we had took ~6 months to learn it, and has since moved on)
• Schema updates are now painful. From what I understand:
  • Certain updates must be performed on only one node; replication then takes care of figuring out what to do on the other node(s)
  • Certain updates must be performed on both nodes
  • Data updates must be performed on one node only (I think)
  • All updates now take significantly longer to perform - from the split-second it takes to run a DDL change-script to ~30 minutes
• I don't know for sure, but I think the bandwidth requirement for replication is very high (in the MBit/s range)
• It introduces many "noise" objects (3 sprocs per table, 3 triggers per table) into the DB, making it inconvenient to find in the object explorer the item that one wants to work on.
• We will never set up a third node for this system, based largely on the perceived difficulty and added pain it would introduce at deployment-time.
• We also now lack a staging environment that mirrors production, because it's too painful to set up.
• Anecdotal: The DBA doing the setup would frequently curse the fact that it was an "MS v1" he was being forced to work with.
• Dimly remembered: The DBA needed to raise several priority support tickets to get help from MS directly.

Granted - some of the pain involved is due to our specific environment and not having in-house talent to support this setup. Your mileage may vary.