When converting a database from an older version of Microsoft SQL to Azure, there will be many gotchas along the way.  I’d like to help you learn from the troubles I had along the way, hopefully sparing you a bit of time that was lost during my first conversion.

Getting Started

I’m going to assume you already have your account, and have already set up the database and firewall settings for your Azure server.  If you haven’t please visit http://sql.azure.com and follow their getting started guide.  This will walk you through each of the steps you’ll need to have completed before any of the following article will help.

To get started developing in Azure you could either build a database from scratch, or “export” your current database to your azure server.  Since I have several databases that I’ve built throughout the years I figured I’d start my development by upgrading an existing database to Azure.

Scripting Your Database Design to Azure

I opened a SQL Management Studio 2008 R2 (I call him Ar-too!)  And right clicked on my database and choose Tasks -> Generate Scripts.  Skip the introduction screen and head right on in to select the database objects to script.  My first instinct was to script out the whole database, and do it all in one go.  After attempting to run that on my Azure server, and receiving many errors, I decided to script only what I needed.

Choose “select specific database objects” and then select tables, then hit next.  You can save this script to a file, but since this was a one shot deal, I chose to save to clipboard.  Paste that into an open connection from SSMS to your Azure server, and then hit either Parse, or Execute…Either way, You may find some of the following errors.

Msg 40514, Level 16, State 6, Line 1
'Filegroup reference and partitioning scheme' is not supported in this version of SQL Server.

Most of your table definitions will have an “on [FILEGROUP] ” option.  You can’t have that in Azure.  Remove all of them from your script.  Ctrl + h is your friend!

Msg 40517, Level 16, State 1, Line 15
Keyword or statement option 'pad_index' is not supported in this version of SQL Server.

If you have “WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)” at the end of your create table statements, remove all the extra options with the possible exceptions of STATISTICS_NORECOMPUTE and IGNORE_DUP_KEY.  Very few of these have been proven compatible with Azure.

The rule of thumb I am following for now is “Use as simple a create statement as possible to create your tables in Azure.

CREATE TABLE tableName (
   columnName datatype )

I’ll push for more advanced create statements as I dig deeper into Azure.

Copying Your Data to your Azure Database

Next I wanted to get some data onto my new server so I could do some simple performance testing comparing the exact same queries in my test environment against the new server.  I chose to use the SQL Server Import and Export Wizard.  The only catch I found for connecting to the Azure server is you need to choose .Net Framework Data Provider for SQL Server as the connection type.  Other types don’t seem to work yet.

I chose all the tables I thought I would use in my testing, and hit next all the way to the end… Then I received some errors.

Messages
Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion,
the message returned from the provider is: Tables without a clustered index are not
supported in this version of SQL Server. Please create a clustered index and try again.
(SQL Server Import and Export Wizard)

It appears that if you are copying mapping tables to azure, you’re going to have to develop them in such a way that they are organized on the disk.  Usually my mapping tables are just heaps.  But for now, it appears I’ll have to rebuild my tables (in both environments) to physically ordered by the map columns.  Since this is a big change to my test, I’m off to upgrade my maps to have clustered indexes.

If you’ve had any additional issues migrating your database from local servers to Azure, please add them to the comments below.  The more we share, the more time we can save!  And, as always, if you have any questions, please send them in!