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?
(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.
Menu
227
