Moving a SQL Azure database to another data center

I recently decided to move Am I Interesting to the North Central US data center to improve performance for my Facebook queries. I’ve had relatively poor latency and some packet loss when executing queries over the Atlantic when the app was placed in the North Europe data center.

When I went to move the web role as a first step, I also found – to my surprise – that I had accidentally “misplaced” my database server in South Central US when I originally created it! :-|. Now that’s a new experience coming from traditional hosting – misplacing a server in an entirely different part of the world! šŸ™‚

So this was my starting point:

After moving the hosted service (web role) to North Central US, which this post doesn’t cover, my infrastructure layout now looked like this:

I now prioritized to move the database service, as the app uses SQL Azure more intensely than other storages, from South Central US to North Central US, and my plan of attack to avoid downtime (even though data could be inconsistent a few minutes) was to:

  1. Create a new database server in North Central US
  2. Create a copy of my application database in North Central US
  3. Turn on one-way Data Sync from my old database server to new server
  4. Modify my ServiceConfiguration for my hosted service to use the new database instead
  5. Shut down the old database server (after successful sync)

Step 1 – Create a new database server in North Central US

This was simply done in the Azure Management Portal.

Step 2 – Create a copy of my application database in North Central US

For this step I first tried to follow the MSDN articles on http://msdn.microsoft.com/en-us/library/ff951629.aspx and http://msdn.microsoft.com/en-us/library/ff951624.aspx but came out short when trying to execute the command CREATE DATABASE xxx AS COPY OF server.database, which gave me a “transport-level error”.

So instead I created a new empty database on the destination server with the same schema as my source database (using GENERATE SCRIPTS in SQL Management Studio), hoping that data sync could transfer all data for me (see next step, I’m documenting as I go as you probably can tell :)).

Step 3 – Turn on Data Sync from old database server to new server

I then provisioned a new Data Sync server through the Management Portal and placed it in North Central US. I then selected to “Sync between SQL Azure databases” and proceeded in the wizard to define my new database (Hub) and my old database with single-direction sync to the Hub. After a few minutes all data was synchronized!:

Step 4 – Modify ServiceConfiguration to use the new database instead

I modified the ServiceConfiguration and published a new version of the app.

Step 5 – Shut down the old database server (after successful sync)

After all web role instances were online again after the changed connection string, I verified that new data was written to the new database, executed one final data sync and then deleted the old database.

Done!

I now have the following geographical infrastructure:

Next thing to tackle is moving Azure Storage (Queues & Tables) from North Europe to North Central US, but that challenge is for another day and another post…