SQL Database Migration To SQL Azure

Many customers are asking about migration from SQL to SQL Azure. This post will describe the considerations you have to take and the migration process.

First of all, be familiar with SQL Azure limitations:

• Transact-SQL Reference (SQL Azure Database): https://msdn.microsoft.com/en-us/library/ee336281.aspx
• Guidelines and Limitations (SQL Azure Database): https://msdn.microsoft.com/en-us/library/ee336245.aspx

SQL Azure does not run in backward compatibility mode (SQL Server 2000 or SQL Server 2005). It is running compatibility mode 10 (SQL Server 2008).  Thus, all deprecated SQL Server 2000 and 2005 features will not work in SQL Azure (for the most part, there might be a few exceptions). 

If you work with SQL 2000/2005, first upgrade it to 2008 or SQL 2008 Express and get everything working there first. Then go through the migration process to SQL Azure.

Note that there are TSQL commands that are not supported by SQL Azure. There is a tool (SQLAzureMW) in codeplex (https://sqlazuremw.codeplex.com) that will analyze your database and also analyze SQL Profiler trace files (to cover your dynamic generated SQL) and tell you what is not compatible.  From there, you have a great start on figuring out what you need to do in order to get your database in SQL Azure.  Once your database is compatible, you can also use SQLAzureMW to actually migrate your database for you to SQL Azure. (SQLAzureMW requires SQL Server 2008 R2 CTP).

My recommendation is to start with SQLAzureMW and let it give you a rough idea of how much work you will need to do.  Then you can decide if you need to go through the upgrade process to SQL Server 2008 first before you go to SQL Azure.

Have fun!