Moving SQL Server Relational Workloads To Azure

This article will cover the options to move your SQL Server relational database workloads to the cloud on Azure and what solutions are a good fit based on your level of development and appetite for migration effort to the cloud. Each of the options has its own pricing method, benefits, and drawbacks. At the very least there will be some integration and testing involved when you move from on-prem to the cloud that will be taken into consideration. In other cases, these migration costs can be a lot more depending on the solution that fits your needs.

The flexibility that comes with the cloud to scale resources up or down can be an attractive feature to consider a move to the cloud.

This article will cover the options to move your SQL Server relational database workloads to the cloud on Azure and what solutions are a good fit based on your level of development and appetite for migration effort to the cloud.

Each of the options has its own pricing method, benefits, and drawbacks. At the very least there will be some integration and testing involved when you move from on-prem to the cloud that will be taken into consideration. In other cases, these migration costs can be a lot more depending on the solution that fits your needs.

When to move?

First, let’s discuss when it is a good time to consider the cloud for your existing workloads. A move to the cloud almost always comes with migration costs even for the simplest lift and shift migrations using SQL Server on Azure VM.

To offset these costs, it is good to ask the following questions when considering a move to pick a good time to move.

1. Have we done the most we can to maximize our current on-prem investments? Optimization techniques might include:
   • Adding Parallelization of jobs
   • Query tuning techniques (Indexes, Partitions)
   • Scale-up existing servers with more resources
2. Are we at the end of the life cycle for our existing solution?
   • If the solution is at the end of its life cycle the opportunity can be used to migrate a workload to the Azure platform since migration costs will be incurred either way.
3. Are we evaluating a new analytics service that can’t be accommodated on our existing on-prem solution?
   • If your organization is adding new services such as Big Data or Artificial Intelligence/Machine Learning, creating POCs will be easier in the cloud as to avoid putting strain on existing servers.

Cost Considerations

Some costs will stay the same depending on the option for cloud adoption. For example, SQL Server on Azure VM is the closest option to running a server on-prem in the cloud.

If you have multiple servers running various applications then unless you move the entire set of servers to the cloud you will still be liable for costs of the data center until you migrate the entirely on-prem servers to the cloud.

Also on the cloud, you pay for what you use. However, if you have a rogue process, then you pay for the additional computation and storage used by that process. You are not fixed to the bandwidth of a specific server. There are ways to monitor and control rogue processes on the cloud, but you will need to make sure they are set up.

The benefit is that a business’ efforts towards optimizing tech resources can lead to cost savings that would not happen with on-premises.

Azure Relational Database Management Options

There are 4 options to move to the cloud for clients currently on SQL Server on Prem.

1. SQL Server on Azure VM
2. SQL Server Managed Instance
3. Azure SQL DB
4. Azure Synapse Dedicated SQL Pools

We will evaluate each of the above 4 options on the criteria below.

• Migration Costs - costs to migrate to the cloud
• Service Offering Specialization - The degree to which you can control usage and costs for services.
• Cost Savings compared to on-prem solution - This can be nuanced, however, we will explain scenarios where an option might cost you more or less depending on your use case.

SQL Server on Azure VM

SQL Server on Azure Virtual Machines enables you to use full versions of SQL Server in the cloud without having to manage any on-premises hardware. SQL Server virtual machines (VMs) also simplify licensing costs when you pay as you go.

Azure virtual machines run in many different geographic regions around the world. They also offer a variety of machine sizes. The virtual machine image gallery allows you to create a SQL Server VM with the right version, edition, and operating system. This makes virtual machines a good option for many different SQL Server workloads.

Figure 1: Example of a sample workload on-prem migrated to a SQL Server on Azure VM.

Migration Costs

Of the 4 options, SQL Server on Azure VM has the lowest cost hence we mark it as 4 star

Service Offering Specialization

Of the 4 options, SQL Server on Azure VM gives the least flexibility in choosing services and splitting out services based on what is needed. If you pick an Enterprise license version of SQL Server you get the offerings that come with it whether you will utilize all the features or not.

Cost Savings compared to on-prem solution

In cost savings compared to the on-prem solution category SQL Server on Azure VM is kind of in the middle since you don’t save much compared to on-prem and usually, costs are comparable to on-prem. You do get some benefit of offerings that Microsoft has to incentivize move to the cloud but generally, costs are very similar to on-prem.

Azure SQL Server Managed Instance

Azure SQL Managed Instance is the intelligent, scalable cloud database service that combines the broadest SQL Server database engine compatibility with all the benefits of a fully managed and evergreen platform as a service. SQL Managed Instance has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, providing a native virtual network (VNet) implementation that addresses common security concerns, and a business model favorable for existing SQL Server customers. SQL Managed Instance allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes. At the same time, SQL Managed Instance preserves all PaaS capabilities (automatic patching and version updates, automated backups, high availability) that drastically reduce management overhead and TCO.

Figure 2: Example of an on-prem workload migrated to Azure SQL Managed Instance.

Migration Costs

Migration costs are higher than from SQL Server on Azure VM since if you are running ETL workloads on your SQL Server on Azure VM you will need to split out those services and pay for the cost of development and migration separately. We mark it with 3 stars to show it is less than SQL Server on Azure VM.

Service Offering Specialization

In managed instance, you are allowed a more granular pick of options when choosing services. You can choose to just migrate database instances and if you aren’t using services such as SSRS, SSAS, SSIS you don’t need to utilize them.

Cost Savings compared to on-prem solution

If you are running an entire workload on-prem with SSRS, SSAS and SSIS this option will be a higher cost to migrate and maintain since you have to separate services. 

Azure SQL Database

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. PaaS capabilities that are built into Azure SQL Database enable you to focus on the domain-specific database administration and optimization activities that are critical for your business.

With Azure SQL Database, you can create a highly available and high-performance data storage layer for the applications and solutions in Azure. SQL Database can be the right choice for a variety of modern cloud applications because it enables you to process both relational data and non-relational structures, such as graphs, JSON, spatial, and XML.

Figure 3: Example of moving an on-prem workload to Azure SQL Database.

Migration Costs

Migration costs are higher than from SQL Server on Azure VM and SQL Server Managed Instance since some features in Managed instances aren’t available in Azure SQL Server Database. For example, if you are using SQL Agent that isn’t available in Azure SQL DB. Also if you need to migrate multiple databases and be able to perform cross-database queries you need to create elastic pools and the code to do that is an additional migration effort than using Managed instances.

Service Offering Specialization

In Azure SQL DB you are allowed a more granular pick of options when choosing services. You can choose to just migrate database instances and if you aren’t using services such as SSRS, SSAS, SSIS you don’t need to utilize them. You can also just choose to migrate a database rather than a whole instance. The drawback is that you have more migration costs since if you have multiple databases you want to migrate using this option you will have to set up something called Elastic pools that add more overhead for migration.

Cost Savings compared to on-prem solution

If you are running an entire workload on-prem with SSRS, SSAS and SSIS this option will be a higher cost to migrate and maintain since you have to separate services.

Azure Synapse Dedicated SQL Pools

Azure Synapse Analytics is an analytics service that brings together enterprise data warehousing and Big Data analytics. Dedicated SQL pool (formerly SQL DW) refers to the enterprise data warehousing features that are available in Azure Synapse Analytics.

Dedicated SQL pool (formerly SQL DW) represents a collection of analytic resources that are provisioned when using Synapse SQL. The size of a dedicated SQL pool (formerly SQL DW) is determined by Data Warehousing Units (DWU).

Once your dedicated SQL pool is created, you can import big data with simple PolyBase T-SQL queries, and then use the power of the distributed query engine to run high-performance analytics. As you integrate and analyze the data, a dedicated SQL pool (formerly SQL DW) will become the single version of truth your business can count on for faster and more robust insights.

Figure 4: Example of moving an on-prem relational workload to Azure Synapse SQL Dedicated Pools.

Migration Costs 

This is probably the highest migration cost of the options and overall highest cost since it is required to have partitioned data to move into this offering to leverage the options to give query performance in Azure dedicated pools. Each database is its dedicated SQL pool and since you get one database per pool if you are migrating multiple databases there is additional work per database.

Service Offering Specialization

Azure synapse dedicated pools allow moving a single database per dedicated pool. If you want to pay for just a database you can do so in this option.

Cost Savings compared to on-prem solution

The data scale is at the Petabyte level and you are given a tremendous amount of power to process data in the MPP engine. However, if you run the pools 24x7 then you are paying substantially more for dedicated pools hence it gets the highest possible stars for cost savings. You will pay for the power!