Peer Replication in SQL Server 2005/08

Question

Does anyone have experience configuring peer replication using SQL Server 2005 or 2008?

In particular, I am wondering if other options / alternatives are being considered, and why P2P replication was ultimately chosen.

If you used P2P replication:

Did you have any problems during synchronization and was it easy to track? How easy was / was conflict resolution? Did you need to make schema changes (i.e. replace identifier columns, etc.)?

Alternatively, if you were considering P2P replication and went with another option, why did you rule it out?

Answer 1

(Disclaimer: I'm a developer, not a database administrator)

We have SQL Server 2005 merge replication configured to replicate between two active / active geographically separated nodes for resilience on an outdated system.

I do not know if this is easy to control; out of my competence.

It creates triggers in each table to create a publish / subscribe mechanism, each of which calls its own stored procedure.

In our case, it was configured to use the identifiers 1-1bn in node 0, 1bn-2bn in node 1 to avoid identity conflicts (instead of using the NodeId + EntityId compound key for each table, or change the keys to be a GUID, for example).

I think the replication latency is about 15 s (between London and New York on the allocated bandwidth).

Huge pain works :

• A highly paid contractor is required per year to set it up (provided, part of this is due to the inherited character of the database design).
• We are missing someone who has experience to support it (our own DBA, which took us ~ 6 months to study it, and has since moved)
• Schema updates now hurt . From what I understand:
  • Some updates should be performed for only one node; replication will then take care of what to do with the other node (s)
  • Some updates must be performed on both nodes.
  • Data updates should only be performed on one node (I think)
  • All updates now take significantly longer - from the split seconds needed to run the DDL change - script to ~ 30 minutes
• I do not know for sure, but I think that the bandwidth requirement for replication is very high (in the range of MBit / s)
• It introduces a lot of “noise” objects into the database (3 lines in the table, three triggers for each table), which makes it difficult to find the object you need to work on in the object explorer.
• create a third node for this system, based mainly on the perceived complexity and additional pain that it will introduce during deployment.
• We also do not have an intermediate environment that reflects production, because it is too painful to set up.
• Anecdotal: The tuning DBA often curses the fact that it was the "MS v1" with which it was forced to work.
• I vaguely remembered: the database administrator needed to raise several priority support tickets in order to directly receive help from MS.

Provided - part of the pain is associated with our specific environment and does not have its own talent to support this installation. Your mileage may vary.