Azure — Difference between Azure SQL Database and SQL Server on VM
Comparison — Azure SQL Database vs SQL Server on Virtual Machine
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
- Difference between Traffic Manager and Front Door Service
- Difference between Azure Load Balancer and Application Gateway
- Difference between Azure Private Links and Azure Service Endpoints
- Difference between Azure Blob Storage and Azure Data Lake Storage (ADLS)
- Difference between Azure SQL Database and Azure SQL Managed Instance
- Difference between Azure Storage Queue and Service Bus Queue
Happy Clouding!!!
