Azure Tools Belt: SQL Azure Migration Wizard

Labels: , , , , , , ,

One of the questions that clients often ask is: What tools could be used for Windows Azure development. Everyone knows that you need a web browser and a code editor (ex: Visual Studio), but what else? So, I decided to do a serie of posts to present them. I’m therefore beginning with the indispensable SQL Azure Migration Wizard, since it’s used more often at the beginning of a project.

This serie is not meant to be an exhaustive list of all tools. Some other excellent tools are surely available. If you think I have forgotten one or want me to talk about one, let me know. I will be more than happy to adding it to the list.

What is SQL Azure Migration Wizard?

Most of the Azure projects that are starting these days are migration project or at least contain some data migration. The SQL Azure Migration Wizard, like its name implies , is a tool that will take you be the hand and help you migrate your database. In a few clicks the wizard will bring the database schemas and the data to the cloud.

Because Azure SQL database is very similar, but not totally compatible to Windows SQL Server 2008,the Wizard will also provide you a list of things that you should take care of like a missing clustered key.

How to use it

First, I will assume that you already have an active Azure account, if you don’t, please follow the instructions on this web page. Then of course you will need the SQLAzureMW tool that you can download on the CodePlex page project. Let’s do a simple migration from a local database to SQLAzure.

clip_image001[1] To get started, just double-click on the executable.
Some options will be proposed, but in our case we need to select: Analyse / Migrate SQL Database (second choice from the top)
clip_image002[1] Then pick your source. One nice thing with keeping the Master database selected, the list of all available databases on the specified server will be populated. Once you are done, click the Connect button.
clip_image003[1] The database used in this demo is a subset of Northwind database with three tables: Products, Suppliers, and Categories.
Once you have selected what you wanted to migrate, click the Next button.
clip_image004[1]
clip_image005[1]		 Here three outputs are generated:
  1. A result Summary: This explains all the steps made to extract the schemas and data of the database. The analysis will also highlight some incompatibilities and suggest some modifications. In this case, since I have not added any primary keys or indexes, it warned me that a clustered index will be added.
  2. SQL Script: A nicely written database creation script that you can save for maintenance.
  3. Data file: Since one of the best way to import massive data in AzureSQL database is by using BCP, some data files have been prepared to be imported.
Click the Next button to continue.
clip_image006[1] For the next step you will need a database server in Azure. So connect to the Azure portal, and from the left panel select Database.
You can than create a new server by clicking the Create button. You could also use a server that you already have.
Put the server name in your clipboard you will needed.
clip_image007[1] Back to our SQLAzureMW, paste the full name of the server in the Server name dropdown. It should be something like xxx.database.windows.net
Then enter the credentials of your SQL server. Note that the username should be follow by “@” and the first part of the server name.
Click Connect when you’re done.
clip_image008[1] Since no database was created, the list is empty. Let’s create a new database by clicking the Create Database button.
A popup window will ask for the name, location and format of the database to be created.
Click Create Database, and after few seconds you should be able to click the Next button to continue.
clip_image009[1] To see that the new database is really created go on the AzurePortal: you will see it in the database section.
clip_image010[1] After a quick confirmation the migration will be done.
A Nice summary is displayed so you can see if something went wrong.
The migration has been performed successfully, click Exit.
clip_image011[1] You can now connect to the new database by SQL Server Management Studio or by the Azure Management Portal.
You can see that the three tables are there and the data has also been migrated.
clip_image012[1] Like it was supposed to, the index was successfully created. This is really magic!


Conclusion

To conclude, let’s say that this tool is really fantastic and that you should definitely try it if you are planning to do a migration. It will save you a lot of time. Please note that the SQLAzureMW does not manage logins and users. I therefore strongly suggest using the Azure User Management Console – AUMC, available on CodePlex.

If you have any comments, suggestions or experiences to share, feel free to let me know by adding a comment, by e-mail or by the contact page. Next post is about the Windows Azure Auto-scaling Application Block (WASABi)


~Frank


References