Sql – Peer to peer replication in SQL Server 2005/08

replicationsql-server

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?

    Best Solution

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

    Related Question