Migrating a SQL Server Database to SQL Azure

As a follow on to my colleague’s recent excellent post I thought that this week I would present a practical, hands-on example of moving a real-world on-premise SQL Server database to SQL Azure.

There are at least three ways to migrate data into SQL Azure:

1. SQL Script
2. Bulkcopy (bcp)
3. SQL Server Integration Services (SSIS)

Each has benefits and limitations. Don’t use the script option to move very large data volumes, for example.

Here we are going to take an approach based on kind of a combination of 1 and 3. We will script our database schema and then use SSIS to replicate the data to the cloud. Since there are some features in SQL Server 2008 that are not supported in SQL Azure we will have to do a little hand work to modify the code that is generated for us. We can minimize that hand work if we make some changes in the default options the wizard gives us before we generate the script.

Here is what I had to do:

1. In SQL Server Management Studio, right click the on-premise database  
2. Choose Tasks | Generate Scripts  
3. Change the default options so that
   1. ANSI Padding = False
   2. Convert UDDT to Base Types = True
   3. Set Extended Properties = False
   4. USE DATABASE = False
4. Delete all the stuff having to do with creating the database, etc. I have already created the database in the SQL Azure Developer Portal. 
5. Delete the unwanted or unsupported features. In my case these included:
   1. SET ANSI_NULLS ON
   2. SET ANSI_NULLS OFF
   3. ON [PRIMARY]
   4. NOT FOR REPLICATION
   5. PAD_INDEX = OFF, (
   6. WITH ( …
   7. TEXTIMAGE
   8. NONCLUSTERED
6. I moved some things around in the code so that tables were created before views, etc. Stuff like that just seemed to make sense to me. You also have to move the creation of things that other things depend on higher up in the code.  
7. I went through an iterative process of running the code, examining the errors, making changes, and running the code again. Yeah, it is a lot of code. It took me about 30 minutes to get it right. Your time may vary.

Once the schema exists on SQL Azure it is straightforward to replicate the data with SSIS. The wizard pretty much takes care of everything!

Click here to view the screencast:

http://www.youtube.com/watch?v=SW3TcF4W1Ws

Okay, so perhaps it is a little more tedious than one would like. Somebody has to actually look at the code. That, I think, is part of the developer’s job. Yes, maybe it should be easier and maybe someday it will be but for now it is what it is. There is an interesting project on CodePlex that attempts to further automate this process. I recommend that you check it out.

If you get a chance, try this out yourself! Then consider attending Learning Tree’s Windows Azure Course!

Happy migrating,

Kevin