Azure SQL Versus SQL Server In An Azure VM

This article will compare and contrast the two options for deploying SQL Server in Microsoft Azure, Azure SQL (SQL-as-a-service) and SQL Server running in a virtual machine.
 

 

SQL Server in a Virtual Machine

In my experience, when customers (developers and IT pros, alike) want to use SQL Server they typically start thinking about deploying SQL Server in a Windows Virtual Machine.
Note that there is a Linux option for SQL Server 2017 too, such as this Azure Marketplace image for SQL Server 2017 Enterprise on an Ubuntu virtual machine.
The good things about this default choice are:

• Familiarity: You know how to work with SQL Server. You know the backup tools, how to explain it, what works and what doesn’t work, and some of the problems that you will encounter.
• Compatibility: When some application requires SQL Server you know that SQL Server on Windows Server will work.

However, some of that familiarity includes the knowledge of the work that is ahead for you. You know that you’re going to have “fun” making SQL Server highly-available and tolerant of disasters. You know that you’re going to have more machines to patch, secure, and manage. You also know that you have another SQL installation, and that comes with more maintenance, backups, log file management, performance/capacity planning of the infrastructure, and the eventual SQL Server upgrades.
But most of all, you’re familiar with the cost. Whether you’ve got a small business with a single SQL Server Standard installation, or a large enterprise requiring lots of SQL Server machines, those costs are going to escalate. There are ways to be more clever about those costs, but to be honest, between Azure consumption and cost-of-ownership costs, SQL Server in a virtual machine is not the cheapest way to run SQL Server in the cloud.
 

 

Azure SQL

It is possible to consume SQL Server as an Azure service, without any virtual machines. You simply deploy a SQL database, get the connection string and start using it from as little as $4.9105 per month. SQL Server Standard costs at least $350 per month (East US, RRP).
The operational benefits of Azure SQL are:

• Speed: If you need a SQL database, you deploy it in the Azure Portal and it’s ready. You do not need to wait for Azure infrastructure to be deployed.
• Focus: You no longer are distracted by non-database activities.

For example, one can deploy an Azure SQL database and replicate it to another Azure region for disaster recovery in just a few minutes. It is faster than you will get SQL Server running in a virtual machine!
Microsoft manages the code of Azure SQL. It is always the latest version of SQL Server and you’ll never have to do another upgrade. As a part of your cost, Azure automatically backs up (full, differential and transaction log) your database to geo-redundant storage and these backups can optionally be retained up to 10 years.
In terms of familiarity, yes, Azure SQL is different because it doesn’t have an underlying infrastructure that you can see. This is pretty neat because you don’t manage this anymore! Your basic tooling doesn’t change because you will use SQL Management Studio and/or Visual Studio. Note that you’ll get more tooling from Azure to help you manage your query performance.
Unfortunately, there is a catch. Azure SQL is not a full-blown SQL Server. If your application requires SQL Server then Azure SQL might not be compatible. Check with your application vendor. If your application only requires a connection string, then Azure SQL might just be for you.

Middle Ground

Microsoft is currently running a limited preview for a new SQL Server option called SQL Managed Instances. The idea is that a SQL Instance is deployed as a service for you, offering over 99 percent compatibility with the full SQL Server product. The hope is that SQL Managed Instances will provide a stepping stone for customers as they go through the cloud- or digital transformation process, switching from on-premises SQL Server to Azure’s SQL Managed Instances. The plan is to then eventually redevelop applications, so they can switch to Azure SQL.

Advice

I have no idea when SQL managed Instances will appear. My gut feeling is that they will be expensive, just as the web app alternative of App Service Environment (ASE) is. Today, the choice is simple:
 

 

• Azure SQL: If your Azure application has no compatibility requirements and only requires a connection string, then Azure SQL is the best choice. New applications should be developed for Azure SQL.
• SQL Server in a virtual machine: When your Azure application doesn’t allow you to choose Azure SQL, then you have no choice but to use SQL Server in a virtual machine.