November 29, 2011

How to Migrate Data between On-Premise SQL Server 2008 R2 and SQL Azure Without Getting a Migraine

I love my job at Microsoft, but there are some times when we simply make it really damn hard for people to do business with us. Migrating data from an on-premise SQL Server to SQL Azure is sadly one of those lapses where, for whatever reason, we’ve left people who aren’t full time SQL Server DBAs totally lost in the wilderness with a set of poorly documented and often dysfunctional tools.

After spending a couple of hours shaving yaks myself tonight trying to move a ~250mb data set (small, but not trivially small) from one SQL Azure database to another, I thought it would be best if I documented what I did to make it work.

Scenario: I needed to download an expensive dataset on one SQL Azure account and migrate it to another on a different SQL Azure subscription, but I needed to make and test some schema changes against the data set locally before I exported it back to its final resting place.

Seems simple enough, right? I just need to:

  1. Download the SQL Azure Database to my local system;
  2. Make a back-up of the dataset locally in case I screw up the other one during my schema changes;
  3. Finish making / testing / integrating schema changes;
  4. Deploy to SQL Azure on new subscription.

Sounds pretty easy to me, as it would any sane developer who’s had some experience working with the Microsoft server stack since 2000.

Rather than tell you the things I spent hours trying that don’t work I’ll explain what does.

Before you do any of this, make sure your SQL Azure Firewall has an exception for whichever development / backup machine you’re using.

Backing Up SQL Azure to On-Premise SQL Server 2008 R2: RedGate’s SQL Azure Backup

SQL Azure Backup from RedGate solved my first problem painlessly – currently SQL Azure Backup is free so grab a copy while you still can.

All you have to do is point it to your target database on SQL Azure and your target on-premise SQL Server database (screenshot taken from RedGate.)

That’s about as complicated as it gets.

Pushing from SQL Server 2008 R2 to SQL Azure: SQL Server Management Studio Data Export (Requires Magic)

One of the SQL Azure migration techniques Microsoft recommends is using the SQL Server Import and Export Wizard to push your data to SQL Azure. Unfortunately they leave one critical part out, which I will show you.

You begin by selecting your database in Management Studio (I’m using the Express edition.)

image

And for the second step you can leave all of the client / connection settings as-is for your on-premise SQL Server (shown below.)

image

And now we come to the part that the MSDN documentation totally left out… If you’re like me, you’ll naturally try to login using the SQL Server Native Client 10.0 datasource for your export target, because hey, makes sense right?

image

As it turns out, you need to use the .NET Framework Data Provider for SqlServer and change the following fields:

  1. Set Security –- Encrypt to true;
  2. Set Security – Password to your SQL Azure login password;
  3. Set Security – User ID to your SQL Azure user id without the @servername at the end;
  4. Set the Source – Data Source property to the servername.database.windows.net – no need to specify any of the TCP or port nonsense here; and finally
  5. Select the Source -- Initial Catalog to be [SQL Azure database you’ve already created on your service but haven’t necessarily set a schema for yet.]

Once you’ve cleared this hurtle it’s pretty much smooth sailing.

If you have any questions about this process or why I didn’t mention some of the available alternatives, go ahead and ask them in the comments and I’ll get back to you.

Discussion, links, and tweets

I'm the CTO and founder of Petabridge, where I'm making distributed programming for .NET developers easy by working on Akka.NET, Phobos, and more..