Last time I walked you through how to see if your database will work with Windows Azure SQL Database (WASD) using the Migration Wizard on Codeplex. It’s an easy tool to learn to use, and it answers the question what won’t work if I migrate to SQL Azure. It will even help you perform the migration too, copying all the schema and data from your database to a new Azure Database. But if you find you have to make changes to your database before migrating to Azure, you’ll find the Migration Wizard lacks a lot of functionality that will prove useful during the process. That’s why I moved SQL Server Data Tools (SSDT).
Let’s take the same AdventureWorks2008R2 Database from the previous post, and test it for migration using SSDT. Before trying to follow along, make sure you have downloaded and installed SSDT on your machine. I’ll be using Visual Studio 2012, but I’ve used SSDT in 2010 as well and all the functionality appears to be the same to me.
First, let’s create a new database project.
Next, right click on your project in the Solution Explorer and click Import -> Database.
You’ll then have to create a new connection to your database. In my case I’ll connect to my localhost copy of AdventureWorks2008R2. Then, for Import Settings I’m only going to capture the application scoped objects. I’m not concerned with security objects during this test.
I would be careful trying to migrate database settings from on-premise to Azure. Since you’re not going to be able to set up file groups, etc, don’t even try to import those if you’re using them. It’ll just save you hassle later in the migration project.
Once you’ve connected and set your options, just hit start to begin the import. You’ll get a console showing you the import progress. When it’s complete, hit finish to close the console and return to the project.
Now you have all the objects from your database in your project. At this point I would advise you to save your project and commit this as the initial version of your migration project. This is your starting point. You could deploy this project to another instance of your on-premise database server. But the source control conversation is a talk for another time.
Let’s talk about how we could use this project to see what will and won’t work in Azure SQL Database.
Right click on your solution in the Solution Explorer again, but this time click properties.
You’ll notice that the target platform on my screen is SQL Server 2012. If you imported from another version of SQL Server you’ll see that listed in the Target platform drop down. Select Windows Azure SQL Database, then hit save, or CTRL +S. That way the project now knows you’ve updated the target to WASD.
Hit View -> Error List. You’ll be presented with a list of all the objects and options you’re currently using that aren’t compatible with Azure SQL Database. This is the list of items you’re going to have to work through before migrating to Azure. I know I keep mentioning it, but being tied into Visual Studio lets you keep track of your updates (as long as you tie in version control). It also lets you assign tasks, if you tie in TFS. There are many added benefits in using SSDT to migrate your database.
And once you’ve addressed all the errors in that list, you can run a deploy against your target Azure Database and deploy your schema there! The only thing you really lose in moving from the migration wizard to SSDT is the easy way to deploy data from your source database to your new Azure database. You could always use the import export wizard, but the Migration Wizard will perform better. Since it runs using BCP, it consistently pushes more rows per second from my lab environment to Azure. My average is just shy of 30k rows per minute. Not screaming fast, but better than I see in SSIS, which runs around 12k rows per minute during my last tests.
I do admit I haven’t taken a great deal of time to performance tune my SSIS Package and see if I could squeeze more out of the throughput.
Once you’ve successfully deployed your schema to Azure SQL Database, you’re read for some integration tests. We’ll go through that next time. Until then, if you have any questions please send them in. I’m here to help!