Three tips for a better SQL Azure migration

The right tool for your SQL Azure migration
There are three main approaches for migration. You can use the Generate and Publish Scripts Wizard in SQL Server Management Studio (SSMS), or you can use the SQL Azure Migration Wizard, a free, open source solution on CodePlex. You can even use SQL Azure Data Sync to generate your schema objects and then synchronize the data.

Whichever method you use, you must account for the fact that SQL Azure does not support all features in SQL Server. For instance, you cannot implement user-defined data types or extended properties in SQL Azure. These limitations don’t prevent you from using a particular migration method, but they might impact your decision on which method you use.

To migrate a SQL Server database to SQL Azure, you most move both the schema and data. Not surprisingly, the schema must exist within the SQL Azure database before you can copy data into that database. One method you can use to define the schema in the target database is to generate a script file or files in SSMS based on the database you want to migrate. SSMS includes the Generate and Publish Scripts Wizard, which automatically generates a Transact-SQL script file (.sql) that you can run against the SQL Azure database.

When you run the wizard, you must set several of the advanced options to ensure that the script will generate a schema that conforms to SQL Azure standards. For instance, you should set the Convert UDDTs to Base Types option to True if your on-premises database contains user-defined types. There are many details on how to set your script options.

If you’re working in SQL Server 2008 R2, one of the advanced options in the wizard lets you specify SQL Azure as the target database type, in which case many of the options are already preconfigured to support a SQL Azure implementation.

In addition, SQL Server 2008 and later versions let you specify whether to script only the schema, only the data, or both. If you include the data, the script will contain a series of INSERT statements that will add the data to the target database.

Another option you have for migrating your on-premises database is to use the SQL Azure Migration Wizard, available on CodePlex. Like the Generate and Publish Scripts wizard in SQL Server 2008 or later versions, the SQL Azure Migration Wizard can migrate both the schema and data. The SQL Azure Migration Wizard is supported by the development community, but not by Microsoft itself. Even so, many users have successfully used the tool to migrate their databases and the data.

As you step through the wizard, the tool will generate a script file, analyze the Transact-SQL code in the file, and let you make any changes necessary to ensure that the code conforms to SQL Azure standards. You can then run the script against the SQL Azure database from within the wizard or run the script manually. Keep in mind that the script analysis function does not catch all compatibility issues. For example, SQL Azure requires a table to have a clustered index before you can insert data into that table. However, you can still create the table without the index, so the wizard’s analysis will find nothing wrong with the script. It’s not until you try to insert data that you discover there’s a problem.

You can also migrate your database by using SQL Azure Data Sync, a database synchronization service available through the Windows Azure platform. The service will create your schema objects and load the data from your on-premises databases. You can choose which database objects to include and you can create filters that let you synchronize only a subset of data.

Note, however, that SQL Azure Data Sync will synchronize only those database objects that conform to SQL Azure standards. For example, if a column is configured with a user-defined data type, that column will not be included in the syncing process and the objects will not be included in the schema. For any objects you cannot migrate automatically, you must explicitly create them in the SQL Azure database.

Copying your data to SQL Azure
The three migration methods described above all support the ability to migrate data along with the schema, with the obvious exception being the Generate and Publish Scripts Wizard in pre-2008 versions of SQL Server. However, even if you use a tool that supports data migration, you might still want to control when and how data is loaded in the target SQL Azure. For instance, if your database contains a significant amount of data, you might want to bulk-load chunks of data in parallel or control the batch size of your data load.

SQL Server supports several methods for loading data into a SQL Azure database, in addition to the migration tools described above. For example, you can use the bcp utility to bulk-load data to the database. When you use this method, you must specify the full path to the SQL Azure server for the server option, as in -S servername.database.windows.net. In addition, you must specify the username and password to the Azure account.

You can also use the SQL Server Import and Export Wizard to load data into the SQL Azure database. You can access the wizard directly through the Windows Start menu on a SQL Server computer or launch the wizard from within SSMS or SSIS. The wizard walks you through the steps necessary for loading the data into a SQL Azure database. Note that, for your destination provider, you should use .Net Framework Data Provider for SQL Server. In addition, the destination data source should be the name of the SQL Azure instance, and the initial catalog should be the name of the SQL Azure database.

For more granular control over your load operations, you can use SSIS to create a package that targets the SQL Azure database. SSIS lets you control task execution so you can determine what data to load and when to load it. You can also set up error handling to help you troubleshoot problems that might arise during the load operation. If you do use SSIS, Microsoft recommends that you use the ADO.NET or ODBC drivers for your connection managers and destination components.

Assessing your SQL Azure migration
Recently, Microsoft announced a new experimental service called the SQL Azure Compatibility Assessment Tool. The service is free, but to use it you must first generate a .dacpac file based on the database you want to migrate. To create the file, you must use the CTP4 version of SQL Server Data Tools. You can then upload the file to the SQL Azure Compatibility Assessment site. The service usually completes the analysis within 60 seconds. You can then review a report that explains any compatibility issues.

If you try this service, make sure the database you create in SQL Azure is ready to go when you start loading your data. The key to successfully migrating to SQL Azure rests with ensuring that your schema conforms to SQL Azure standards. Each method available for migrating the schema has its advantages and disadvantages, and no one method can guarantee a flawless migration. For that reason, you should take any additional steps necessary to make certain your on-premises database or the script you generate from that database is compatible with SQL Azure. Don’t assume that an automatic process will address all potential issues. It’s up to you to ensure a smooth and problem-free migration.

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Check out his blog, Slipstream.