Migrating AdventureWorksDW to SQL Azure using SQL Azure Migration Wizard

AdventureWorks

AdventureWorks is the latest of Microsoft's sample databases, and for the basis for a great number of sample applications (including our own Epic AdventureWorks demo.

AdventureWorks comes in several editions, and the only edition which has a direct Azure migration path is the LT edition (http://msftdbprodsamples.codeplex.com/releases/view/37304). This is a rather puny edition, with all of 32 orders in the database and lacking in some referential integrity. For my reporting demos, I prefer to use the DW edition, which is the data warehousing sample. I have that installed on a local SQL Server 2008 instance (with SQL Server 2008 R2 client bits installed), so we'll migrate from my local instance to our SQL Azure using the SQL Azure Migration Wizard.

SQL Azure Migration Wizard

You can get the SQL Azure Migration Wizard (SAMW) from http://sqlazuremw.codeplex.com/. SAMW comes packaged as a zip file-no installation is necessary, but you should read the documentation on Codeplex to understand configuration options and how it looks for features not supported by SQL Azure.

Is Migration Possible? Database Analysis

Most tables and views should migrate fine, as will most basic stored procedures. However, there are a lot of features in SQL Server 2008 not supported by SQL Azure; see http://msdn.microsoft.com/en-us/library/ee336250.aspx for the list of what is and what is not supported.

Before we do any migration, SAMW can analyze our database, scripts or profiler trace and report back on known unsupported features. Depending on the results, it's a judgment call on whether to continue with the migration.

After starting SAMW, we have a number of actions we can perform. For now, we're going to analyze our database to see if this is possible.

We're prompted for our database connection information. Leaving Master DB selected allows us to choose from a list of databases on the next screen.

Here we'll choose AdventureWorksDW, the database we wish to analyze.

We have the option of selecting specific objects, but in this case we want to migrate them all. The Advanced button opens a properties panel where we can set additional settings for the script generation. It's a good idea to look these over manually to make sure all the features we need will be scripted as we want.

The Script Wizard Summary page gives us a confirmation of all the options we've chosen. We can use the Back button to make any changes we need to.

One final confirmation and we're ready to go. The progress bar and results will update as the analysis is performed. The analysis took less than a minute for this database.

Once the analysis is done, we can review the results and check the resulting SQL Script.

The only red flag in this analysis is that EVENTDATA is not currently supported in SQL Azure, so our database DDL trigger will probably not function correctly. This incompatibility is also noted in the SQL Script, so it's up to us if we create this trigger or not.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
--~ DatabaseDdlTrigger [ddlDatabaseTriggerLog] -- EVENTDATA is not supported in current version of SQL Azure
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'ddlDatabaseTriggerLog')

That's not too bad, and for our migration, we'll just delete this trigger. It's not important for the demos I need to do. One additional action SAMW performed was to add a clustered index to any table without one. All tables must have a clustered index in SQL Azure, so we should review that list to make sure the recommended index makes sense.

At this point, we can save the SQL Script, but this script does not migrate the data, which we need to do. So we'll start the wizard over.

Prepare SQL Azure

If necessary, now would be a good time log in to the SQL Azure portal (http://www.sql.azure.com/) to create a database, add a database user and get the connection settings. Don't forget to add a firewall rule for your location!

Migration

This time, we'll choose to Analyze and Migrate a SQL Database. The connection and database selection steps are the same.

Remember that we do not want to migrate ddlDatabaseTriggerLog, so we'll choose to migrate only specific objects. The summary confirms we will not be migrating any triggers, since there was only one.

This time, in addition to analyzing the database and generating a DDL script, data are added to a bulk copy file. Instead of this being the final step, we can continue on to migrate the objects and data into SQL Azure.

We're prompted for our connection settings, and we can then choose the target database.

We're now asked to confirm the script execution, and the process of creating table objects and migrating data begins. It's pretty impressive to watch the speed of bcp, uploading nearly 11,000 rows per second at times. Total processing time was 4 minutes, 20 seconds.

Logging in through SSMS 2008 R2 confirms our database now exists in the cloud. Let the demos begin!

Thanks to the development team!

The SAMW development team deserves a huge round of thanks for producing such a great tool. Nicely done!