Lift-and-shift on-premises SQL Server database to Azure Cloud

//Lift-and-shift on-premises SQL Server database to Azure Cloud

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.

By |2018-11-07T11:20:10+00:00November 7th, 2018|

About the Author:

Hi! My name is Santosh and I'm an Azure Consultant at Nebbia Tech. I enjoy working on all things Azure and DevOps and helping businesses and software teams realize their full potential by leveraging these practices, tools and technologies. I'm also very involved with the local tech community in my capacity as the President of the Orlando .NET User Group and Orlando Codecamp.