Drastically reduce costs with a new option for running SQL workloads in Azure

By garethmonk -


In an earlier blog, I looked at the pros and cons of the two main options for running SQL workloads in Azure – Azure SQL Database (PaaS) or SQL Server on Azure VMs (IaaS).

The news now is there’s a third option that sits somewhere in the middle, addressing the limitations of both existing choices, and opening the possibilities of significantly reduced costs.

Azure SQL Managed Instance allows customers to migrate their SQL workloads to Azure, benefiting from the cost reduction and lower management demands of PaaS, but without the need for heavy redevelopment. This new deployment choice enables frictionless migration for SQL apps and modernisation in a fully managed service.

It offers near 100% compatibility with SQL Server on-premises (Enterprise Edition), providing a native virtual network (VNet) implementation that addresses common security concerns and a business model favourable for on-premises SQL Server customers.

It also allows existing SQL Server customers to lift and shift their on-premises apps to the cloud with minimal application and database changes while retaining all PaaS capabilities, such as automatic patching and version updates, backup, high-availability.

The result should be drastically reduced management overhead and TCO.

Challenges of PaaS versus IaaS

While the existing PaaS option delivers quickly and takes away the need to hire or retrain people to manage your database, it’s unlikely what you have can jump straight into Azure SQL database with some refactoring. This means you’d need a lot of redevelopment work around schema changes and feature depreciation and the like, all of which is expensive, and you may not have the relevant expertise for.

Opting instead for SQL IaaS on Azure VMs while keeping redevelopment costs low, meant high ongoing costs and a management overhead.

So, the choice has been this: do we redevelop over time or spend effort upfront, paying to redevelop apps now and move to the cloud fully-refactored, allowing us to minimise costs over the long term?

But with SQL Managed Instances, you can get running quite quickly and without lots of redevelopment work. To be clear, it’s still not like-for-like, you can’t just pick it up and put it in the cloud and expect things to work immediately. However, it’s lots closer to what you have today and still get the benefits of some PaaS elements – it costs less money to run, you don’t have to manage the underlying infra or design and configure security features. Also, out-of-the box you get the same resiliency and high availability.

Compare this to on-prem where you probably need two sets of people involved in deploying a highly available cluster, then a database team. That’s two sets of cost you could avoid with Managed Instance because Microsoft does this for you, as well as managing disaster recovery.

Bridging the security and costs gap

One of the key attractions of the new SQL option is it offers the same underlying technology and features, which includes advance threat protection.

In this way it traverses one of the largest blockers to adoption among security conscious organisations: Managed Instance can be deployed inside a secured network with private connectivity. This allows organisations to benefit from cost savings while maintaining security.

On costs, SQL Managed Instance can work out up to 10x cheaper than running your SQL workload on Azure VM’s and 30x cheaper than AWS, which is extremely compelling.

Operational advantages

Historical challenges to the PaaS offering included that you couldn’t get all the benefits of SQL Server, such as the SQL Agent, but you can get near 100% feature parity with Managed Instance.

With this, you can run database migration at scale, with the ability to deploy many SQL workloads quickly. You can also link databases across the globe without lots of configuration work done by expensive database and infrastructure architects, or integrate into Azure Active Directory, or benefit from dynamic data masking without engaging a consultant. With Managed Instance you can effectively tick a box and it’s done.

In other words, the new deployment option has resolved lots of the pain of migrating to PaaS. Check out the summary below on the key differences between SQL Server on-premises and Managed Instance. And if you’re currently trying to manage Azure without expert support, feel free to reach out to us with any queries.

SUMMARY - key differences between SQL Server on-premises and Managed Instance

  • Managed Instance benefits from being always-up-to-date in the cloud, which means some features in on-premises SQL Server may be obsolete, retired or have alternatives. There are specific cases when tools need to recognise a particular feature works in a slightly different way or that service is not running in an environment you don’t fully control:
  • High-availability is built in and pre-configured. Always-on, high availability features aren’t exposed in the same way as SQL IaaS implementations.
  • Automated backups and point in time restore. Customer can initiate copy-only backups that don’t interfere with automatic backup chain.
  • Managed Instance does not allow specifying full physical paths, so all corresponding scenarios must be supported differently: RESTORE DB does not support WITH MOVE, CREATE DB doesn’t allow physical paths, BULK INSERT works with Azure Blobs only.
  • Managed Instance supports Azure AD authentication as a cloud alternative to Windows authentication.
  • Managed Instance supports SQL Server Integration Services (SSIS) and can host SSIS catalog (SSISDB) that stores SSIS packages, but they’re executed on a managed Azure-SSIS Integration Runtime (IR) in Azure Data Factory (ADF), see Create Azure-SSIS IR in ADF. To compare the SSIS features in SQL Database and Managed Instance, see Compare SQL Database and Managed Instance (Preview).
  • Azure SQL Database Managed Instance provides high compatibility with on-premises SQL Server Database Engine. Most of the SQL Server Database Engine features are supported in Managed Instance. Since there are still some differences, it’s strongly recommended that the T-SQL Differences be validated prior to migration.
  • Azure SQL Managed Instance bridges the gap between SQL Server on Azure VM’s (IaaS) and Azure SQL databases (PaaS), allowing customers to migrate their SQL workloads to Azure, benefiting from the cost reduction and reduced management of PaaS, without the need for heavy redevelopment.