LLI Wiki — My Digital Knowledge Base

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:

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

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.

Mastodon LLIMastodon SocialFosstodon