Azure SQL Managed Instance for a DBA: when to choose it, roles and differences
When to pick Managed Instance over Azure SQL Database or SQL Server, the roles and permissions model with Entra, differences and pitfalls.
Azure SQL Managed Instance (MI) is often sold as "SQL Server in the cloud without the chores". That is half true: backups, patching and high availability are managed, but you still get a full instance engine with its own quirks. This is how I place it today, as a DBA who also runs Azure SQL Database and on-premises SQL Server.
When to choose Managed Instance
My starting question is simple: do I need an instance-level feature that Azure SQL Database does not offer? If not, a single database or an elastic pool is cheaper and easier to run.
flowchart TD
A["Workload to host"] --> B{"Instance-level features needed?"}
B -->|"No"| C["Azure SQL Database (single or elastic pool)"]
B -->|"Yes"| D{"OS access required?"}
D -->|"No"| E["Azure SQL Managed Instance"]
D -->|"Yes"| F["SQL Server on Azure VM or on-premises"]
The reasons that really justify an MI, in my experience:
- Cross-database processing: three-part name queries (
database.schema.table) across several databases of one instance. - Transactional replication as a publisher: an Azure SQL Database can only be a subscriber.
- Linked servers to external sources (for example a SQL Server left on-premises).
- SQL Server Agent, Database Mail, CLR, intra-instance Service Broker.
- A lift and shift migration with few application changes.
If none of these holds, the MI becomes a permanent fixed cost: an instance is billed continuously, loaded or not.
Quick comparison
| Criterion | Azure SQL Database | Managed Instance | SQL Server on-premises |
|---|---|---|---|
| Unit of management | Database (or pool) | Instance | Instance |
| Cross-database queries | No (except elastic query) | Yes | Yes |
| SQL Agent | No (elastic jobs) | Yes | Yes |
| Replication | Subscriber only | Publisher, distributor, subscriber | Full |
| Linked servers | No | Yes (with limits) | Yes |
| Networking | Public or private endpoint | Injected in a dedicated subnet | Yours |
| OS / file access | No | No | Yes |
| Backups | Automatic | Automatic (+ manual COPY_ONLY) | Yours |
| Patching | Managed | Managed | Yours |
Always check the official T-SQL differences page: the feature list keeps moving.
Roles and permissions model
Two layers stack on top of each other.
Management plane (Azure RBAC): who can create, scale, delete the instance or configure its backups. Built-in roles such as SQL Managed Instance Contributor serve this purpose. They grant no access to data.
Data plane (SQL): like SQL Server, with server-level logins. The big difference is Microsoft Entra authentication: you set an Entra administrator, then create logins for groups.
-- Instance level (master database)
CREATE LOGIN [dba-team@contoso.com] FROM EXTERNAL PROVIDER;
ALTER SERVER ROLE sysadmin ADD MEMBER [dba-team@contoso.com];
-- In an application database
USE AppDb;
CREATE USER [app-readers] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-readers];
My habits: Entra groups rather than named accounts, very few sysadmin members, SQL logins only when a tool leaves no alternative.
Pitfalls and lessons learned
- Networking comes first: dedicated, delegated subnet, with NSG and route table managed by the service. Poor preparation is paid for at creation time, which can take a long while.
- Collation and time zone are chosen at instance creation and cannot be changed. Check them before migrating.
- Jobs and logins do not follow a restore: recreate and test them.
- An MI is not a full SQL Server: no disk access,
BACKUP ... TO DISKis impossible, a few configuration options are missing. - The fixed cost pushes toward consolidation, but a shared instance is also a shared point of failure: weigh it.
- It often stays out of necessity, not taste: you retire it one dependency at a time (cross-database processing, replications, linked servers) and then move to single databases.
My rule: Azure SQL Database by default, Managed Instance when a real instance feature demands it, SQL Server on a VM only when you need the OS.