DB strategies to scale with Azure Traffic Manager
I am interested in using Azure Traffic Manager to route website / web server traffic to a server located near the client (originally UK and US).
Scott Hanselman wrote about how to set up quickly and easily here , but he didn't go into the options regarding the database (many people asked about this in the comments). So - can anyone tell me the options here.So, if I set up 2 websites and use Traffic Manager to route between them, how do I solve the database access problem. I am using SQL Azure for the database (standard tier at the moment) and my web application and database are "chats" enough. So my guess is that I would need a database for each region and then establish some sort of sync between them. What are the options here? I've seen mention of SQL Azure Sync - is this still considered a good solution here? Or will they make better use of the premium database's active geo-replication features?
source to share
I would recommend using the Active Geo-replication feature if you want low latency and better throughput, but note that traffic redirected to the main database can be written to the database, all other databases are read-only. If you need to upgrade, you can use Service Bus to submit jobs to upgrade the primary version.
source to share
Our company has a very similar situation. We are essentially setting up a complete infrastructure across different Azure datacenters (Cloud Service, Azure DB, etc.) in the same affinity group. In our case, all update / insert / delete operations on the DB route are through our application, so we send this statement to the queue and then execute the statement against the local DB as one transaction. Competing consumers at each location monitor the asynchronous queue and are applied to the other DB (displayed in the Elastic Scale Scale Manager Form) eventually sequentially. We use surrogate GUID for all primary keys, so our solution is simplified. For example, for insertion, the .NET code generates a GUID and then inserts locally. The same insert is added to the queue with a GUID,eliminating the need to obtain a generated key.
source to share