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