Google Cloud Cloud Migration

Migrating SQL Server On-Premise to Azure SQL

9 min read

Jul 20, 2020

Let’s revisit our options when moving your SQL Server workloads to Azure and check out ways to migrate on each of them. Let's break them down into sections in case you want to jump to specific ones.

Deployment Options

SQL Virtual Machines Azure SQL Database SQL Managed Instance

Migrating to Azure SQL

Azure Database Migration Service Azure Data Migration Assistant Backup & Restore Transactional Replication Bulk Load

Migrate your Schema Migrate your Data Data Sync

Bonus - Data Box

Deployment Options

SQL is everywhere now. Be it on premise or Azure, you have a multitude of options on how you want to set up your environment and meet your infrastructure and application needs. Even on-premise, you already have a variety of options, including hosting it in a physical server, a virtual machine, and using containers. Because the focus of this article is migrating to Azure SQL, we’ll focus on your options in Azure SQL. Azure SQL Deployment Options

SQL Virtual Machines

First we'll discuss the infrastructure-as-a-service option for deploying your SQL Server on an Azure VM. This is the most straightforward way of moving your on-premise SQL Server workload to the cloud. It's best for lift and shift migration, specially for applications that require particular access and elevated permissions at the OS level. There are also some legacy applications that require certain software to be installed together with the SQL Server instance. You might also want to just quickly migrate your application to the cloud without any changes. This might be due to cost, personnel, data center issues, or as part of digital transformation goals. Migrating your SQL Server on-premise to Azure VM gives you the flexibility and benefit of having your infrastructure in the cloud. Provisioning servers is faster, and scaling up/down hardware resources is also faster. In terms of migration, it's similar to any on-premise migration to another server. This means all your previous methods for SQL Server migration will work, including log shipping, replication, mirroring, detach/attach, backup/restore, and bulk loading. You can also utilize the Data Migration Assistant or Data Migration Service tools in Azure.

Azure SQL Database

Azure SQL Database is a platform as a service. If you are developing new applications that you want to be cloud-native, or have an on-premise application that you want to modernize, Azure SQL Database should be your database of choice. Azure SQL Database lets you focus on what you do best (your application), and leaves the database maintenance tasks to Azure. It's fully managed by Azure, which means backups, upgrading, HADR, and monitoring is handled by Azure with minimal (or no) user involvement. Azure SQL Database is highly-available, promises an SLA of 99.99% availability, and boasts of a number of tuning and security features. It's always on the latest version of SQL (without manually patching/upgrading), means you’ll always benefit performance-wise from the latest improvements, and have access to the latest SQL features. Azure SQL Database also lets you easily scale up/down your database in terms of resources, with near-zero interruption to your application. The usage and resources are also contained per database, so this is perfect for multi-tenant applications. Because you only have access to the database, the downside is that the instance-level features are not available to you, as well as any features that require OS access. So, if your existing application is using CLR, service brokers, server-level objects (Linked servers, SQL Jobs), filestream, and so on, this would not be a straightforward option. It’ll require some rearchitecting and changes on your application if you want to migrate to Azure SQL. There are a few options in terms of deployment for Azure SQL Database:

Single Database: This is a fully managed and isolated database. This is similar to a single user database or a contained database for SQL Server.
Elastic Pool: Elastic pool is a feature for Azure SQL where you can group your databases into a single pool so they can share the resources you are already paying for. Consider this scenario: You have one Azure SQL Database for your OLTP that is busy and heavily used during business hours and requires you to provision it with eight vCores. But after office hours, it has minimal to no transactions. Then, you also have a database for your ETL/Data warehouse that has numerous transactions during the night and only minimal transactions in the morning. With Elastic Pool, you can group them together and have them share resources instead of paying for each of them during off-peak hours.

SQL Managed Instance

When Azure SQL came out, the barrier for entry was the amount of refactoring, rearchitecting, and/or rebuilding required to existing applications when you want to move them to the cloud. If you are using SQL Server on-premise, especially for complex applications, you are bound to use some, if not all, of the server-level features of SQL Server. Chief among them is SQL Server Jobs, among other things. This made platform-as-a-service a poor option for many existing applications. However, Managed Instance changed all that. Managed Instance allows you to stand up a SQL Instance in Azure. Managed Instance gives you the broadest compatibility to on-premise SQL Server with all the benefits of a fully-managed platform-as-a-service. You have access to all the great features of Azure SQL Database such as automated backups, patching/updates, ease of scaling, as well as the intelligent and security features on Azure SQL. Managed Instance also allows you to put your MI on a private vNet instead of public endpoints. This addresses a common security issue. Because you have an Instance, you also have access to the server-level objects like linked servers, SQL Server Jobs, and logins, among others. This made it easier to lift and shift existing applications to Azure. For a more detailed comparison of features and capabilities for the deployment options on Azure SQL, check out this documentation.

Migrating to Azure SQL

In the previous section, we discussed the different deployment options available in Azure SQL. Now that we know our options, let's take a look at the ways you can migrate your database application to the cloud. As long as you're aware of what your applications are, and the features you are using and other applications that need to interact with your application, coming up with a clear and appropriate strategy for your cloud journey can be uncomplicated. Azure also has an Azure Database Migration Guide if you need assistance in figuring things out. You can break down your process into pre-migration, migration and post-migration. There are a number of toolzs that can help you at each stage.

Azure also recently launched Azure Migrate. It provides a centralized platform to organize your migration which makes it easier to identify, assess, and migrate your infrastructure/applications as a whole. Here, we’ll be focusing on moving your databases to Azure SQL. Here’s a quick comparison on what you can use for each of them:

	Azure SQL VM	SQL Managed Instance	Azure SQL Database
Azure Database Migration Service	Yes	Yes	Yes
Data Migration Assistant	Yes	No	Yes
Backup & Restore	Yes	Yes	No
Detach/Attach	Yes	No	No
Mirroring/Availability Group	Yes	No	No
Transactional Replication	Yes	Yes	Yes
Log Shipping	Yes	No	No
Bulk Load	Yes	Yes	Yes

Let’s discuss these options:

Azure Database Migration Service

Azure DMS lets you migrate databases from various sources to Azure Data Platforms. This is best for online migration with minimal downtime. The service allows you to create an assessment before your migration to help you identify action items that require your attention before and/or during your migration. The assessment also provides recommendations about how to address these items. After addressing the issues, you can use the same service for the actual migration of either or both your schema and data. You can perform online migration with DMS and keep your target database synching with your source database. This minimizes down time and lets you have a cutover when you're completely synched. Also, DMS allows you to migrate more than just SQL server databases (see below): Azure Data Migration Service

Azure Data Migration Assistant

DMA is sort of the offline little brother of DMS. You can download and install it to your server to help assess and migrate your SQL Server or AWS RDS SQL database to Azure SQL. It also has a tool to assess your load and make recommendations on what type of pricing tier your load is most appropriate for. It can also assess and migrate SSIS to Azure SQL Managed Instance. For migration, you can opt to migrate just the schema or both schema and data. DMA is best for assessing your databases and offline migration. For larger and multiple databases, it's better to use DMS.

Backup and Restore

You can make a backup of your database and restore to your target with SQL on Azure VM and for SQL Managed Instance. Also, you can copy your native SQL backup to a blob storage and restore it on an Azure SQL Managed Instance. We'll skip those items that you can only do with SQL on Azure VM, since these methods are similar to what you commonly do with on-premise migration of SQL Server: Detach/Attach, Mirroring/Availability Group, and Log Shipping. These traditional methods can also be utilized to migrate to Azure VM.

Transactional Replication

Replication is not a necessarily a migration tool, but you can utilize it to synchronize your data from an on-premise SQL Server to Azure SQL. You can configure both Azure SQL Database and Azure SQL Managed Instance as a subscriber to an on-premise SQL Server. You can use a snapshot of the replication to start loading the data to the subscriber. Then, the transactional replication can get it synchronized. When it's synchronized, you can schedule a cut-over to stop the replication and repoint your application to the new database.

Bulk Load

There are other various ways to load your data to Azure. There are essentially two (or three) steps to it.

1. Migrate Your Schema

After you've addressed any changes you need so your application is compatible with Azure SQL, you can migrate your schema to the new database. This can be done by using either:

DMS or DMA.
DACPAC: DACPAC is a single file containing the definitions for your database schema: Tables, view, stored procedures, functions, and other database objects. It's sort of like a schema, except only backup. You import a DACPAC to Azure SQL Database using SSDT, SSMS or SQLpackage.exe.
Generate SQL Scripts: There are various ways to do this depending on your development practices. SSMS also has the capability to generate scripts to recreate your database. I detailed the steps in this previous article.

2. Migrate Your Data

After you migrate your schema, you’ll then need to migrate your data to the new database. You can use either:

DMS or DMA.
BACPAC. Similar to DACPAC, BACPAC creates a single file but encapsulates both schema and data. This enables you to 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.
Generate SQL Scripts: If your database is small enough, you can create a script to load your data, SSMS can also do the same thing for you. However, this is not advisable for big databases.

3. Data Sync

For small databases, this might not be required. If you can migrate your database using bulk load, it's likely a small one that can be done within your migration window. That’s why I stated two or three steps. If you require to sync your data after your initial data load, you’ll need to utilize any of the options previously mentioned in this list (DMS, DMA).

Bonus: Data Box

A common question when talking about migrating to Azure SQL is: “What if we have a very large database. How can we move it to Azure?” Recently Azure Data Box went into GA. This lets you move Terabytes of data to Azure in a faster, more inexpensive and reliable way. It's a physical device that you order. When you receive it, you can copy your data on-premise. Then, send it to Azure. This makes it easier and faster to copy to other Azure resources such as Blob Storage or VM. From there, it can be consumed by your Azure SQL VM or SQL Managed Instance. For more details, click here. Azure Data Box