Migrate from on-prem SQL server to Azure SQL Database

This blog describes the various approaches you can use to migrate an on-premises SQL Server database to Azure SQL Database.

In this migration process you migrate both your schema and your data from the SQL Server database in your current environment into SQL Database, provided the existing database passes compatibility tests.  Fortunately with SQL Database Version 12 (V12), there are very few remaining compatibility issues other than server-level and cross-database operations (see Azure SQL Database Transact-SQL differencesAzure SQL Database General Limitations and Guidelines, and Azure SQL Database resource limits).  Databases and applications that rely on partially or unsupported functions will need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.

If not done already, create a server to host the database you wish to migrate, and then set up a firewall rule for it (see SQL Database tutorial: Create a SQL database in minutes using sample data and the Azure portal).

Note: If you are fairly confident your database is compatible and it is not large a large database, you can skip testing for compatibility and just use the SSMS Migration Wizard.  If this wizard detects compatibility issues, errors will be displayed to the screen and the migration will not continue – you can then proceed to the steps below to test and fix the compatibility issues.  Or, you can run the SQL Azure Migration Wizard and it can fix most compatibility issues (the few that remain) while creating the SQL Database, so this could be the only tool you need to use since it also migrates the data.

The Microsoft Data Migration Assistant has an option to migrate from on-prem SQL Server to another version of SQL Server either on-prem or in a Azure VM (schema and data).  It does not yet support migrating to SQL Database.

Test compatibility

First you will want to test for SQL Database compatibility issues before you start the migration process:

Fix compatibility issues

If there are any compatibility issues, you will need to fix them before proceeding with the migration by using:

  • SQL Azure Migration Wizard, available on CodePlex, will generate a T-SQL script from the incompatible source database that is then transformed to make it compatible with the SQL Database.  It will then connect to the SQL Database and execute the script on the target database.  It also has options to analyze trace files to determine compatibility issues and the script can be generated with schema only or can include data in BCP format.  It will also process the body of functions or stored procedures which is normally excluded from validation performed by SSDT (see next option), so it may find issues that might not otherwise be reported by SSDT alone.  See the wizard in action via the video SQL Database Migration Wizard
  • SQL Server Data Tools for Visual Studio (SSDT).  Import the database schema into a Visual Studio database project for analysis.  Specify the target platform for the project as SQL Database V12 and then build the project.  If the build is successful, the database is compatible.  If the build fails, resolve the errors in SSDT.  Once the project builds successfully, you can publish it back as a copy of the source database and then use the data compare feature in SSDT to copy the data from the source database to the Azure SQL V12 compatible database.  You can then migrate this updated database
  • SQL Server Management Studio (SSMS).  Use SSMS to fix compatibility issues using various Transact-SQL commands, such as ALTER DATABASE

Migrate database

And lastly you will migrate the compatible SQL Server database to SQL Database.  There are several migration methods for various scenarios.  The method you choose depends upon your tolerance for downtime, the size and complexity of your SQL Server database, and your connectivity to the Microsoft Azure cloud.

To migrate schema and data from on-prem SQL Server (or Azure SQL Database) to Azure SQL Data Warehouse, use the Data Warehouse Migration Utility (Preview).

There is also a SQL Server Migration Assistant for Oracle, Sybase, DB2, MySQL, and Microsoft Access to migrate from SQL Server to Azure SQL Database.

More info:

Migration cookbook now available for the latest Azure SQL Database Update (V12)

Migrating a SQL Server database to Azure SQL Database

How to Migrate from On-Premises to Azure SQL Database

Migrating an on premise SQL Server Database to Azure

Free ebook: Microsoft Azure Essentials Migrating SQL Server Databases to Azure

What features are not supported in Azure SQL Database?

Azure SQL Limitations compared with a SQL Server Enterprise

Azure SQL Database Live Migrations

Migrating from SQL Server to Azure SQL Database using Bacpac Files

Migrating Databases to Azure SQL Database

Video training: Migrating SQL Server Databases to Azure

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Azure SQL Database, SQL Server, SQLServerPedia Syndication. Bookmark the permalink.

2 Responses to Migrate from on-prem SQL server to Azure SQL Database

  1. Nuno says:

    Dear James,

    I would like your help to solve a big problem that have to publish my web site on windows Azure with database.

    My web site was developed on visual studio 2010:
    MVC 4 and Entity framewrok 4.5, code first.

    I can publish the web site on Azure but the problem is that the migrations don’t recreate the database on the server side.

    I try copy the azure database connectionstring to my local webconfi.release and also, copy this connection string to the azure web app.

    But don’t work.

  2. Pingback: Migrate from on-prem SQL server to Azure VM IaaS | James Serra's Blog