As a consultant who primarily deals with the Azure cloud and DevOps, I often get to meet software team stakeholders who, for a myriad variety of reasons, still haven’t migrated to the cloud. The fastest route to get an on-premises system to the cloud, is popularly known as lift-and-shift.

“Lift and shift is an approach, one among many, for migrating your apps to the cloud. It means moving an application and its associated data to a cloud platform—without redesigning the app.”

[netapp.com]

In most cases, lift-and-shift of a system involves a two-phase approach – move the application and move the database (in no particular order). For the sake of brevity, we will specifically focus on the databases, i.e. moving an on-premises SQL Server database to the Azure cloud.

Presented below are the most commonly recommended options for hosting SQL Server databases on Azure and a beginner’s decision matrix for what options to consider and when. Please note that I’m not only focusing on migrating these databases to the cloud but also making minimal code/database/architectural changes while doing so.

Tip: when moving to the cloud, start with PaaS and/or serverless offerings and use a process of elimination to figure out if you require managed instances or IaaS.

Azure has the following PaaS offerings for hosting SQL Server databases – Azure SQL Database – Single Database, Azure SQL Elastic Pool and Azure SQL Managed Instance.

 

Azure SQL Database – Single Database:

Azure SQL Database – Single Databases are part of Azure’s PaaS offering for hosting SQL Server databases. This is perhaps the best end result in a lift-and-shift scenario.

Salient features:

  • This option is best when your application has only one SQL Server database, or, if your application uses multiple database, the code and architecture are well silo-ed
  • Newly created databases run on the latest stable version of SQL Server and Microsoft completely handles the updates and patching of the platforms underneath.
  • Cross database queries and linked servers are not available in this option
  • You have no access to the SQL Agent, CLR or Service Broker
  • DTU or vCore resource provisioning options available
  • If your on-premises SQL Server hosted SSIS jobs, you have to lift-and-shift these jobs to the cloud by using Azure Data Factory

Recommended lift-and-shift migration options:

  1. Assess your on-premises database with the Data Migration Assistant and migrate using Database Migration Services
  2. Use Redgate’s Schema and Data Compare tools
  3. Import a single database with bacpac

Elastic Pools:

Elastic Pools are part of Azure’s PaaS offering for hosting SQL Server databases. Multiple databases are hosted on a single logical Azure SQL Server and share resources from a common pool.

Salient features:

  • This option is best when your application has multiple databases with either the same schema (often multi-tenant SaaS applications) or different schemas and you would like to pool resources between them because of their resource usage pattern
  • Newly created databases run on the latest stable version of SQL Server and Microsoft completely handles the updates and patching of the platforms underneath.
  • Allow cross database queries across databases with the same schema (horizontal) or different schemas (vertical)
  • Linked servers are not available in this option
  • You have no access to the SQL Agent, CLR or Service Broker
  • DTU or vCore resource purchasing models available
  • If your on-premises SQL Server hosted SSIS jobs, you have to lift-and-shift these jobs to the cloud by using Azure Data Factory

Recommended lift-and-shift migration options:

  1. Assess your on-premises database with the Data Migration Assistant and migrate using Database Migration Services
  2. Use Redgate’s Schema and Data Compare tools
  3. Import individual databases with bacpac

Managed Instance:

Managed Instance is a new (as of blog publish date) deployment model that provides most of the features of an on-premises SQL Server Enterprise Edition server without the hassles of maintenance. From a lift and shift perspective, I’ve found this to be the most attractive option (minus cost) for complex on-premises database solutions.

Salient features:

  • This option is often the fastest option for list-and-shift of a legacy on-premises SQL Server server with one or more databases
  • Higher entry price points (compared to other PaaS options)
  • Out-of-box High Availability guarantees and 99.99% SLA
  • Newly created databases run on the latest stable version of SQL Server and Microsoft completely handles the updates and patching of the platforms underneath
  • The solution comes with a VNet for addressing common security concerns including hybrid networks
  • Allows cross database queries and linked servers
  • You have access to the SQL Agent, CLR or Service Broker
  • vCore based resource purchasing model only with general and business service tiers
  • If your on-premises SQL Server hosted SSIS jobs, you have to lift-and-shift these jobs to the cloud by using Azure Data Factory

Recommended lift-and-shift migration options:

  1. Assess your on-premises database with the Data Migration Assistant and migrate using Database Migration Services
  2. Use Redgate’s Schema and Data Compare tools
  3. Restore backups

SQL Server on Azure Virtual Machines:

Azure also offers SQL Servers on Azure Virtual Machines as an IaaS offering. I hesitate recommending virtual machines for a variety of reasons but I’ve come across a couple of useful scenarios.

Salient features:

  • Most on-premises SQL Servers run in VMs. So this option is often the one with least resistance (from internal stakeholders) for list-and-shift of a legacy on-premises SQL Server server with one or more databases.
  • If you’re using SQL Server version specific features, this allows you to select exactly which version you can install on your VM.
  • Learning curve for the staff supporting on-premises deployments to start supporting this option is minimal compared to others
  • Allows cross database queries and linked servers
  • You can carry over existing licenses from your on-premises server to the VM in Azure.
  • You have access to the SQL Agent, CLR or Service Broker
  • Your on-premises SQL Server hosted SSIS jobs will work on the Azure VMs in most cases

Recommended lift-and-shift migration options:

  1. Use Redgate’s Schema and Data Compare tools
  2. Restore backups

My recommendations (most preferable to least):

1a) Azure Single Database

1b) Azure Elastic Pools

two) Azure SQL Managed Instance

…..

n>=9) SQL Server on Azure Virtual Machine

Footnote:

In some instance, clients will have compelling business-centric (albeit technically weak) reasons for keeping their data on-premises and we can accommodate for this by lifting and shifting their applications to the cloud and connecting to the on-premises SQL Server using hybrid networks by extending their on-premises network and Active Directory Domain to the cloud via Azure Virtual Network.

Author’s Note:

This blogpost was intended to provide a 10000ft view of the lift-and-shift process for on-premises SQL Server to Azure to those who might be interested in doing so in the future. I understand that most on-premises SQL Server database over time develop some hard baked dependencies which may or may not be addressed in this blogpost. I would love to hear feedback or suggestions of these and if I get enough, I may do a part 2 of this blogpost in a few weeks/months.

References:

Put your databases on autopilot with a lift and shift to Azure SQL Database (Azure team blog on Microsoft.com)

Cloud-native or lift-or-shift (AWS on Medium)

Choose a cloud SQL Server option: IaaS or PaaS (Microsoft Docs)

Full disclaimer:

Nebbia Technology is a Microsoft Gold Partner and a Redgate partner. But I’ve been using the tools and technologies that I recommended in this blogpost for close to a decade independent of Nebbia (I joined in August 2018) and would feel comfortable recommending them regardless of relationships with these providers.

Looking to lift-and-shift to the cloud?

Nebbia Technology can assess whether your on-premises and/or legacy applications are good candidates to lift-and-shift to the cloud so you can leverage the power of the cloud without having to go through expensive rewrite efforts while ensuring business continuity. We also have in-house experts in Azure, DevOps, databases and .NET and web development to assist with the effort.