DB Strategies for scaling with Azure traffic manager

Question

I'm interested in using Azure traffic manager to route website/webapp traffic to a server located near the client (initially UK and USA).

Scott Hanselman wrote about how quick and easy this is to setup here but he didn't delve into the options regarding the database (many people were asking about this in the comments). So - can someone tell me the options here.

So, if I setup 2 websites, and use traffic manager to route between them, how do I solve the issue of database access. I'm using SQL Azure for the database (Standard tier currently), and my web app and database are reasonably "chatty". I'm presuming therefore that I'll need a database for each region, and then to set up some sort of sync between them. What are the options here? I've seen mention of SQL Azure Sync - is that still considered a good solution here? Or will the Active Geo-replication features of a premium database provide a better solution?

Answer 1

I would recommend going with Active Geo-replication feature if you want low latency and better throughput but note that the traffic routed to primary database can write into the database all other databases are read only. If you need to update, you can consider using service bus to submit the jobs to update the primary.

Answer 2

Our firm has a very similar situation. We essentially setup a complete infrastructure at various Azure data centers (Cloud Service, Azure DB, etc.) in the same affinity group. In our case, all update/insert/delete operations to the DB route through our app, so we send that statement to a queue and then perform the statement against the local DB as a single transaction. Competing consumers at each location monitor that queue asynchronously and apply to the other DB's (mapped in Elastic Scale Shard Map Manager DB) in an eventually consistent manner. We use surrogate GUID's for all Primary Keys, so our solution is simplified. For example, for an insert, the .NET code generates the GUID, then inserts locally. The same insert is added to the queue with the GUID, removing the need to fetch the generated key.