SQL Server: Cloud Options?

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.

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 in Sheffield, Yorkshire 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.