Multi-Datacenter design for SQL Server I'm not a DBA and perhaps I can't explain myself properly. I'm looking for some fresh ideas about how, and what I need to deploy, to get a full SQL Server database available across different continents, something like building a SaaS over your private "cloud".

Imagine you have several offices in LATAM, EMEA, EAST and EEUU. Each office in every region has its own SQL Server with local databases and you are tired of managing hundreds of servers. 

Easiest way to do resolve this is converging all small databases into a big one, placing it into a datacenter. As a first approach, I though that a single datacenter based in a single region will be enough to satisfy our requirements, but it won't because of latency. I mean that local users in one region can't work with a database based in a different region.

What's next? Simple thing. Deploy a datacenter in every region you want and every database will contain the same information with read/write and HA capabilities. At this point, I guess SQL Server with an AlwaysOn cluster will satisfy HA, but what about read/write? Do I have to use any kind of replica with every database service? Will it work?

And going further, imagine that a single country in a region needs to have its own database. How do I set up replication for this? What if I have to change a database schema?

Any help or clarification will be appreciated.



Our goal is to design a 3-tier application based on .net WCF published via Citrix XenApp. Due to some requirements this application won't work if the client is trying to connect to a far datacenter (i.e. LATAM to EU) because of latency (WCF works with local devices). Resuming this part, we need to build 2 DC in every continent to offer a reliable application tier with a proper fault tolerance and HA. Our main problem is to know what can we do at data tier...

My first idea was to deploy a SQL AlwaysOn cluster in the primary datacenter as a central node, with N SQL Std replicating bidirectionally from this master node. Yes, I said bidirectionally because some people at my work are fully confident with this scenario, but I'm still trying to understand how it will work in SQL Server (even if it's possible). I have more experience with MySQL and Galera and it works, but my SQL Server knowledge is limited.

I know you will ask me, why don't you connect directly from application tier to this central database? Perhaps this is the best choice because this SQL service won't be quite exhaustive (100.000 transaction per day), but we have to ensure the service and application functionality at 99,99999.
At this point, I guess SQL Server with an AlwaysOn cluster will
  satisfy HA, but what about read/write?


I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name.


  Deploy a datacenter in every region you want and every database will
  contain the same information with read/write and HA capabilities


You ask about the read/write capabilities and I think this is where you will be disappointed. Even with a combination of local FCIs with a cross site AG it is only ever possible to have one node as the PRIMARY (writable) node. The other nodes can act as read-only nodes to allow you to off load reads and backups.

From my experience with building systems like this you really want to keep things as simple as possible. For this reason I wouldn't recommend trying to extend an AlwaysOn FCI + AG to more than 2 sites. I would also stay clear of bidirectional transnational replication as this has a tendency to break.

Without more information it is difficult to recommend a solution for you. Some very general topics to consider would be; a caching layer in your app at each site to help with the latency or a SQL Server Azure deployment.

After the update to the question:

I really wouldn't recommend you to use SQL Server in that way. Bidirectional replication between 2 nodes is bad enough. >2 nodes plus the cost of all those Enterprise Editions of SQL Server just sounds like a bad idea. Maybe take a look at what Azure has to offer.