With the introduction of cloud-based, managed SQL server solutions, there are now more options than ever for running SQL Server. In this insight, we will dig into those options and see how their different features compare. As SQL Server is a Microsoft product, we will focus primarily on the offerings available in Azure. Azure offers the following options:
SQL Server Cloud Options
Option 1: Azure SQL Database
Azure SQL DB (Database) is a PaaS (Platform as a Service) solution that provides a containerised logical SQL Server that runs databases individually or as part of an elastic pool that shares resources between a set of databases. Azure SQL DBs are very flexible and scalable, offering automatic scaling functionality out of the box. SQL databases stored within Azure SQL DB can also be easily copied between pools and instances, which can be helpful for development and, in some cases, reporting.
Option 2: Azure SQL Manage Instance
SQL MI (Managed Instance) is a PaaS solution that provides a provisioned resource set that can host up to 100 databases each. The MI sits within an Azure VNET, making it easily targetable via IP or FQDN. This, amongst other things, makes SQL MI slightly more familiar for those used to a standard SQL Server.
As with Azure SQL DB, resources can be scaled as needed. Scaling can take a little longer with SQL MI as it is a provisioned resource set that will need scaling rather than a logical, containered one.
Keep updated with the latest from Pipe Ten by subscribing below.
Option 3: Azure SQL VM
The final option is the simplest, a Virtual Machine with SQL Installed, available with licensing out of the box or with BYOL (Bring your own licence). Features such as automated updates, backup and SQL best practices analysis are available thanks to the SQL IaaS Agent Extension, making managing SQL Server easier than ever.
With Azure (and most other Public Cloud platforms), you can deploy to multiple regions at the click of a button, making multi-region redundancy in SQL Server simpler than ever.
SQL VM AoAG is compatible with Hybrid Cloud
SQL Server Cloud Feature Comparison
| Azure SQL Features | Option 1: SQL Database |
Option 2: SQL Managed Instance |
Option 3: SQL VM |
| SQL Server version/build: | Latest | Latest | Latest |
| Latest DB Engine Features: | Yes | Yes | Yes |
| Backup: | Snapshot Bacpac export |
Automated backup Backup to Azure Storage Bacpac export |
Automated backup Backup to Azure Storage Virtual Disk Snapshots VM Backups |
| Instance and DB monitoring/metrics: | Built-in | Built-in | Some, via IaaS Agent Extension. |
| SQL Agent Service: | No | Yes (SQL Agent Jobs) | Yes |
| Automated Patching: | Yes | Yes | Yes – via SQL IaaS Agent Extension. |
| Network Access: | Azure FQDN (else Private Endpoint). | Virtual Network. | Virtual Network. |
| Portal Support: | Yes | Yes | Yes |
| Reporting Service (SSRS): | Requires Power BI (or similar). | Can be used as a data source. | Built-in, full SRRS support. |
| Cross-database Transactions: | No | Yes, within instance. | Yes |
| Licensing: | Azure Hybrid benefit compatible. | Azure Hybrid benefit compatible. | Azure Licence. Bring Your Own License (BYOL). Azure Hybrid benefit compatible. Azure HA DR Licence compatible. |
| High Availability: | Built-in (99.995% uptime). | Built-in (99.99% uptime). | Configurable with AoAG. |
| Cross Region Replication (using Auto-failover groups): |
Yes | Yes | No |
| Availability Zone Compatible (in case of multi-zone region): |
Yes | Yes | Yes |
| Active Replication (with readable secondaries): |
Yes – intra and cross region | Yes – intra and cross region | Yes – intra and cross region with AoAG. |
| AoAG Configuration: | Automated | Automated | Manual |
| AWS Alternative: | None | Amazon RDS for SQL Server. | Amazon EC2 SQL Server Instance |
| Cost: | Low, Medium, or High (1) | High (2) | Medium, High (3) |
(1) Azure SQL DB can be deployed as low-tier serverless options that can be very cost-effective, medium-priced provisioned solutions through to high-priced business critical multi-region solutions.
(2) With provisioned resources that run an orchestrated virtual machine rather than a containerised solution, SQL Managed Instances are pricey.
(3) Microsoft’s core licencing model for SQL server means that low resource SQL Server VMs do not really benefit from low costs.
Conclusion
So what should you choose? As always, it depends on various factors, including feature requirements, budget, scalability or other considerations such as vendor lock-in. The best place to start is to simply list the conditions your SQL service must meet and go from there.
Do you need a scalable solution that can match your company’s growth without needing too much maintenance? If So, Azure SQL DB should be your choice.
Do you need a stable, low-maintenance solution that still lets you connect up SSRS to run your reports and run cross-database transactions? Azure SQL MI is for you.
Are you concerned about Vendor lock-in, restricted backup options, and currently using the SQL Agent Service? Azure SQL VM is a sound choice.
Author: Jack Jones
Jack has been an integral part of Pipe Ten’s engineering team for over 5 years. With a long history of being immersed in Microsoft’s ecosystem, Jack embodies Pipe Ten’s provider agnostic approach and has lead the evolution of many customer solutions to integrate the benefits of public cloud, specialising in Azure and AWS. The wealth and sheer depth of Jack’s cutting edge technical knowledge and skillset has been crucial to the success and growth of many customers’ businesses.