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:
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:
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:
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!