INTRODUCTION
In this article, I compare deployment of Microsoft Azure SQL and SQL Server in a VM. I review the pros and cons of both approaches towards SQL Server deployment and take a closer look at cases when you may actually need each.
AZURE SQL IN THE NUTSHELL
Running SQL in Azure is a bit unusual because it doesn’t rely on an underlying infrastructure that you can literally see. For some, Azure SQL may be a great idea as they can forget about managing additional services and hardware. There is also no need to manage updates as well because Microsoft takes care of the Azure SQL code so that you always use the latest version of SQL Server.
Your basic toolkit remains the same: SQL Management Studio or Visual Studio. Note that you can get even more tools from Azure to help you manage your query performance. Looks good as you still can use tools that you are so used to!
Another nice thing about Azure SQL is simplicity. You no longer need to deploy a virtual machine to start working with SQL Server, but, unfortunately, there is a catch. Azure SQL is not a full-blown SQL Server. If an application requires the SQL Server, Azure SQL might not be compatible. But, if your application requires only a connection string, Azure SQL is great for you.
Within the subscription, 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. Also, you can deploy an Azure SQL database and replicate it to another Azure region for disaster recovery purposes in just a few minutes. It is faster than if you have SQL Server running in a virtual machine!
Now, I want to say a couple of words about pricing. For the Azure SQL database, you can just deploy it, get the connection string, and start using it from as little as almost $5 per month ($4.906, to be more precise). SQL Server Standard, in its turn, costs at least $350 per month (East US, RRP).
All this being said, Azure SQL operational benefits are:
Deployment Speed: If you need an SQL database, deploy it in Azure Portal, and it’s ready. You do not need to wait for Azure infrastructure to be deployed.
Focus: You are no longer distracted by non-database activities.
Infrastructure: You do not need to install and manage complicated infrastructure for SQL anymore.
GOOD OLD SQL SERVER IN A VM
When users want to use SQL Server, they typically think of deploying SQL Server in a Windows VM. There are several pros of this simple choice:
Familiarity: You know how to work with SQL Server. You know the backup tools, how to manage it, and which features or applications work and which don’t. And, what is also important, you know some of the problems that you may encounter.
Compatibility: When some application requires the SQL Server, you know that SQL Server on Windows Server will always work.
Flexibility: You can easily manage your system resources running SQL Server in a VM. Just add CPU, RAM, or storage resources to the VM according to your needs.
But, cons may override strong sides of such deployment. You make the resulting infrastructure very complicated, and it can turn out to be too expensive to run SQL Server in a VM.
If you are familiar with SQL Server, you also know the work that is ahead for you. Making SQL Server highly-available and fault-tolerant requires additional knowledge and skills in Failover Clustering. Generally speaking, you’re going to have more machines to patch, secure, and manage; so you will end up with a complicated infrastructure. And, on top of that, you have another SQL installation that adds more pains, like maintenance, backups, log file management, performance/capacity planning of the infrastructure, and SQL Server upgrades.
But most of all, you’re familiar with SQL Server deployment 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 grow. Sure, you can run that VM in Azure. But, comparing costs of Azure resources consumption and ownership costs, deploying SQL Server in a VM is not the cheapest way to run SQL Server in the cloud.
There is a Linux option for SQL Server 2017 – Azure Marketplace image for SQL Server 2017 Enterprise on an Ubuntu VM. Find more about it here:
It is a way cheaper, but it is difficult to manage.
IS THERE A 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. SQL Managed Instances is expected to be 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. Eventually, this service brings them to Azure SQL. But, if you need full operational SQL Server and you don’t want to be dependent on Azure services, SQL Server in a VM will be the best choice for you.
CONCLUSION
What are the use cases for Azure SQL and SQL Server in a VM? The former is the perfect fit for applications without any special compatibility requirements. If all you need is providing them just a connection string, Azure SQL is a great choice. Furthermore, it saves you some money! The latter, in turn, is quite expensive, but it works with any application for sure. Deploying SQL Server in a VM is also a great idea when you run many services in your cloud infrastructure that need to be replicated to on-premises infrastructure.
On the whole, I expect more applications to work with Azure SQL soon since Microsoft has a serious intention to move their users to Azure.
