Five ways to migrate your on-premises SQL database to Azure

There are currently three deployment options for SQL Server Database on the cloud. Well, four, if you count Azure SQL Databases in elastic pool. But Azure SQL DB in elastic pool is essentially just Azure SQL Databases grouped into an elastic pool so you can maximize what you’re paying for and share the resources. Anyway, I digress. Right now, you can either put your SQL Server database in a SQL Server in an Azure VM, in a Managed Instance, or in an Azure SQL Database. Now, there are different considerations for choosing one over the other, but that’s a whole different blog post. In this post, we are going to focus on how you can move your on-premises SQL Server database to Azure. Here’s a neat table comparing the possible ways for the three:

SQL Server in Azure VM	Managed Instance	Azure SQL Database (Singleton/Elastic Pool)
Backup & Restore	Yes	Yes	No
Dettach/Attach	Yes	No	No
Mirroring/Availability Groups	Yes	No	No
Log Shipping	Yes	No	No
Replication	Yes	Yes	Yes
DMA/DMS	Yes	Yes	Yes
Bulk Load	Yes	Yes	Yes

Most every strategy you have for migrating SQL Server on-premises will work for SQL Server in Azure VM. This is the most common way of migrating to the cloud. It is easy since it is essentially just moving your old server to a new server. Managed Instance also allows you to restore Native SQL Server backups from on-premises SQL Server, so it is fairly compatible and moving is more or less smooth. The challenge comes when moving your existing SQL Server Databases to Azure SQL Database. Due to security reasons and infrastructure differences you cannot restore a SQL Server backup to Azure. So this often leaves a lot of people scratching their heads about how to move their existing databases to Azure SQL Database. Here are five ways to migrate your on-premises SQL Database to Azure:

1. Data Migration Assistant/ Data Migration Service

   This is the service/tool that Azure specifically created for this task. The tool can also perform assessments to identify feature parity and database compatibility issues when moving your SQL Server to any of the available deployment options for SQL Server from on-premises SQL Server to Azure SQL Database. It will present you with a report identifying all those, as well as possible fixes for the different Database Compatibility level available for your chosen target. For the migration, DMA/DMS has offline and online options. Offline allows you to either migrate just the schema or the schema and data in one shot. You would likely use this method for testing/POC, for small databases, or those applications that can afford longer downtime while you move the data. The online option allows you to keep the on-premises database and the Azure SQL Database continuously synchronizing after you have moved the initial schema/data. This way you have ample time for the Azure SQL Database to get caught up and then decide when to cut-over and start pointing your application to the Azure SQL Database. Microsoft's Doc has a detailed step by step on how to do both offline and online migration to Azure SQL Database.

2. Replication

   You can add an Azure SQL Database as a subscriber to a transactional replication. This can let you migrate the data and objects from your on-premises SQL Server to Azure SQL Database. Since you cannot restore backups, you can only initiate this replication from a snapshot. Similar to online migration for DMS, you can start your synching well before your scheduled migration and when everything is synchronized, decide when you can cut-over and re-point your applications to the Azure SQL database. One thing to note, you can only replicate tables with a primary key. So if you have a number of tables without them, you'll need to get creative or pair it with other techniques to move those tables.

3. DACPAC

   Dacpac is a single file containing the definitions for your database schema - tables, view, stored procedures, functions, and other database objects. Sort of like a schema, only backup. You can create and restore one to Azure SQL Database using SSDT, SSMS or SQLpackage.exe. Since this only migrates your schema, you'll need to pair it with other techniques to bring your data to Azure SQL DB. You'd likely use this for POC/test or for new applications that do not require any data to migrate. You can create a dacpac file and deploy one using SSDT, sqlpackage.exe or SSMS.

4. BACPAC

   BACPAC is similar to DACPAC but encapsulates both schema and data. So you can bring your entire database to Azure SQL Database. This single file stores the information in json format. This strategy is applicable to small databases. You can create a BACPAC file and import one using SSDT, sqlpackage.exe or SSMS.

5. Generate SQL Scripts

   Sql Server Management Studio has a Generate SQL Script UI. You can right click a database-> Tasks -> Generate SQL Scripts. If you've been working with SQL Server, you've probably used this before. What you may not have noticed before is when you click on Advanced, you can actually control what the script provides you. Specifically, you can change the Engined Type from Stand-Alone or Azure SQL Database. So the script provided is compatible with it. You can also actually specify on type of data to script whether to output Schema Only or Schema and Data. So for what it's worth, it is your TSQL version of DACPAC or BACPAC. The advantage of this is you can add/modify the resulting TSQL script as you need to. Similar to BACPAC, if you are scripting the data, it is applicable for small databases but not bigger databases. If you review the resulting script, it is actually writing your data as individual insert commands per row.

Azure provides a very helpful tool for planning your migration, the Azure Database Migration Guide. You can change the source and destination and it will provide you with a guide for the pre-migration, migration, and post-migration tasks that you need to be aware of as well as links to the resources and tools for each step. If you are planning a migration, be sure to check the page beforehand to assist you in your planning.