We are looking at hosting 3 globally distributed SQL Server installations at different data centers. The intent is that Site A will serve web traffic and data for a specific region, same with Site B and C. In the case that Site A data center goes down, looses connectivity, etc. the users of Site A users will fail over to Site B or C (depending which is up). Also, if a user from Site A travels to Site C they should be able to access their data as it was on Site A.

My questions is what SQL replication technology (SQL Replication or 3rd party) can support this scenario? We are using SQL 2008 R2 Enterprise at each site, each site runs on top of VMWare with a Netapp filer. Would something like distributed caching help in this scenario as well?

We have looked at and tested Peer-to-Peer replication but have encountered issues with conflicts during our testing. I imagine there are other global data centers that have encountered and solved this issue.

2 Answers 2


What you are describing would be either peer to peer replication, or merge replication. Peer to peer is masterless, while merge replication has a master, however you can use mirroring on the publisher and mirror the publisher to another data center so that if the publisher goes offline the workload can fail over to another site.

  • We've attempted to use SQL peer to peer replication. With the limitations of using identity fields and poor conflict resolution it doesn't seem to be a fit. I imagine it would work if one could segment users globally and have a robust identity seed strategy. Our attempts failed when we have a user at Site A create a record and a user at Site B update said record.
    – jgombala
    Commented Mar 17, 2011 at 22:51
  • Any replication technology will require that identity management issue. Replication should take care of that by default. If I remember correctly you can adjust the size of the identity pools on each server when you setup the replication. It might need to be done via the stored procedures after the replication is setup. Conflict resolution is left to you to deal with at this point. Microsoft doesn't provide conflict resolution as part of the product any more.
    – mrdenny
    Commented Mar 18, 2011 at 19:14

You options in this case are:

Is it the case that site A is always the "master" so long as it is available? Is that case that B or C will be the standby, or do you require two standbys? How are you managing the failover (not just for the DBs, for the entire application)?

I have used the first two of these technologies, the advantage of database mirroring is that it is easy to manage and you have all the licensing you need. SnapMirror operates at a lower level and will require additional licensing for your Filers. I vaguely prefer database mirroring because I am a DBA. GoldenGate is a newer product, that involves a third vendor, more licensing, etc but it is certainly quite capable - but I have not used it "in anger" yet so I'll not recommend it except as a possible option.

  • Thank you for the input. However, the client doesn't want a "master" DB scenario. They are wanting the ability to have user in Site A use Site A's database, users in Site B use Site B's database, etc. In the case Site A, B, and C users need to see data entered by each other. If Site A user travels to Site C they need to see data they entered in Site A. If Site A fails it should fail-over to another site (B or C). Sort of like a mesh, peer to peer replication scenario... not sure if it's even possible.
    – jgombala
    Commented Mar 17, 2011 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.