Migrating SQL Server On-Premise to Azure SQL
Deployment OptionsSQL 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.
SQL Virtual MachinesFirst 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 DatabaseAzure 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 InstanceWhen 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|
Backup and RestoreYou 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 ReplicationReplication 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 LoadThere are other various ways to load your data to Azure. There are essentially two (or three) steps to it.
1. Migrate Your SchemaAfter 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 DataAfter 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.