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.