Azure SQL Database vs Managed Instance
Azure offers multiple database services based on the SQL Server engine. This document explains the differences between the major PaaS (Platform as a Service) offerings: Azure SQL Database and Azure SQL Managed Instance (SQL MI).
Overview
Azure SQL Database
A fully managed database service running on the latest stable SQL Server version. You are freed from OS and instance management, managing only the database itself (or elastic pool). It is ideal for cloud-native application development.
Azure SQL Managed Instance
A fully managed instance with nearly 100% compatibility with on-premises SQL Server. It is deployed within a VNet (Virtual Network) and has a private IP. It is ideal for "lift and shift" migration from existing SQL Servers.
Functional Comparison Table
| Feature | Azure SQL Database | Azure SQL Managed Instance |
|---|---|---|
| Deployment Model | Per Database (Single Database / Elastic Pool) | Per Instance (Instance) |
| SQL Server Compatibility | High (Some feature limitations) | Nearly 100% (Equivalent to on-premises) |
| Network | Public Endpoint (Firewall control), Private Link | VNet Integration (VNet Injection) is mandatory |
| SQL Server Agent | Not supported (Alternative: Elastic Jobs) | Supported |
| Cross-Database Query | Not supported (Alternative: Elastic Query) | Supported |
| Linked Server | Limited | Supported (SQL Server, Azure SQL DB, MI, etc.) |
| CLR (Common Language Runtime) | Not supported | Supported |
| Backup/Restore | Automated backup, Point-in-time restore (.bacpac) | Native backup/restore (.bak) supported |
| Time Zone Setting | Fixed to UTC | Changeable |
| Machine Learning | Not supported | Machine Learning Services supported |
Details of Key Differences
1. Architecture and Management Scope
- SQL Database: The "Database" is the unit of management. Concepts of physical servers or instances are abstracted.
- Managed Instance: The "Instance" is the unit of management. Instance-level features such as SQL Server Agent jobs, logins, and server-level configurations are available.
2. Network Configuration
- SQL Database: Has a public endpoint by default, but access sources are restricted by firewall rules. It is also possible to connect with a private IP from within a VNet using Private Link.
- Managed Instance: Deployed to a dedicated subnet within a Virtual Network (VNet) (VNet Injection). It has a private IP by default, assuming a secure closed network connection.
3. Ease of Migration (Lift & Shift)
- SQL Database: Features used on-premises (Cross-database queries, SQL Agent, CLR, etc.) may not be available, potentially requiring application modification.
- Managed Instance: High compatibility with on-premises SQL Server allows migration (lift and shift) with minimal changes to application code. Restore from native backup files (.bak) is also supported.
Selection Guidelines
When to Choose Azure SQL Database
- Developing new cloud-native applications.
- Wanting to minimize management costs.
- Scaling and billing per database fits the requirements.
- Wanting to optimize costs for infrequently used DBs using the Serverless compute tier.
When to Choose Azure SQL Managed Instance
- Wanting to migrate existing applications from on-premises SQL Server as-is.
- Instance-level features like SQL Server Agent, Cross-database queries, CLR, Service Broker are required.
- Complete network isolation within a VNet is a requirement.
- Need to integrate with other data sources using Linked Servers.