I am exploring Azure for some hosting options for a client of mine, and one thing we're looking for is a distributed model, where we'll have VMs sitting on the east and west coast, and when someone visits the site(s), they will go to the servers closest to them. This is identical to the Azure CDN model, however we need to full control over the content served, whereas the CDN option is public only (e.g. we need public and private). The database powering everything will be SQL Azure, so we're also hoping this solution can be smart enough to go to the SQL instance closest to the VM(s) as well.
I can't really find much detail from the Azure side on how to go about this, though I'll be honest in saying I also don't know the proper terms to even search for. Any tips/suggestions? The load balancing option from Azure doesn't seem like it'd be quite right, as it appears to be round-robin based which isn't what we're after either.
I think there is no solution out-of-the-box for doing that for SQL Azure that you may configure between a virtual machine and SQL Azure instances that will route the request according to the IP/whatever.
There is the Azure Traffic Manager service that you may use for that job, and best practices for that list some sentence about SQL Azure that basically is that you should plan the architecture. Traffic Manager may use geo-location by IP addresses, round-robin and failover (listed in the link).
I need to say that in terms of the cloud nearest geo-location isn't always the best option, and i would go with the testing latency from your datacenters and some local locations using that latency measurement tool AzureSpeed.
And, usually what i observed is that, because of the good network infrastructure that connects all of the datacenters, the latency inside of that datacenter without leaving Microsoft network infrastructure is not an issue.
So, i would connect Azure Traffic Manager for your VMs and plan SQL Azure instances to put in the same regions as your client-faced VMs will be.