This chapter covers two key deployment options for SQL Server in Azure: Azure SQL Managed Instance and SQL Server on Azure Virtual Machines (SQL VM). Both are part of the 'relational data' domain and appear in roughly 15-20% of DP-900 exam questions related to deployment options. Understanding when to choose Managed Instance over SQL VM—and the specific limitations and capabilities of each—is critical for the exam. We will dive deep into architecture, migration paths, feature comparison, and common exam traps.
Jump to a section
Think of Azure SQL Managed Instance as a condominium unit and SQL Server on Azure VM as a standalone house. In a condo, you own the interior (your database) but the building management handles the roof, plumbing, electrical, and security (OS patching, backups, storage replication). You can paint walls (configure instance-level settings) but can't change the building's foundation (the underlying hypervisor). In a house, you own everything—land, structure, and interior—so you must mow the lawn (apply Windows updates), fix the roof (handle storage failures), and install your own security system (firewall, anti-malware). You have full control but full responsibility. The condo association (Microsoft) ensures consistent upgrades and shared amenities like a pool (automated backups, high availability). If you want to add a second story (scale up), the condo may limit how high you can build (instance size limits), while with a house you can theoretically add as many floors as your lot allows (VM size limits) but must hire contractors (manual configuration). Both have trade-offs: condos are easier to maintain and come with predictable costs, while houses give you total freedom at the expense of ongoing maintenance. In the Azure world, Managed Instance is for customers who want built-in SQL Server features like Service Broker, SQL Agent, and cross-database queries without managing the OS, while SQL VM is for those who need full control or legacy compatibility that Managed Instance doesn't support.
What Are Azure SQL Managed Instance and SQL VM?
Azure SQL Managed Instance (MI) and SQL Server on Azure Virtual Machines (SQL VM) are two of the four deployment options for SQL Server in Azure (the others being Azure SQL Database single database and elastic pool). They are designed for customers who need to lift-and-shift existing SQL Server workloads to Azure with minimal changes, or who require features not available in Azure SQL Database (e.g., SQL Agent, Service Broker, cross-database queries, CLR, filestream).
Azure SQL Managed Instance: A platform-as-a-service (PaaS) offering that provides near-100% compatibility with on-premises SQL Server. Microsoft manages the underlying operating system, storage, and networking. You get built-in high availability (99.99% SLA), automated backups, automatic patching, and a native virtual network (VNet) integration. You can choose from General Purpose (GP) or Business Critical (BC) service tiers. The instance size ranges from 4 to 128 vCores, with storage up to 16 TB (GP) or 4 TB (BC).
SQL Server on Azure VM: An infrastructure-as-a-service (IaaS) offering where you provision a Windows or Linux virtual machine and install SQL Server on it. You have full control over the OS, SQL Server version, and configuration. Microsoft provides a pre-configured SQL Server image in the Azure Marketplace, but you are responsible for patching, backups, and high availability setup. You can use Azure benefits like Hybrid Benefit and Reserved Instances. The VM sizes range from small (2 vCPUs, 8 GB RAM) to massive (416 vCPUs, 12 TB RAM) with attached premium SSDs.
How They Work Internally
Managed Instance runs on a dedicated set of virtual machines that are part of a Microsoft-managed cluster. Each instance is placed in a specific virtual network (VNet) and subnet. The subnet must be delegated to Microsoft.Sql/managedInstances. The instance has a fully qualified domain name (FQDN) like yourinstance.xxxxx.database.windows.net. Network traffic is routed through the VNet, and you can connect via Azure ExpressRoute or VPN. The instance uses a SQL Server database engine that is kept up-to-date by Microsoft. Backups are taken automatically every 5-10 minutes (transaction log), daily full, and weekly differential, with a retention period up to 35 days. High availability is built-in: in BC tier, a secondary replica is synchronously committed; in GP tier, remote storage is used and data is replicated three times within the region.
SQL VM is just a regular Azure VM with SQL Server installed. You can use the Azure portal to provision a VM with a pre-installed SQL Server image. The VM has its own OS disk and data disks. You can connect via RDP or SSH. SQL Server runs as a service. You must configure backups yourself (e.g., using Azure Backup or manual scripts). High availability can be achieved by creating a Windows Failover Cluster with SQL Server Always On Availability Groups, which requires at least two VMs and a load balancer. Patching is your responsibility, though Azure Automation Update Management can help. The VM's storage can be premium SSDs, standard SSDs, or HDDs, with throughput limits based on VM size.
Key Components, Values, Defaults, and Timers
Managed Instance defaults: - Backup retention: 7 days by default, configurable up to 35 days. - Time zone: UTC by default, can be changed to any supported time zone. - Collation: SQL_Latin1_General_CP1_CI_AS by default. - Max storage: GP up to 16 TB, BC up to 4 TB. - vCore range: 4 to 128. - Memory: 5.1 GB per vCore for GP, 5.1 GB per vCore for BC (with additional memory for secondary replica). - License: Included or Azure Hybrid Benefit.
SQL VM defaults: - VM image: Windows Server 2019/2022 with SQL Server 2019/2022 (Evaluation edition by default). - Storage: 30 GB OS disk (premium SSD recommended), data disks configurable. - SQL Server edition: Developer, Express, Web, Standard, Enterprise (choose at deployment). - Backup: Not configured automatically. - Patching: Manual or via Azure Automation.
Timers: - Managed Instance automated backup schedule: Full backup weekly, differential backup every 12 hours, transaction log backup every 5-10 minutes. - Point-in-time restore (PITR) window: Up to 35 days. - SQL VM: No automatic backup schedule; you set up your own.
Configuration and Verification Commands
Managed Instance: - Create via Azure CLI:
az sql mi create --resource-group myRG --name myMI --location eastus --admin-user myadmin --admin-password MyP@ssw0rd! --vnet myVNet --subnet mySubnetVerify connection string: Server=myMI.xxxxx.database.windows.net;Database=myDB;User Id=myadmin;Password=MyP@ssw0rd!;
Check service tier:
SELECT DATABASEPROPERTYEX('myDB', 'ServiceObjective');SQL VM: - Create via Azure CLI:
az vm create --resource-group myRG --name mySQLVM --image MicrosoftSQLServer:sql2019-ws2019:enterprise:latest --admin-username azureuser --admin-password MyP@ssw0rd!Connect via RDP: mstsc /v:mySQLVM.publicip
Check SQL Server version:
SELECT @@VERSION;Interaction with Related Technologies
Both options integrate with Azure services: - Azure Active Directory (AAD): Managed Instance supports AAD authentication natively; SQL VM requires AAD integration via domain join. - Azure Key Vault: Both support Transparent Data Encryption (TDE) with Key Vault. - Azure Backup: Managed Instance has built-in backup; SQL VM can use Azure Backup for SQL Server workloads. - Azure Site Recovery: SQL VM can be replicated to another region; Managed Instance has native geo-replication (auto-failover groups). - Azure Monitor: Both can send logs and metrics to Azure Monitor Log Analytics.
Exam Focus: Key Distinctions
The DP-900 exam tests your ability to choose between Managed Instance and SQL VM based on requirements. Remember:
Choose Managed Instance when you need PaaS benefits (automated backups, patching, HA) and near-100% SQL Server compatibility, especially for lift-and-shift migrations.
Choose SQL VM when you need full control over the OS and SQL Server, need to run older SQL Server versions (e.g., 2008, 2012), require third-party software on the same VM, or need extreme scalability (e.g., 128+ vCores).
Common exam trap: Managed Instance does NOT support SQL Server Integration Services (SSIS) running on the instance itself (you need Azure Data Factory or a separate VM). Also, Managed Instance does not support cross-instance queries (linked servers to other instances) in all scenarios.
Assess compatibility and requirements
Before choosing between Managed Instance and SQL VM, you must assess your workload. Check SQL Server version, edition, and feature usage. Use the Azure Database Migration Guide or Data Migration Assistant (DMA) to identify blocking features. For Managed Instance, note that it does not support SQL Server 2008 or older versions directly (you must upgrade first). It also does not support Replication with non-SQL Server subscribers, or SQL Server Analysis Services (SSAS) on the same instance. If your application uses these, SQL VM is the better choice. Also consider performance requirements: Managed Instance offers up to 128 vCores, while SQL VM can go up to 416 vCores. Assess network latency: Managed Instance is always in a VNet, so on-premises connectivity via ExpressRoute or VPN is smooth.
Choose deployment option based on management needs
Decide how much management overhead you want. Managed Instance offloads OS patching, backup management, and high availability to Microsoft. You only manage the database schema and instance-level settings (collation, time zone, server-level firewall rules). SQL VM requires you to manage everything: OS updates, SQL Server cumulative updates, backup schedules, disk space, and HA setup. If your team is small or you want to focus on application development, Managed Instance is ideal. If you have dedicated DBAs who need full control, SQL VM is better. Also consider cost: Managed Instance has a higher per-vCore cost than SQL VM because it includes management services. SQL VM allows you to use Azure Hybrid Benefit and Reserved Instances to reduce costs significantly.
Provision and configure networking
For Managed Instance, you must create a dedicated subnet (minimum size /28) delegated to Microsoft.Sql/managedInstances. The subnet cannot contain other resources. You also need a network security group (NSG) that allows inbound traffic on port 1433 (TCP) and 11000-11999 (for redirect). For SQL VM, you can use any subnet in a VNet. You can assign a public IP if needed, but for security, it's better to use a private IP and connect via VPN/ExpressRoute. Configure NSG rules to allow only necessary traffic. For both, enable Azure SQL connectivity from on-premises via point-to-site or site-to-site VPN. Remember: Managed Instance always has a private IP address (no public endpoint by default), but you can enable a public endpoint for specific scenarios.
Migrate data using appropriate tools
For Managed Instance, use Azure Database Migration Service (DMS) for online migrations with minimal downtime, or use native backup/restore: take a full backup of your on-premises SQL Server database, copy the .bak file to Azure Blob Storage, then restore using T-SQL: `RESTORE DATABASE myDB FROM URL = 'https://mystorage.blob.core.windows.net/backups/myDB.bak'`. For SQL VM, you can also use DMS, or simply copy the backup file to the VM and restore using SQL Server Management Studio (SSMS). Another option is to use transactional replication to keep the target in sync during migration. For large databases (over 1 TB), consider using Azure Data Box to physically ship data to Azure.
Configure post-migration settings and monitoring
After migration, configure backups for SQL VM (Managed Instance already has them). Set up Azure Monitor alerts for performance metrics like DTU/vCore usage, storage space, and deadlocks. For Managed Instance, you can use the built-in performance dashboard in Azure portal. For SQL VM, install the Azure Monitor agent to collect SQL Server logs. Also configure security: enable Azure AD authentication for Managed Instance (requires creating an AAD admin). For SQL VM, you can join the VM to your on-premises domain or use Azure AD Domain Services. Finally, test the application connection strings: Managed Instance uses a fully qualified domain name (FQDN) like `yourinstance.database.windows.net`, while SQL VM uses the VM's private IP or DNS name.
Enterprise Scenario 1: Lift-and-Shift of Legacy Application
A financial services company has a 15-year-old SQL Server 2012 database that runs critical trading applications. The database uses SQL Agent jobs for ETL, Service Broker for messaging, and cross-database queries. They want to move to Azure with minimal code changes. They choose Azure SQL Managed Instance because it supports all these features natively. They use the Azure Database Migration Service (DMS) to perform an online migration with minimal downtime. The instance is provisioned in a Business Critical tier to meet high availability requirements (99.99% SLA). They configure a failover group to another region for disaster recovery. Post-migration, they notice that the automatic backup schedule (every 5-10 minutes for logs) meets their RPO of 15 minutes. They also use the point-in-time restore feature to recover from a user error. The migration saves them from upgrading SQL Server licenses and reduces DBA overhead by 40%.
Enterprise Scenario 2: Hybrid Environment with Full Control
A healthcare organization needs to run SQL Server with custom CLR stored procedures and third-party monitoring agents that require OS-level access. They also need to run SQL Server 2016 (which is not supported in Managed Instance). They choose SQL Server on Azure VM. They deploy a Windows Server 2016 VM with SQL Server 2016 Enterprise using an Azure Marketplace image. They attach premium SSD disks for data and log files. They configure a Windows Failover Cluster with two VMs and a load balancer for high availability using SQL Server Always On Availability Groups. They use Azure Backup to take daily backups and store them in a Recovery Services vault. They also set up Azure Update Management to schedule OS patching. The total cost is lower than Managed Instance because they use Azure Hybrid Benefit and Reserved Instances. However, they need a dedicated DBA to manage patching and performance tuning.
Common Misconfigurations and Pitfalls
Managed Instance subnet size too small: A /29 subnet has only 8 IPs, but Azure uses 5 for internal purposes, leaving only 3 for the instance. Always use at least /28 (16 IPs) to allow for future scaling and maintenance operations.
SQL VM without backup: Many forget to configure backups after deployment. A single disk failure can cause data loss. Always set up automated backups using Azure Backup or a custom script.
Network latency for on-premises connections: Both options require careful network design. For Managed Instance, if the application is on-premises, use ExpressRoute or a VPN with sufficient bandwidth. For SQL VM, consider using Azure ExpressRoute with private peering to reduce latency.
DP-900 Exam Focus on Azure SQL Managed Instance and SQL VM
Objective Codes: This topic falls under 'Describe relational data workloads' and 'Describe relational data services on Azure' (DP-900 objectives 2.1 and 2.2). The exam expects you to differentiate between Managed Instance and SQL VM based on management responsibility, compatibility, and use cases.
Common Wrong Answers: 1. 'Managed Instance is always cheaper than SQL VM' – False. SQL VM can be cheaper if you use Hybrid Benefit and Reserved Instances, because you are not paying for the PaaS management overhead. Managed Instance has a higher per-vCore cost. 2. 'SQL VM supports all SQL Server features' – True, but the exam might ask about a specific feature that is NOT supported in Managed Instance (e.g., SQL Server 2008 compatibility, SSAS, SSIS). Candidates often choose Managed Instance for these features incorrectly. 3. 'Managed Instance does not support SQL Agent' – False. Managed Instance fully supports SQL Agent. The exam may present a scenario where SQL Agent is needed and both options are viable, but the correct answer depends on other factors like control vs. automation. 4. 'Both options have the same high availability SLA' – False. Managed Instance has a 99.99% SLA (BC tier) while SQL VM has a 99.95% SLA for a single VM (with premium disks) and 99.99% for a properly configured Always On Availability Group. But the exam may test that a single VM without HA has a lower SLA.
Specific Numbers and Terms:
Managed Instance vCore range: 4 to 128.
Managed Instance storage limits: GP up to 16 TB, BC up to 4 TB.
SQL VM can have up to 416 vCPUs (e.g., Standard_M416-208s_v2).
Backup retention for Managed Instance: up to 35 days.
Subnet requirement for Managed Instance: /28 minimum.
SQL VM supports all SQL Server versions from 2008 to 2022.
Edge Cases:
Managed Instance does not support FILESTREAM in GP tier (only BC).
Managed Instance does not support cross-database transactions in all cases (some limitations exist).
SQL VM can be stopped (deallocated) to save costs; Managed Instance cannot be stopped, it always runs.
How to Eliminate Wrong Answers:
If the scenario mentions 'full control over the OS' or 'need to install custom software', eliminate Managed Instance.
If the scenario mentions 'minimal administration' or 'automatic backups', eliminate SQL VM (unless the question asks for the cheapest option).
If the scenario mentions 'SQL Server 2008', eliminate Managed Instance (not supported).
If the scenario mentions 'cross-instance queries' or 'linked servers', both are possible, but Managed Instance has limitations (only within same instance).
Always check the number of vCores required: if more than 128, SQL VM is the only option.
Managed Instance is a PaaS offering with automated management; SQL VM is IaaS with full control.
Managed Instance supports up to 128 vCores; SQL VM supports up to 416 vCores.
Managed Instance requires a /28 or larger subnet delegated to Microsoft.Sql/managedInstances.
Managed Instance does not support SQL Server 2008/2012/2014; you must upgrade or use SQL VM.
SQL VM can be stopped to save costs; Managed Instance cannot be stopped.
Both support Azure Hybrid Benefit for SQL Server licensing.
Managed Instance has automatic backups with up to 35 days retention; SQL VM requires manual backup configuration.
For high availability, Managed Instance offers built-in 99.99% SLA (BC tier); SQL VM requires manual setup (e.g., Always On).
These come up on the exam all the time. Here's how to tell them apart.
Azure SQL Managed Instance
PaaS: Microsoft manages OS, storage, patching, backups.
Near-100% SQL Server compatibility (SQL Agent, Service Broker, etc.).
Built-in high availability (99.99% SLA in BC tier).
Automatic backups with up to 35 days retention.
Always in a VNet with private IP; no public endpoint by default.
SQL Server on Azure VM
IaaS: You manage OS, SQL Server, patching, backups.
Full control over SQL Server version and configuration.
High availability must be manually configured (e.g., Always On).
Backups must be manually configured (Azure Backup or custom).
Can have public IP or private IP; full network control.
Azure SQL Managed Instance
vCore range: 4 to 128.
Storage: GP up to 16 TB, BC up to 4 TB.
Cannot be stopped/deallocated (always running).
Supports SQL Server 2016+ feature set.
License included or Azure Hybrid Benefit.
SQL Server on Azure VM
vCore range: 2 to 416 (depending on VM size).
Storage: up to 64 TB (with multiple disks and Storage Spaces).
Can be stopped (deallocated) to save compute costs.
Supports all SQL Server versions from 2008 to 2022.
License: bring your own (Azure Hybrid Benefit) or pay-as-you-go.
Mistake
Managed Instance is just a renamed Azure SQL Database.
Correct
Managed Instance is a separate PaaS offering with near-100% SQL Server compatibility, including instance-level features like SQL Agent, Service Broker, and cross-database queries. Azure SQL Database is a single database service that does not support these instance-level features. The two are different deployment options.
Mistake
SQL VM is always more expensive than Managed Instance.
Correct
SQL VM can be cheaper if you use Azure Hybrid Benefit (bring your own SQL license) and Reserved Instances (1-3 year terms). Managed Instance includes the SQL license cost, so its per-vCore price is higher. For workloads with low vCore requirements, SQL VM may be more cost-effective.
Mistake
Managed Instance supports all SQL Server versions.
Correct
Managed Instance only supports SQL Server 2016 and later versions (feature parity). If you need SQL Server 2008, 2012, or 2014, you must upgrade first or use SQL VM. The exam may test this limitation.
Mistake
Both Managed Instance and SQL VM have the same backup capabilities.
Correct
Managed Instance has automated backups (full, differential, log) with a retention period up to 35 days. SQL VM has no automatic backups; you must configure them yourself using Azure Backup or custom scripts.
Mistake
SQL VM cannot be used for disaster recovery.
Correct
SQL VM can be used for disaster recovery by setting up Always On Availability Groups across regions, or by using Azure Site Recovery. Managed Instance also supports auto-failover groups. Both can achieve DR, but the configuration effort differs.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Choose Managed Instance when you want PaaS benefits (automated backups, patching, HA) and need near-100% SQL Server compatibility for features like SQL Agent, Service Broker, cross-database queries, and CLR. It's ideal for lift-and-shift migrations with minimal changes. Avoid Managed Instance if you need full OS control, require older SQL Server versions (2008-2014), need to run SSIS or SSAS on the same instance, or require more than 128 vCores.
Yes, Azure Hybrid Benefit is available for Managed Instance. You can use your existing SQL Server licenses with Software Assurance to reduce the cost. The same applies to SQL VM. The exam may test that both options support Hybrid Benefit.
The minimum subnet size is /28 (16 IP addresses). Azure reserves 5 IP addresses for internal use, so you have 11 available for the instance and future scaling. Using a smaller subnet (e.g., /29) will cause deployment failure.
Managed Instance does not support SSIS as a built-in feature. To run SSIS, you must use Azure Data Factory with Azure-SSIS Integration Runtime or provision a separate SQL VM. This is a common exam trap.
By default, Managed Instance has only a private IP address and is accessible only from within the same VNet or from on-premises via VPN/ExpressRoute. You can enable a public endpoint, but it is disabled by default. SQL VM can have a public IP if you configure it, but it's not recommended for security.
For General Purpose tier, maximum storage is 16 TB. For Business Critical tier, maximum storage is 4 TB. SQL VM can have up to 64 TB depending on the VM size and number of disks.
Managed Instance has built-in point-in-time restore (PITR) with a retention period of up to 35 days. For SQL VM, you must configure your own backup strategy using SQL Server native backup or Azure Backup to enable PITR. The exam may test that PITR is automatic only for Managed Instance.
You've just covered Azure SQL Managed Instance and SQL VM — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?