The purpose of this document is to list the limitations of Azure Managed Instance and Database so we can compare them with the old-fashioned SQL on VM option.
- Deployment and Management
- Maintenance and Operations
- Scalability and Performance
- Features and Capabilities
- Network and Security
- High Availability and Disaster Recovery
- Pricing and Licensing
- Azure SQL Database – DTU explanation
- Some useful URLs
Deployment and Management
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| Deployment | Full control over SQL Server installed on a VM | Fully managed service, SQL Server instance-like | Fully managed, database-as-a-service (DBaaS) |
| Management | Self-managed (OS, patches, backups) | Automated management (backups, patches, upgrades) | Fully automated (backups, patches, high availability) |
| Customization | Full control over SQL Server and OS settings | Limited customization, but most instance-level configurations | Least customizable, database-level settings only |
| Use Case | Full flexibility for custom SQL environments | Ideal for migrating existing SQL workloads with minimal refactoring | Best for cloud-native applications or lightweight workloads |
| Red9 support | Proactive Red9 monitoring and tuning (including auto-tuning and auto-fix issues), full support and maintenance | Limited Red9 monitoring, more complex tuning, not supported auto-tuning or auto-issue fixing. | No Red9 monitoring, only manual tuning and maintenance. |
Maintenance and Operations
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| Maintenance Responsibility | User manages everything (patching, backups) | Managed by Azure (backups, high availability, patches) | Fully managed by Azure |
| Operational Control | Full control over the OS and SQL Server | Control over SQL instance, but limited to supported features | Limited to database-level operations |
| Backups | Red9 backup automation, point-in-time restore | Azure automated backups, point-in-time restore | Azure automated backups, point-in-time restore |
Scalability and Performance
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| Scalability | Manual scaling by changing VM size | Built-in scalability through service tiers | Easy scaling (elastic pools, serverless options available) |
| Performance | Performance tied to VM size and configuration | Performance optimized via built-in features (tiers) | Performance tied to service tier (vCore or DTU model) |
| Elasticity | Custom configurations based on VM and SQL Server | Limited to available instance sizes | Offers elasticity through elastic pools and serverless options |
Features and Capabilities
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| SQL Server Version | Any version you install (2012, 2014, 2016, etc.) | Built on Enterprise Edition SQL Server | Managed SQL platform, closest to latest SQL Server features |
| SQL Feature Support | Full support, including SSRS, SSIS, SSAS | SSIS is available, but no SSRS/SSAS | No SSRS/SSAS/SSIS, SQL Agent support directly |
| Cross-Database Queries | Full support | Supported across databases in the same instance | Limited (possible via elastic queries) |
| Custom Software | Full support (can install any third-party tools) | No ability to install custom software | No ability to install custom software |
Network and Security
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| Network Configuration | Full control over networking, security groups | Virtual Network (VNet) support with private IP | Public and private endpoints supported, VNet integration via private link |
| Security | User controls security (firewalls, encryption) | Built-in security features, managed by Azure | Built-in security features, managed by Azure |
| Authentication | Full control (Windows Authentication, certificates, etc.) | Supports Active Directory and SQL Authentication | Supports Active Directory and SQL Authentication |
High Availability and Disaster Recovery
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| High Availability | Supports all available options, like Always On Availability Groups, failover clustering | Built-in high availability with automatic failover | Built-in high availability with automatic failover |
| Disaster Recovery | Supports all available options, like Always On Availability Groups, Log Shipping, Replication | Built-in disaster recovery options | Automated DR with geo-replication options |
Pricing and Licensing
| Feature | SQL Server on Azure VM | Azure SQL Managed Instance (MI) | Azure SQL Database |
|---|---|---|---|
| Pricing | Pay for the VM size, SQL Server license, and storage separately | Pay for the instance based on the service tier (vCores) | Pay for the database (vCore or DTU-based) |
| Licensing | SQL Server license (BYOL or included in Azure cost) | SQL Server license included in the cost | SQL Server license included in the cost |
| Price comparison (8vCore + 4TB Data size + Backup), Single Instance | $1,890.00 (SQL Standard, E8bdsv_5, Premium SSD v2 – 12k IOPS + 400 MB/s, max IOPS 80k + 1200 MB/s) | $2,533.87 (Next Generation General Purpose, max IOPS 12k + 400MB/s, max TempDB size 192GB) $4,820.70 (Business Critical, database size limit 1TB) $17,492.40 (Business Critical, 32vCore, database size limit 4TB) | $2,303.72 (General purpose, Per each database, database size limit of 2TB, max IOPS 2,5k + 36 MB/s) $5,555.40 (General purpose, Per each database, 24 vCore and database size limit of 4TB, max IOPS 7,6k + 50 MB/s) $3,682.27 (Hyperscale, per each database, TempDB max size 256GB) |
| Price comparison (8vCore + 4TB Data size + Backup + DR/HA) | $2,769.01 (SQL Standard + DR, E8bdsv_5, Premium SSD v2 – 12k IOPS + 400 MB/s, max IOPS 80k + 1200 MB/s) $3,628.22 (SQL Standard + DR/HA, E8bdsv_5, Premium SSD v2 – 12k IOPS + 400 MB/s, max IOPS 80k + 1200 MB/s) | $3,479.25 (Next Generation General Purpose, max IOPS 12k + 400MB/s, max TempDB size 192GB) | $4,023.65 (General purpose, Per each database, database size limit of 2TB) $9,330.69 (General purpose, Per each database, 24 vCore and database size limit of 4TB) $4,749.00 (Hyperscale, per each database, TempDB max size 256GB) |
Azure SQL Database – DTU explanation
DTU (Database Transaction Unit) is a performance measurement used by Azure SQL Database to describe the relative capacity of a database in terms of CPU, memory, reads, and writes. DTUs provide a simplified way to select a performance tier based on the workload requirements of your database, without having to configure and balance individual resources like CPU or IOPS manually.
Components of a DTU
A DTU represents a blended measure of these three main resources:
- CPU: The processing power required for queries and other operations.
- Memory: The amount of RAM available for query execution, caching, and temporary storage.
- Disk I/O:
- Reads: The amount of data read from storage (disk).
- Writes: The amount of data written to storage (disk).
Each DTU tier is designed to offer a balanced amount of these resources. For example, as you move from a lower DTU tier (e.g., Basic) to a higher DTU tier (e.g., Standard or Premium), you get more CPU, memory, and I/O capacity.
How DTU Works
- Performance Tiers: Azure SQL Database offers multiple performance tiers based on DTUs:
- Basic: Suitable for lightweight workloads like small databases or development environments.
- Standard (S0, S1, S2, etc.): Designed for general-purpose workloads with moderate performance requirements.
- Premium (P1, P2, etc.): Meant for high-performance workloads that require high transaction rates and low latency, especially for business-critical applications.
- Resource Scaling: As you choose higher DTU tiers, you get more resources:
- Higher CPU capacity for faster query processing.
- More memory for caching data and query execution.
- Higher IOPS (Input/Output operations per second) for faster reads/writes to storage.
- Simplified Sizing: Instead of managing individual resources (like CPU, memory, or I/O) directly, you select a DTU tier that meets your performance needs. This simplifies the process of sizing and scaling the database.
When to Use DTUs vs. vCores
While DTUs are simple, Azure also offers a vCore-based pricing model, which allows more granular control over CPU and memory resources. DTU-based pricing is generally better for:
- Small to medium-sized databases with relatively simple performance needs.
- Users who want simplicity in resource management without dealing with specifics of CPU cores and memory allocation.
In contrast, the vCore model is better suited for:
- Large databases or enterprise applications with complex or predictable resource requirements.
- Organizations that prefer to manage and configure CPU and memory separately, providing more control and flexibility.
