Introduction

In the spring of 2009, Microsoft introduced SQL Azure which is database service built upon SQL Server 2008 technology. In the early beta stages of SQL Azure, there was no easy way for users to migrate their on-premise SQL Server 2005 / 2008 database to SQL Azure. In order to ease migration of on- premise SQL Server database to SQL Azure a tool called SQLAzureMW was developed.

This paper provides an overview of SQLAzureMW and describes how you can use it to migrate your SQL Server 2005 / 2008 database to SQL Azure.

Compatibility
There are a lot of compatibility issues that you need to take into consideration before migrating your SQL Server database to SQL Azure. SQL Azure is built on top of SQL Server 2008. This means that if you want to migrate your SQL Server 2000 database or your SQL Server 2005 database to SQL Azure, you need to make sure that your databases are compatible with SQL Server 2008. For example, earlier versions of SQL Server support the definition of outer joins that use the = and = operators in the WHERE clause. This provides join operators in the FROM clause. When the compatibility level of the database is set to SQL Server 2005 or SQL Server 2008, the outer join operators (= and =) are not supported. This is only one example of compatibility issues that you will find from migrating from SQL Server 2000 / 2005 to SQL Server 2008. It is not the purpose of this whitepaper to discuss in detail the migration process from SQL Server 2000 / 2005 to SQL Server 2008. If you are interested in best practices around migrating your SQL Server 2000 / 2005 database to SQL Server 2008 please check out “Ultimate guide for upgrading to SQL Server 2008”.

You will find that the best migration experience for migrating to SQL Azure is migrating a SQL Server 2008 compatible database. This means that if you want to migrate your SQL Server 2000 database to SQL Azure, then you need to upgrade your database to SQL Server 2008 before you migrate to SQL Azure. If you want to migrate your SQL Server 2005 database to SQL Azure, you will still have to make sure that your database / stored procedures / TSQL code is still compatible with SQL Server 2008. Microsoft has a great tool called “SQL Server Upgrade Advisor”. The upgrade advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to identify features and configuration changes that might affect your upgrade. It provides links to documentation that describe each identified issue and how to resolve it. Once you verified that your database is compatible with SQL Server 2008, you can migrate your SQL database to SQL Azure.

Now, that said, SQL Azure does not support 100% of new SQL Server 2008 functionality. For example, rowguid and filestream are not currently supported in SQL Azure. SQLAzureMW will do its best to identify compatibility issues, fix them where possible and notify off all issues that it knows about.

SQL Azure Migration Wizard
SQLAzureMW is a tool that will help you migrate your SQL Server 2005 / 2008 database to SQL Azure. It looks for patterns that have been identified as incompatible and flags incompatibility issues and fixes them when possible. Also note that SQLAzureMW uses Regex and does a pattern search. It is not a full TSQL parser and can thus miss something or report something that is invalid when it is not. For example: if you have a variable declared in a stored procedure named @rowguid, SQLAzureMW would find rowguid and report that it is not supported. While that is true, the use of as a variable name is not. So, when you look through the analysis, be sure to look at the warning and validate that the TSQL flagged. If it look ok to you, then ignore the warning and let it run against SQL Azure.

For full documentation see SQLAzureMW documentation found on http://sqlazuremw.codeplex.com/releases/view/32334

Last edited Jan 20, 2011 at 8:14 PM by ghuey, version 1

Comments

kiquenet Jul 12, 2012 at 10:53 PM 
any good screenshots ?