SQL Azure server in different region to web server

Question

I've just taken over a web project hosted on Windows Azure.

We've been having performance problems, and I noticed today that our SQL Azure server is in the West Europe region, while the instances hosting our site are in the North Europe region.

1. How big a problem is this?
2. Assuming it's a problem (and I assume it is at least a problem from a bandwidth billing point of view!) how do I fix it?

Is there an easier way to migrate than purchasing a second SQL Azure server in the correct region, taking the site offline, exporting from the current server, importing to the new server, modifying the site config so it points at the new server? If it can be done without downtime, that would make me very happy.

Answer 1

Q: How big a problem is this?

A: It's a potentially significant problem, due to:

1. Latency - You are looking at double-digit millisecond ping times between your web server and its backend database. This is silly and can only make your site less responsive than it should be.
2. Cost - Data transfer between subregions is billable, whereas data transfer within a subregion is free.

Q: How do I fix it with minimal downtime?

A: It is difficult for us to make a specific recommendation without knowing anything about the size or complexity of your database. Microsoft offers many options for moving data from one SQL server to another:

1. Copying entire databases
2. Copying data only
3. SQL Azure Data Sync Service

Generally speaking, if you have enough data that a simple backup and restore won't be quick enough to satisfy your requirement to minimize downtime, Data Sync Service is probably your best bet.

Answer 2

The ideal case is to have your database in the same data center as your compute instances, as @Miles mentioned. I wanted to address the topic of "buying" another database server.

When you configure a SQL Azure server, there's no cost for the server itself, only for the database. And that database cost is amortized daily, based on the database size for each day.

For a migration from one data center to another, let's just say you were able to successfully back up your database and store the backup in a blob. You could then restore this database to your new server. Once you know your two databases are in sync (maybe by setting up sync service), you can then modify your app's connection string to point to the newly-created database. Assuming your database connection is stored in your app's web config, you would likely need to deploy a new version of your app. You can deploy to staging, making sure everything works right, and then do a VIP swap to roll out your new app to production.

Once you migrate to your new database and ensure all the data is up-to-date, you can safely delete the database in the original data center. Assuming this all took place in, say, 3 days, you'd only incur double-database cost for those 3 days.