Transferring a Database to SQL Azure: The Magic Handshake

Update 8/30/2011: I'm leaving this post as-is for reference; however please be aware that the best way to transfer a database to SQL Azure is to use the SQL Azure Migration Wizard

Transferring an existing SQL Server database to SQL Azure can be very easy if you know the right tools and one essential configuration detail, or what I like to call, "the magic handshake." SQL Azure is still near the bleeding edge, and if you get some bad advice you could spend hours on this. Here's a quick rundown on how to do it the easy way.

The Magic Handshake

There are several ways to get your database up to a SQL Azure instance, but the most painless is the SQL  Import and Export Wizard. However, if you don't know the magic handshake, your experience goes something like this:

1. You start up the Import and Export Wizard, either from the start menu or from SQL Management Studio. You make sure to use the SQL Server 2008 R2 version, because you know that plain old SQL Server 2008 can't talk to Azure.
2. You set your source data to the database you want to export to Azure.
3. When setting your destination, for some reason you can't log in to your Azure instance. Undaunted, you do some quick Googling (with Bing, of course) and find out that you have to include the full name to your server in your user name, e.g. Bullwinkle@reallyweirdname.database.windows.net. With that change you actually log in. And you can select the target Azure database! The excitement is building now! With a tremendous sense of anticipation, you click Next.
4. Boom! you're out of luck:

The wizard says it, "cannot get the supported data types from the database connection," and that, "the stored procedure required to complete this operation could not be found on the server." But what it should have said is, "you didn't give me the magic handshake."

So what is the magic handshake? It happens back at step 2. You have to select .Net Framework Data Provider for SqlServer for your destination. This will give you a distinctly non-wizard like settings screen:

Does this look like a wizard to you?

But it's pretty simple: just fill out the Data Source, Initial Catalog, User ID, and Password the same as you would in a connection string. Also, you can use the simple User ID without the @server suffix. Click Next again and you're off to the races!

Azure Compatibility

If your database is simple enough, you may not need to make any changes to your schema. In fact, I would recommend that unless you know you're schema is too clever, go ahead and grind through the Import and Export Wizard steps I've outlined above. If you have any Azure computability problems, you'll get detailed error messages from the wizard.

If you do get errors, then you won't be able to let the wizard create your tables for you. That means you'll have to script the database schema first and correct the errors that were mentioned in the import error dialog. For example, Azure doesn't like tables that don't have any clustered indexes defined. To fix this, just change the primary key on each table from non-clustered to clustered, run the creation scrip in your Azure DB, then run the Import/Export wizard. It will pick up on the existing tables and use them instead of trying to create new tables for the import.

I decided to write a post on this because there doesn't seem to be a lot of good information available on migrating SQL Server databases to Azure. Now that I've been through the process, it seems very simple. That is, once I learned the magic handshake.