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.
