This chapter provides a deep dive into Azure SQL Managed Instance, a key PaaS offering in Azure's relational data portfolio. For the DP-900 exam, understanding MI's role, its compatibility with SQL Server, and its differences from other Azure SQL options is critical—approximately 10-15% of questions on relational data touch on managed instance scenarios. You will learn exactly what MI is, how it achieves near-100% SQL Server compatibility, when to choose it over Azure SQL Database or SQL Server on VMs, and the specific exam traps to avoid.
Jump to a section
Imagine a large corporation with headquarters running a massive on-premises SQL Server cluster. To support a regional office, the company decides to open a branch that operates exactly like headquarters but with a fraction of the staff. Instead of buying servers, hiring DBAs, and managing backups, the branch uses a fully managed service: a pre-configured office space with all utilities, security, and IT support included. The branch manager (the application) connects to this office via a dedicated private network tunnel (Azure ExpressRoute or VPN) that ensures traffic never traverses the public internet. The office has its own private IP address (private endpoint) and is isolated from other tenants. The branch has a fixed capacity of desks and computers (vCores and storage) that can be scaled up or down with a simple request. Daily backups, patching, and high availability are handled by the building management (Azure platform). The branch can run any SQL Server application that expects a full instance, including SQL Agent jobs, cross-database queries, and linked servers, just like at headquarters. The key difference is that the branch does not own or manage any infrastructure—it just uses the space. This mirrors Azure SQL Managed Instance: it provides near 100% SQL Server instance-level compatibility while offloading all management to Azure, making it ideal for lift-and-shift migrations where you want to avoid refactoring applications for Azure SQL Database.
What is Azure SQL Managed Instance?
Azure SQL Managed Instance (MI) is a Platform-as-a-Service (PaaS) deployment option for Azure SQL that provides near-100% compatibility with SQL Server on-premises. It is designed for customers who want to migrate existing SQL Server workloads to Azure with minimal changes, but who need instance-level features that Azure SQL Database (single database or elastic pool) does not support. MI runs a managed instance of the SQL Server database engine in a virtual network (VNet) isolated environment, giving you a private IP address and full control over network security. Microsoft handles patching, backups, high availability, and storage, but you still have access to SQL Server Agent, cross-database queries, linked servers, CLR, Service Broker, and other instance-scoped features.
How MI Achieves Near-100% Compatibility
MI is built on the same SQL Server engine as Azure SQL Database, but with a key difference: it exposes a full instance boundary. This means you can run multiple databases on a single instance, use instance-level logins, configure SQL Agent jobs, and set up cross-database transactions. Microsoft maintains a compatibility matrix that lists which SQL Server features are available in MI versus Azure SQL Database. As of the latest updates, MI supports over 95% of SQL Server 2019 features. Features that are not supported include: FileStream/FileTable (limited support), PolyBase (external tables via SQL Server PolyBase), and some legacy features like SQL Server Reporting Services (SSRS) or SQL Server Integration Services (SSIS) (these require separate Azure services). The exam focuses on the fact that MI is the best choice when you need instance-scoped features and want a fully managed service.
Internal Architecture: The Virtual Cluster
When you deploy an MI, Azure creates a virtual cluster consisting of multiple virtual machines (compute nodes) and a distributed storage system. The cluster is deployed inside your VNet subnet, which must be dedicated to the managed instance (no other resources can reside in that subnet). The subnet must have a network security group (NSG) that allows specific inbound and outbound traffic for management purposes. The cluster provides automatic failover: if the primary node fails, a secondary node takes over with no data loss (using Always On Availability Groups technology). The storage is backed by Azure Premium managed disks with built-in geo-redundancy. You can choose between General Purpose (standard tier, with remote storage) and Business Critical (premium tier, with local SSD storage for lower latency and higher performance).
Key Components and Defaults
vCores: You choose the number of virtual cores (2 to 80 vCores) and the hardware generation (Gen4 or Gen5). Gen5 is the default and offers Intel Xeon processors.
Storage: You provision storage up to 8 TB for General Purpose and up to 4 TB for Business Critical (with potential for larger sizes in preview). The minimum storage is 32 GB.
Backup: Automated backups are enabled by default with point-in-time restore (PITR) retention up to 35 days. Long-term retention (LTR) can be configured up to 10 years.
High Availability: Built-in with a 99.99% SLA for Business Critical and 99.95% for General Purpose (when configured with zone-redundant deployment).
Security: Includes Azure Active Directory (Azure AD) authentication, Transparent Data Encryption (TDE) at rest, Always Encrypted, and dynamic data masking.
Networking: MI is always deployed in a VNet subnet with a delegated subnet (Microsoft.Sql/managedInstances). You must configure a route table and NSG to allow management traffic from Azure's management infrastructure.
Configuration and Verification
To create a Managed Instance via Azure CLI:
az sql mi create \
--resource-group myResourceGroup \
--name myManagedInstance \
--location eastus \
--admin-user sqladmin \
--admin-password MyPassword123! \
--subnet /subscriptions/.../subnets/mySubnet \
--vcores 8 \
--storage 256 \
--tier GeneralPurposeTo verify the instance and its details:
az sql mi show --resource-group myResourceGroup --name myManagedInstanceInteraction with Related Technologies
MI can be used with Azure Data Factory for ETL, Azure Analysis Services for analytics, and Power BI for reporting. It supports linked servers to connect to other SQL Server instances (on-premises or in VMs). It also integrates with Azure Key Vault for TDE and Always Encrypted keys. For disaster recovery, you can configure auto-failover groups between two MIs in different regions, which provides asynchronous replication and automatic failover.
Exam-Relevant Details
MI is a PaaS offering, not IaaS. You do not manage the underlying OS or SQL Server binaries.
MI requires a dedicated subnet in a VNet. The subnet must be delegated to Microsoft.Sql/managedInstances.
You can connect to MI using SQL Server Management Studio (SSMS) or Azure Data Studio, just like a regular SQL Server.
Migration to MI can be done using Azure Database Migration Service (DMS) with minimal downtime.
MI supports both Windows and SQL Server authentication, but Azure AD authentication is recommended.
The exam may ask: 'Which Azure SQL option should you choose for a legacy application that uses SQL Agent jobs and cross-database queries?' The answer is Azure SQL Managed Instance.
Provision a Managed Instance
In the Azure portal, navigate to 'Create a resource' and select 'Azure SQL Managed Instance'. Fill in the required fields: subscription, resource group, instance name, region, admin credentials, and compute + storage configuration. Under networking, you must specify a VNet and a dedicated subnet (at least /28 in size). The subnet cannot contain any other resources. Once you click 'Review + create', Azure deploys the virtual cluster. This process takes 4-6 hours because it involves provisioning VMs, configuring storage, and setting up the network. During this time, the instance status shows 'Creating'. You can monitor progress in the activity log.
Configure Network Security
After deployment, you must ensure the subnet's NSG allows management traffic. Azure automatically creates an NSG with required rules, but you can view them: inbound rules allow TCP ports 9000, 9003, 1438, 1440, and 1452 from the 'AzureCloud' service tag. Outbound rules allow all traffic to the internet for updates. The subnet also needs a route table with a default route (0.0.0.0/0) to the internet via the 'VirtualNetworkServiceEndpoint' next hop. Do not modify these rules unless you understand the impact. If you block management traffic, the instance may become unavailable. To verify connectivity, you can test from a VM in the same VNet using SQL Server Management Studio (SSMS) with the private endpoint (e.g., myinstance.xxxxx.database.windows.net).
Migrate Database Using DMS
Use Azure Database Migration Service (DMS) to migrate an on-premises SQL Server database to MI. First, create a DMS service in the same region as the target MI. Then, create a migration project, select the source (SQL Server) and target (MI). DMS will assess the source database for compatibility issues (e.g., unsupported features), then perform a schema migration and data migration. For minimal downtime, configure continuous sync from the source to the target, then perform a cutover. DMS uses the transaction log to keep the target in sync. The migration can take hours to days depending on database size and network bandwidth. After migration, update application connection strings to point to the MI endpoint. DMS supports both online (minimal downtime) and offline migrations.
Configure Security and High Availability
After migration, enable Azure AD authentication: in the Azure portal, go to your MI, select 'Active Directory admin', and set an Azure AD user or group as the admin. Enable TDE: go to 'Transparent data encryption' and turn it on (uses a service-managed key by default, or you can use a customer-managed key from Key Vault). For high availability, the Business Critical tier provides a local read-only replica that can be used for read-scale workloads. You can also configure an auto-failover group for disaster recovery: in the MI overview, select 'Failover groups' and add a secondary MI in a paired region. The failover group provides a listener endpoint that applications can use to automatically connect to the primary after failover.
Monitor and Scale
Monitor performance using Azure Monitor metrics: CPU percentage, storage space used, IOPS, and session count. You can set up alerts for thresholds (e.g., CPU > 80% for 5 minutes). To scale, you can change the vCore count or storage size via the Azure portal or CLI. Scaling may cause a brief failover (less than 30 seconds) as the instance moves to a new node. For Business Critical tier, scaling is faster because it uses local SSDs. You can also enable 'Zone-redundant deployment' (in supported regions) for better availability within a region. To verify scaling, check the instance status: after a scale operation, the status goes to 'Updating' and then 'Ready'.
Enterprise Scenario 1: Lift-and-Shift of a Legacy ERP System
A manufacturing company runs an on-premises ERP system on SQL Server 2016 with 50 databases, 50 SQL Agent jobs, and linked servers to other databases. They want to move to Azure to reduce hardware costs and improve disaster recovery. They cannot refactor the application to use Azure SQL Database because it uses cross-database queries and SQL Agent jobs extensively. The solution: migrate to Azure SQL Managed Instance. Using Azure DMS, they perform an online migration with minimal downtime (2 hours). They configure an auto-failover group to a secondary region for disaster recovery. Post-migration, they reduce their monthly costs by 30% by right-sizing the vCores (from 32 cores on-prem to 16 vCores in MI). They also enable TDE and Azure AD authentication. The key challenge was ensuring the subnet had enough IP addresses (they used a /27 subnet). They also had to update firewall rules to allow the MI management traffic. The migration was successful, and the application worked without any code changes.
Enterprise Scenario 2: Isolated Development Environment with Instance Features
A financial services firm needs a development environment that mirrors production SQL Server but with lower cost. They create an Azure SQL Managed Instance in a separate VNet with a smaller vCore count (4 vCores). Developers use SQL Agent to run nightly batch jobs and cross-database queries. The environment is isolated from the internet; only developers on the corporate VPN can connect via a private endpoint. They use point-in-time restore to quickly revert databases after testing. The challenge was that the development team initially tried to use Azure SQL Database, but it did not support SQL Agent jobs. They switched to MI and achieved full compatibility. The cost of MI is higher than Azure SQL Database, but the trade-off is acceptable for development needs.
Common Misconfiguration and Consequences
A common misconfiguration is not delegating the subnet correctly. If you create a subnet without the delegation 'Microsoft.Sql/managedInstances', the deployment fails. Another mistake is allowing public endpoint access without restricting IP addresses; this can expose the instance to brute-force attacks. Also, some customers mistakenly choose Azure SQL Database when they need instance-scoped features, leading to application failures post-migration. The fix is to use MI instead. Performance issues arise when customers choose General Purpose tier for latency-sensitive workloads; they should use Business Critical with local SSDs.
DP-900 Objective Coverage
This section focuses on objective 2.2: 'Describe relational data workloads on Azure', specifically comparing Azure SQL Managed Instance to other Azure SQL options. The exam tests your ability to identify which PaaS option fits a given scenario. Key objective codes: - 2.2.1: Identify features of Azure SQL Managed Instance (instance-level features, VNet isolation, near-100% compatibility). - 2.2.2: Compare Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.
Common Wrong Answers and Why Candidates Choose Them
Choosing Azure SQL Database when the scenario requires SQL Agent jobs. Many candidates think Azure SQL Database supports SQL Agent, but it does not. They confuse the 'elastic job agent' (a separate service) with SQL Agent. The exam will describe a scenario with scheduled jobs and cross-database queries; the correct answer is MI.
Selecting SQL Server on Azure VMs for full control and compatibility. While VMs do provide full control, the exam often asks for a 'fully managed' service. Candidates pick VMs because they think 'fully managed' means they don't manage anything, but VMs are IaaS, not PaaS. MI is the PaaS option with high compatibility.
Thinking MI requires public endpoint. MI by default has a private endpoint in a VNet. Candidates may think it's public like Azure SQL Database. The exam might ask about network isolation; the correct answer is that MI is deployed in a VNet and can only be accessed via private IP or via a public endpoint that is disabled by default.
Specific Numbers and Terms on the Exam
The minimum subnet size for MI is /28 (14 usable IPs).
The default backup retention is 7 days (configurable up to 35 days).
The maximum storage for General Purpose is 8 TB; for Business Critical, 4 TB.
The SLA for Business Critical is 99.99%.
The term 'virtual cluster' appears in the exam context of MI architecture.
Edge Cases and Exceptions
MI does not support FileStream/FileTable fully (limited support).
MI does not support PolyBase for external tables (use SQL Server PolyBase in a VM).
MI does not support SSRS or SSIS natively (use Azure Analysis Services or Azure Data Factory).
MI requires a dedicated subnet; you cannot share the subnet with other resources.
During a failover, connections are lost and must be retried. The exam may ask about application resilience.
How to Eliminate Wrong Answers
If the scenario mentions 'SQL Agent jobs', 'cross-database queries', 'linked servers', or 'instance-level logins', the answer is MI (not Azure SQL Database).
If the scenario says 'fully managed' and 'lift-and-shift', the answer is MI (not SQL Server on VMs).
If the scenario requires 'VNet isolation' and 'private IP', the answer is MI (not Azure SQL Database which has a public endpoint by default).
If the scenario mentions 'serverless' or 'elastic pool', the answer is Azure SQL Database (not MI).
Azure SQL Managed Instance is a PaaS offering with near-100% SQL Server compatibility, designed for lift-and-shift migrations.
MI supports instance-level features like SQL Agent, cross-database queries, and linked servers.
MI requires a dedicated subnet (minimum /28) in a VNet, with a private endpoint for connectivity.
The default backup retention is 7 days (configurable up to 35 days); long-term retention up to 10 years.
Business Critical tier offers a 99.99% SLA and local SSD storage for low latency.
Scaling vCores or storage causes a brief failover (<30 seconds); applications must implement retry logic.
MI does not support SSRS, SSIS, FileStream (limited), or PolyBase natively.
Use Azure Database Migration Service (DMS) for online migration with minimal downtime.
These come up on the exam all the time. Here's how to tell them apart.
Azure SQL Managed Instance
Supports instance-level features: SQL Agent, cross-database queries, linked servers, CLR.
Deployed in a VNet with a private endpoint, providing network isolation.
Near-100% SQL Server compatibility, ideal for lift-and-shift migrations.
Fixed compute and storage; cannot use elastic pools.
Higher cost per vCore compared to Azure SQL Database.
Azure SQL Database (Single Database)
Supports only database-level features; no SQL Agent, no cross-database queries (except elastic query).
Has a public endpoint by default (can be configured with firewall rules or private endpoints).
Less compatible; requires application refactoring for instance-level dependencies.
Supports elastic pools for resource sharing among multiple databases.
Lower cost per vCore; can use serverless compute for intermittent workloads.
Mistake
Azure SQL Managed Instance is just a renamed SQL Server on a VM.
Correct
MI is a PaaS service where Microsoft manages the OS, SQL Server binaries, patching, backups, and high availability. You have no access to the underlying VM. SQL Server on Azure VMs is IaaS where you manage everything.
Mistake
MI supports all SQL Server features, including SSRS and SSIS.
Correct
MI does not support SSRS or SSIS natively. These require separate Azure services (Power BI Report Server, Azure Data Factory) or installation on VMs.
Mistake
You can connect to MI using a public endpoint by default.
Correct
MI is deployed in a VNet with a private endpoint. By default, public endpoint is disabled. You can enable it, but it's not recommended for security.
Mistake
MI can be scaled up or down without any downtime.
Correct
Scaling vCores or storage typically causes a brief failover (less than 30 seconds) during which connections are dropped. Application retry logic is required.
Mistake
MI supports elastic pools like Azure SQL Database.
Correct
MI does not support elastic pools. Each MI is a single instance with its own resources. For multi-tenant resource sharing, use Azure SQL Database elastic pools.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
The key difference is scope: MI is an instance-level PaaS offering that supports multiple databases, SQL Agent, cross-database queries, and linked servers, whereas Azure SQL Database is a database-level PaaS offering that supports only single databases or elastic pools. MI is deployed in a VNet with a private endpoint, while Azure SQL Database has a public endpoint by default. MI offers near-100% SQL Server compatibility, making it ideal for migrations with minimal changes. For the exam, remember that if a scenario requires instance-level features, choose MI.
Yes, Azure SQL Managed Instance fully supports SQL Agent jobs. You can create, schedule, and manage jobs just like in on-premises SQL Server. This is a key differentiator from Azure SQL Database, which does not support SQL Agent (though you can use elastic jobs as a separate service). On the exam, if you see a scenario requiring scheduled jobs, the correct answer is MI.
The minimum subnet size is /28, which provides 14 usable IP addresses. This is required because the virtual cluster needs several IPs for management and failover. Always ensure your VNet has a subnet of at least /28 for MI. The exam may ask this as a specific number.
Yes, TDE is supported in MI, both service-managed and customer-managed keys (via Azure Key Vault). It is enabled by default for new instances. TDE encrypts data at rest, including backups and transaction log files. This is a common exam topic: MI supports TDE, and you can bring your own key.
Use Azure Database Migration Service (DMS) for online or offline migrations. DMS assesses compatibility, migrates schema and data, and can perform continuous sync for minimal downtime. Alternatively, you can use backup and restore: take a native backup (.bak), copy it to Azure Blob Storage, and restore to MI using T-SQL. The exam expects you to know DMS as the recommended tool for large migrations.
The SLA is 99.99% for Business Critical tier (with zone-redundant deployment) and 99.95% for General Purpose tier. These SLAs apply to the instance being available for connections. The exam may ask you to compare SLAs between tiers.
By default, MI has only a private endpoint in a VNet, so direct internet access is not possible. You can enable a public endpoint (disabled by default) and configure an NSG to allow specific IP addresses. However, for security, it is recommended to use a jump box or VPN/ExpressRoute. The exam often tests that MI is VNet-isolated.
You've just covered Azure SQL Managed Instance Deep Dive — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?