# Thursday, October 8, 2009

Last month I showed how to manually migrate schema and data from SQL Server 2008 to SQL Azure. I also mentioned the SQL Azure Migration Wizard developed by George Huey in that post. Since then George has updated the tool (currently on 0.2.7) and it now has some great features that make it far more compelling than creating and fixing scripts generated by SQL Server.

To get started, the tool will allow you to select a SQL Server database and then ask you to select which objects to migrate.

image

It will then do an analysis of your database and attempt to fix the problems. For example, if you have a table with a TEXT column, it will automatically make it a varchar(max) and will also unbind XML schemas and UDTs. It will remove keywords (like PAD_INDEX as shown below) not supported by SQL Azure.

image

After you connect to SQL Azure, the tool makes a script for you. You have the option to run the script as is, or you can save it to disk, or you can modify it in the window and run it in real time. The scripts have good comments in there telling you about some potential problems like XML data types, etc. After you play with the script, you can execute it and the tool will let you know the success or failure of the script.

image

Now that you have migrated the schema, the hard part is over. Next you can migrate the data. The easiest way to do this so far is via Red Gate SQL Compare as I showed last time or via scripting the data via the SQL Server script wizard. To get there right click on a database in SQL Server 2008’s SQL Server Management Studio and select Tasks|Generate Scripts. After you select your database you are brought to the Choose Script Options dialog. Turn off all of the other options except “Script Data” and choose on the next screen only the tables you have moved using the SQL Azure Migration Wizard.

image

After you choose the tables to move over, you can then choose to send the script to a new window. You will notice that the only thing that was scripted was the INSERT INTO statements.

image

Now select the tab that contains your query and then choose Query|Connection|Change Connection from the main menu. Enter in the login credentials you have for your SQL Azure account and then click on the Options tab and enter in the name of the database you are connecting to. After you connect you can attempt to run your query. Just make sure that your script will be compatible with SQL Azure before you run it. Meaning if you removed any XML data types, etc, using the Migration tool, you will have to do the same to your script. There is always some cleanup to run, but is pretty straight forward. For really large databases, you may want to highlight sections of the script and run it a table or so at a time to prevents timeouts to SQL Azure. You will also have to make sure that you arrange the script to sequence the INSERTs to coincide with the foreign keys constraints. SQL Server is smart enough to put Order before Order Details, but not all objects does it do this for you.

image

When you are done you can view your database using the SQL Azure Manager I talked about yesterday.

image

Enjoy!

Technorati Tags: