Migrating SQL Server Database Schema to SQL Azure

Despite being similar to SQL Server in many aspects, SQL Azure has a different principle: it is a cloud service. All the physical administration is handled by Microsoft, which means that the commands/parameters related to physical resources are not supported. This includes backup and restore, as the file system is not available and all the data is automatically replicated. Besides the physical-related commands, SQL Azure also doesn’t support some SQL Server data types and other features, such as server options, CLR hosting and distributed transactions.

Having these restrictions in place, SQL scripts that were written (or generated) targeting SQL Server may not work on SQL Azure. To circumvent this issue, SQL Server Management Studio (SSMS) 2008 R2 includes an option to script database objects to “SQL Azure script”. On the “Generate Scripts” wizard one may choose the database engine type. When SQL Azure is selected, some of the other options become disabled.

The funniest thing is that, in my scenario, some of the generated scripts still had unsupported commands/options! I decided to give a try on SQL Azure Migration Wizard. This tool allows you to analyse existing databases or scripts and migrate between SQL Server and SQL Azure (two-way) while ensuring compatibility. Nevertheless, I still got one or another situations where unsupported commands passed through.

The strategy that suited me was to generated a script on SSMS and the run it through SQLAzureMW. A bit more work, but also a double check.

Additional info:

Main differences between SQL Azure and SQL Serer 2008

Migrating Data to SQL Azure Using the Generate and Publish Scripts Wizard

Unsupported Transact-SQL Statements (SQL Azure Database)