Azure — Difference between Azure SQL Database and Azure SQL Managed Instance (SQL MI)
Comparison — Azure SQL Database vs SQL Managed Instance (MI).
TL;DR:
SQL Managed Instance (SQL MI) provides native Virtual Network (VNet) integration while Azure SQL Database enables restricted Virtual Network (VNet) access using VNet Endpoints.
SQL MI helps bridge the gap between Azure SQL Database and On-premises SQL Server due to being built on an instance scoped configuration model.
Both offers Database-as-a-service (DBaaS-PaaS). Azure SQL Database and SQL Managed Instance share a common code base with the latest stable version of SQL Server. Most of the standard SQL language, query processing, and database management features are identical.
Azure SQL Database
The majority of the database-level features, SQL standards, Full-text search (FTS), T-SQL query processing are supported like database collation, database auditing, T-SQL Expression, etc.
Azure SQL Managed Instance (SQL MI)
It supports almost all the SQL Server on-premises capabilities. It has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, providing a native VNet implementation that addresses common security concerns, and grants network administrators full control over access and configuration using firewalls and security groups. It placed in dedicated subset, and only apps in your private network can access your Managed Instances.
Features like Linked Server, SQL Agent, SQL Server Auditing, SQL Data Sync, DB Mail are supported by SQL MI, but not by Azure SQL Database.
SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), Polybase are not supported by both Azure SQL Database and SQL MI.
Similarities
Management
Both preserves all PaaS capabilities (automatic patching and version updates, automated backups, high availability) that drastically reduce management overhead and TCO (total cost of ownership).
Backup
Both supports automatic backup. Full backups are taken every 7 days, differential 12 hours, and log backups every 5–10 min. Backup retention is 7 days default and max 35 days.
Availability
Azure SQL Database: 99.99–99.995% availability is guaranteed for every database.
SQL MI: 99.99.% availability is guaranteed for every database and can’t be managed by user.
Host Accessibility
There is no direct control over underlying compute server. Both are fully managed SQL Server database engine, based on the latest stable Enterprise Edition of SQL Server. Both are deployed on standardized hardware and software that is owned, hosted, and maintained by Microsoft.
License
Both have built-in license model with Pay-as-you-go.
Key differences
Recovery model
Azure SQL Database: From automated backups only.
SQL MI: From automated backups and from full backups placed on Azure Blob Storage.
Active Geo-replication
SQL Database: Supported. In all service tiers other than Hyperscale.
SQL MI: Not supported. alternative solution is Auto-failover groups.
Auto-failover groups
SQL Database: Supported. In all service tiers other than Hyperscale.
SQL MI: Supported.
Auto-scale
SQL Database: Only supported in Serverless model.
SQL MI: Not supported. You need to choose reserved compute and storage (vCore or max storage).
Automatic tuning (indexes)
SQL Database: Supported.
SQL MI: Not supported.
Elastic jobs
SQL Database: Supported.
SQL MI: Not supported. SQL Agent can be used instead.
Long-term backup retention (LTR)
SQL Database: Supported. keep automatically taken backups up to 10 years.
SQL MI: Not supported yet. Manual backups as a temporary workaround.
Hyperscale architecture
SQL Database: Supported.
SQL MI: Not supported.
SQL Server Profiler
SQL Database: Not supported.
SQL MI: Supported.
Cross-database transactions
SQL Database: Not supported.
SQL MI: Supported.
Database mail (DbMail)
SQL Database: Not supported.
SQL MI: Supported.
Linked servers
SQL Database: Not supported.
SQL MI: Supported.
Service Broker
SQL Database: Not supported.
SQL MI: Supported.
SQL Server Agent
SQL Database: Not supported.
SQL MI: Supported.
SQL Server Auditing
SQL Database: Not supported.
SQL MI: Supported.
Use Cases
Choose Azure SQL Database if:
- You don’t need native Virtual Network (VNet) integration but need all the benefits of a fully managed and platform as a service (PaaS).
- For example, use SQL Database when application services components (e.g. App tier) running in Azure App Service.
Choose SQL Managed Instance if:
- You want native Virtual Network (VNet) integration with all the benefits of a fully managed and platform as a service (PaaS).
- For example, use SQL MI when application services components (e.g. App tier) running in Azure Virtual Machine (VM) or Virtual Machine Scale Sets (VMSS) in VNet.
- You want nearly full compatibility with SQL Server on-premises.
- Your application rely heavily on SQL agent or DB mail or Linked server.
- Best for migrations: allows existing SQL Server customers to lift and shift their on-premises applications to the cloud with minimal application and database changes, and with as low a migration effort as possible.
Alternatives
- SQL Server on Azure VM.
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 SQL Database and SQL Server on VM
- Difference between Azure Storage Queue and Service Bus Queue
Happy Clouding!!!
