Summary

Azure SQL Database provides a fully managed PaaS solution with limited control but high availability and automated maintenance, while SQL Server on Azure VM offers an IaaS solution with full administrative control and compatibility with on-premises SQL Server features.

Abstract

Azure SQL Database is a Database-as-a-service (DBaaS) offering that abstracts the underlying hardware and provides a highly available (99.995% uptime) and scalable service with automatic backups and patching. It supports a majority of SQL Server features and is suitable for modern cloud applications requiring quick development and deployment with minimal management overhead. In contrast, SQL Server on Azure VM is an Infrastructure-as-a-service (IaaS) offering that provides full control over the SQL Server instance, allowing for complete customization and compatibility with on-premises SQL Server environments. This solution is ideal for applications that need OS-level access, specific SQL Server features like SQL Agent, DB Mail, and Linked Servers, and for lift-and-shift migrations with minimal changes. The VM-based approach requires manual backup and patching management and offers a pay-as-you-go licensing model, with the option to use existing licenses through the Azure Hybrid Benefit.

Opinions

Azure SQL Database is recommended for new cloud-native applications due to its managed service benefits, such as reduced management overhead and lower total cost of ownership.
SQL Server on Azure VM is preferred for existing applications that require full SQL Server feature compatibility and administrative control, particularly for those that rely on features not supported by Azure SQL Database.
Azure SQL Managed Instance is mentioned as an alternative that combines the benefits of both Azure SQL Database and SQL Server on Azure VM, offering a middle ground with near-complete SQL Server compatibility and a managed service experience.
The author suggests a preference for Azure SQL Database for cloud migrations, emphasizing its suitability for modern applications and ease of use.
The choice between Azure SQL Database and SQL Server on Azure VM should be guided by the specific needs of the application, such as the requirement for unplanned and unbounded database scale (elasticity via scale-out) or planned database scale (scale-up).

Azure — Difference between Azure SQL Database and SQL Server on VM

Comparison — Azure SQL Database vs SQL Server on Virtual Machine

Awesome Azure — Azure SQL Database vs SQL Server on VM

TL;DR:

Azure SQL Database offers Database-as-a-service (DBaaS-PaaS). With SQL Database, you don’t have access to the machines that host your databases. In contrast, Azure Virtual Machine offers Infrastructure-as-a-service (IaaS). Running SQL Server on an Azure VM is similar to running SQL Server in a On-Premise datacenter.

Key differences

Features Azure SQL Database: The majority of the database-level features, SQL standards, T-SQL query processing are supported. For example, database collation, database auditing, T-SQL Expression, etc. Linked Server, SQL Agent, DB Mail, Polybase features are not supported.

SQL Server on Azure VM: It supports all the SQL Server on-premises capabilities.

Size Azure SQL Database: Database size is always depends on the underlying service tiers (e.g. Basic, Business Critical, Hyperscale). It supports databases of up to 100 TB with Hyperscale service tier model.

SQL Server on Azure VM: Max database size is constrained by the size of the VM. SQL Server instances support up to 256 TB of storage. The instance can support as many databases as needed.

Availability Azure SQL Database: It is 99.995% available and availability is guaranteed. By default, Azure infrastructure provides fault-tolerance and high Availability for the Azure SQL databases.

SQL Server on Azure VM: It is up to 99.99%. Achieving high-availability always incur the cost and additional overhead to manage the additional VM servers.

Host Accessibility Azure SQL Database: There is no direct control over underlying compute server. It is a fully managed SQL Server database engine, based on the latest stable Enterprise Edition of SQL Server. SQL Database has deployed on standardized hardware and software that is owned, hosted, and maintained by Microsoft.

SQL Server on Azure VM: You have full administrative control over the database engine. You can choose when to start maintenance/patching, change the recovery model to simple or bulk-logged, pause or start the service when needed, and you can fully customize the SQL Server database engine. With this additional control comes the added responsibility to manage the virtual machine.

Tier Flexibility Azure SQL Database: Computing resource is based on the DTU or VCore Model.

SQL Server on Azure VM: VM series are broadly classified to fulfill all the application needs. e.g. Compute-optimized, Memory-optimized, A-Series…

Backup Azure SQL Database: It is automatic. It will support short-term (7 or 35 days) and Long-term up to 10 years based on the service tiers.

SQL Server on Azure VM: It is not an automatic process. The database backups are managed using SQL native or any third-party tools.

Recovery model Azure SQL Database: Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available.

SQL Server on Azure VM: All 3-recovery models Full, Simple, and Bulk-logged recovery models are supported.

Patching Azure SQL Database: Automatic. SQL Server on Azure VM: Manual.

License Azure SQL Database: Built-in license model with Pay-as-you-go.

SQL Server on Azure VM: Pay-as-you-go included in a SQL Server image. You can also stop or resume VM as needed. Use Microsoft controlled licenses for SQL Server images versions such as SQL Server 2019 and editions such as Express Edition, Standard Edition, and Enterprise Edition. Azure Hybrid Benefit — It supports the use of the existing server license with Software Assurance BYOL (Bring-Your-OWN-License) model where you need to pay for VM (Compute) and storage only.

Use Cases

Choose Azure SQL Database if:

You are building new modern cloud applications that want to use the latest stable SQL Server features and have time constraints in development and marketing.
You want to reduce the cost of database management and focus on the application layer.
Your application is designed for “unplanned & unbounded database scale” (elasticity via scale out).

Choose SQL Server on Azure VM if:

You require full compatibility with SQL Server on-premises.
Your application rely heavily on SQL agent or DB mail or Linked server features then SQL Server on Azure VM is the best fit.
Your applications require OS-level access and full administrative control over the SQL Server instance.
You want to make minimal changes to an existing application. Best for migrations: lift-and-shift ready for existing applications that require fast migration to the cloud with minimal changes or no changes.
Your application is designed for “planned database scale” (scale up).

Alternatives

Azure SQL Managed Instance (SQL MI)

Summary

Choose Azure SQL Database when you want less management overhead and total cost of ownership. It preserves all platform-as-a-service capabilities, such as automatic patching and version updates, automated backups, and high availability.

Choose SQL Server on Azure VM when you want full control of an operating system or have other applications/tools that might need to be installed. If applications rely heavily on SQL agent or Linked server features then SQL Server on Azure VM is the best fit.

Azure SQL Database is my first choice for cloud migrations.

View more from Awesome Azure

Happy Clouding!!!