Introduction

SQL Database Migration Wizard (SQLAzureMW) is designed to help you migrate your SQL Server 2005 (or greater) databases to Windows Azure SQL Database. SQLAzureMW will analyze your source database for compatibility issues and allow you to fully or partially migrate your database schema and data to Azure SQL Database.

7 Steps to an HA Environment:
This project will take you through the following 7 steps as you migrate into a High Availability database environment.
  • Step 1 – Setup
Ensure that SQL Server client tools are installed on a development machine (where you will be running SQLAzureMW).
Note that there are three version of SQLAzureMW:
# SQLAzureMW v3x -- built on SQL Server 2008 R2 SP1 client tools
# SQLAzureMW v4x -- built on SQL Server 2012 client tools
# SQLAzureMW v5x -- built on SQL Server 2014 client tools
So, depending upon the version of SQL Server you are running, pull down the correct version of SQLAzureMW. Download SQLAzureMW and move files to a local folder.
  • Step 2 – Analyze your database for compatibility with Azure SQL Database
The first thing that you will want to do is check and see if there are any compatibility issues between your on-premise SQL Server database and Azure SQL Database. You can use SQLAzureMW to analyze your source database and it will flag any potential compatibility issues that should be resolved prior to migrating your database to Azure SQL Database.
Note that during this process you can use the advanced options to tell SQLAzureMW to:
* Generate / Analyze database schema only
* Generate / Analyze database schema and export data
* Export data only
If you select to export the data from your tables, SQLAzureMW will use BCP to export the data.
  • Step 3 – Modify database script (or source database) to resolve compatibility issues
SQLAzureMW will generate the database schema (tables, views, stored procedures …) during the analysis phase of your database migration while flagging potential compatibility issues. At the time that the analysis phase is completed, you can save off the report and refactor your database or you can modify the script (leaving the source database alone) and then have SQLAzureMW execute that script against Azure SQL Database.
  • Step 4 – Create a Windows Azure account (if you don’t already have one).
Before you can actually create an Azure SQL Database, you will need to have a Windows Azure account. Here are three options for signing up for Windows Azure:
1) Pay-As-You-Go - Purchasing Options
2) Members (MSDN, BizSpark, or Microsoft Partner Network)
3) 90 Day Free Trial
Once you have created your Windows Azure account, you can create your SQL Database Server specifying the subscription name, the hosted region and the administrator name / password information.
  • Step 5 – Create Azure SQL Database
After you have verified that all compatibility issues have been address, you can use SQLAzureMW to create you’re database. This is done by specifying your Azure SQL Database target server and then clicking on the “Create Database” button. A dialog box will displayed allowing you to specify database name, database collation, database edition (web or business) and database maximum size (1 GB or 5 GB for web edition or 10 GB, 20 GB, 30 GB, 40 GB, 50 GB, 100 GB or 150 GB for business edition).
  • Step 6 – Execute database script against target server
Once your database is created, you can tell SQLAzureMW to execute the script generated in Step 2 against the target database (as well as upload data via BCP if selected in advanced options). SQLAzureMW will execute the script and report any errors in the results window. Any errors will be displayed in red text so you can watch for any errors that might be encountered.
  • Step 7 – Validate
Your database is now in Azure SQL Database. It is usually a good idea to visually check your database to make sure that the process worked according to plan. You can use SQL Server Management Studio (SQL Server 2008 R2 SP1or greater) to connect to Azure SQL Database and check your database objects and data. You can also use Azure SQL Database management portal to examine your database objects and query your data.
Screencast
Watch the following screencast and follow-along as my colleague Dave Bost and I step thru the wizard with you.

Compatibility
There are a lot of compatibility issues that you need to take into consideration before migrating your SQL Server database to Azure SQL Database. Azure SQL Database is built on top of SQL Server 2014. This means that if you want to migrate your SQL Server database to Azure SQL Database, you need to make sure that your databases are compatible with SQL Server 2014. 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 older versions SQL Server to SQL Server 2014. It is not the purpose of this whitepaper to discuss in detail the migration process from older versions SQL Server to SQL Server 2014. If you are interested in best practices around migrating your SQL Server 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 Azure SQL Database is migrating a SQL Server 2008 R2 SP1 compatible database or later. This means that if you want to migrate your SQL Server 2000 database to Azure SQL Database, then you need to upgrade your database to at least SQL Server 2008 or later before you migrate to Azure SQL Database. If you want to migrate your SQL Server 2005 database to Azure SQL Database, you will still have to make sure that your database / stored procedures / TSQL code is still compatible with SQL Server 2014. Microsoft has a great tool called “SQL Server Upgrade Advisor”. The upgrade advisor analyzes instances of older versions SQL Server 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 2014, you can migrate your SQL database to Azure SQL Database.

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

SQL Database Migration Wizard
SQLAzureMW is a tool that will help you migrate your SQL Server database to Azure SQL Database. 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 Azure SQL Database.

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

Last edited Jul 24 at 9:56 PM by ghuey, version 6

Comments

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