Migrating your existing on-prem SQL Server database to Azure SQL DB

If you are in the process of moving an existing .NET application to Azure, it’s likely you’ll have to migrate an existing, on-prem SQL database as well. There are a few different ways you can go about this, so let’s go through them.

Data Migration Assistant (downtime required)

The Data Migration Assistant (download | documentation) is free, easy to use, slick and extremely powerful! It can:

Evaluate if your database is ready to migrate and produce a readiness report (command line support included)
Provide recommendations for how to remediate migration blocking issues
Recommend the minimum Azure SQL Database SKU based on performance counter data of your existing database
Perform the actual migration of schema, data and objects (server roles, logins, etc.)

After a successful migration, applications will be able to connect to the target SQL server databases seamlessly. There are currently a couple of limitations, but the majority of databases shouldn’t be impacted. If this sounds interesting, check out the full tutorials on how to migrate to Azure SQL DB and how to migrate to Azure SQL DB Managed Instance.

Azure Data Migration Service (no downtime required)

The Azure Data Migration Service allows you to move your on-prem database to Azure without taking it offline during the migration. Applications can keep on running while the migration is taking place. Once the database in Azure is ready you can switch your applications over immediately.

If this sounds interesting, check out the full tutorials on how to migrate to Azure SQL DB and how to migrate to Azure SQL DB Managed Instance without downtime.

SQL Server Management Studio (downtime required)

You are probably already familiar with SQL Server Management Studio (download | documentation), but if you are not it’s basically an IDE for SQL Server built on top of the Visual Studio shell and it’s free! Unlike the Data Migration Assistant, it cannot produce readiness reports nor can it suggest remediating actions, but it can perform the actual migration two different ways.

The first way is by selecting the command “Deploy Database to Microsoft Azure SQL Database…” which will bring up the migration wizard to take you through the process step by step:

The second way is by exporting the existing, on-prem database as a .bacpac file (docs to help with that) and then importing the .backpac file into Azure:

Resolving database migration compatibility issues

There are a wide variety of compatibility issues that you might encounter, depending both on the version of SQL Server in the source database and the complexity of the database you are migrating. Use the following resources, in addition to a targeted Internet search using your search engine of choices:

In addition to searching the Internet and using these resources, use the MSDN SQL Server community forums or StackOverflow. If you have any questions or problems just leave us a comment below.