Azure SQL Managed Instance – Complete Guide for DP-900
Azure SQL Managed Instance is a fully managed Platform-as-a-Service (PaaS) deployment option within the Azure SQL family. It is designed to provide near-100% compatibility with the on-premises SQL Server database engine, making it the ideal choice for organizations that want to migrate their existing SQL Server workloads to the cloud with minimal changes.
Why Is Azure SQL Managed Instance Important?
Understanding Azure SQL Managed Instance is critical for the DP-900 exam and for real-world cloud data scenarios because:
• It bridges the gap between on-premises SQL Server and fully cloud-native Azure SQL Database.
• It supports features that Azure SQL Database (single database or elastic pool) does not, such as cross-database queries, SQL Server Agent, Service Broker, CLR integration, and linked servers.
• It enables lift-and-shift migrations with minimal application refactoring, reducing the cost and risk of cloud adoption.
• It removes the burden of infrastructure management (patching, backups, high availability) while still providing instance-scoped features.
• It is a key topic in the DP-900 exam under the domain of relational data on Azure.
What Is Azure SQL Managed Instance?
Azure SQL Managed Instance is a PaaS cloud service that provides an instance-level deployment of SQL Server in Azure. Key characteristics include:
• Instance-scoped features: Unlike Azure SQL Database, which is database-scoped, Managed Instance operates at the instance level. This means you can host multiple databases within a single managed instance, and those databases can interact with each other (cross-database queries).
• Near-complete SQL Server compatibility: It supports most SQL Server features, including T-SQL, stored procedures, views, triggers, SQL Server Agent jobs, Database Mail, and more.
• VNet integration: Managed Instance is deployed inside an Azure Virtual Network (VNet), providing network isolation and a private IP address. This makes it suitable for enterprises with strict security requirements.
• Fully managed: Microsoft handles automated backups, patching, monitoring, and high availability. You do not need to manage the underlying VM or operating system.
• Built-in high availability: It uses a technology similar to Always On Availability Groups to provide automatic failover and data redundancy.
• Service tiers: Managed Instance supports General Purpose (for standard workloads, uses remote storage) and Business Critical (for high-performance and low-latency workloads, uses local SSD storage and includes a built-in read replica).
How Does Azure SQL Managed Instance Work?
1. Deployment: You provision a Managed Instance within an Azure Virtual Network. A dedicated subnet is required for the Managed Instance, and this subnet cannot contain other resources.
2. Connectivity: Applications connect to the Managed Instance using a DNS-based endpoint (e.g., myinstance.abc123.database.windows.net). Since it resides in a VNet, on-premises networks can connect via VPN or ExpressRoute, providing secure hybrid connectivity.
3. Migration: You can migrate existing on-premises SQL Server databases to Managed Instance using:
- Azure Database Migration Service (DMS) – for online (minimal downtime) or offline migrations.
- Native backup and restore – you back up a database to Azure Blob Storage and restore it on the Managed Instance.
- BACPAC import/export – export the schema and data as a BACPAC file and import it.
4. Management: You manage it through the Azure Portal, Azure CLI, PowerShell, or SQL Server Management Studio (SSMS). Since it is PaaS, Microsoft handles OS-level and engine-level patching automatically.
5. Automated backups: Full backups are taken weekly, differential backups every 12 hours, and transaction log backups every 5–10 minutes. Point-in-time restore (PITR) is supported with a configurable retention period of 7–35 days. Long-term retention (LTR) is also available for up to 10 years.
6. Scaling: You can scale compute (vCores) and storage independently. Scaling operations may cause a brief connectivity interruption, but the service manages this transparently.
Key Differences: Managed Instance vs. Azure SQL Database vs. SQL Server on Azure VM
• Azure SQL Database (Single/Elastic Pool): Database-scoped, no cross-database queries, no SQL Agent, fully managed PaaS. Best for new cloud-born applications.
• Azure SQL Managed Instance: Instance-scoped, near-full SQL Server compatibility, VNet-native, fully managed PaaS. Best for lift-and-shift migrations of existing SQL Server workloads.
• SQL Server on Azure VM (IaaS): Full SQL Server compatibility (100%), you manage the OS and SQL Server. Best when you need OS-level access or features not supported by Managed Instance (e.g., FILESTREAM, certain third-party agents).
When to Choose Azure SQL Managed Instance
Choose Managed Instance when:
• You are migrating on-premises SQL Server databases and want minimal code changes.
• You need instance-level features like SQL Server Agent, cross-database queries, Service Broker, or CLR.
• You require VNet isolation for security and compliance.
• You want to avoid the overhead of managing VMs but need more compatibility than Azure SQL Database offers.
• You have multiple databases that need to interact with each other within a single instance.
Exam Tips: Answering Questions on Azure SQL Managed Instance
1. Remember the key differentiator: Managed Instance provides instance-scoped features and near-100% SQL Server compatibility. If an exam question mentions needing SQL Server Agent jobs, cross-database queries, CLR, Service Broker, or linked servers in a PaaS environment, the answer is almost always Managed Instance.
2. PaaS, not IaaS: Managed Instance is a PaaS offering. If a question asks about reducing administrative overhead while maintaining SQL Server compatibility, Managed Instance is the answer. If the question requires full OS-level access, SQL Server on Azure VM (IaaS) is the answer.
3. VNet requirement: Always remember that Managed Instance is deployed inside a Virtual Network. Questions about network isolation, private endpoints, or hybrid connectivity often point to Managed Instance.
4. Migration scenarios: If the question describes a lift-and-shift migration of existing SQL Server workloads to Azure with minimal changes, think Managed Instance. Look for keywords like existing SQL Server, minimal refactoring, SQL Agent jobs, or multiple databases.
5. Service tiers: Know the two tiers: General Purpose (standard workloads, remote storage) and Business Critical (high-performance, local SSD, built-in read replica). If a question mentions low-latency reads or a read-only replica, think Business Critical.
6. Do not confuse with Azure SQL Database: Azure SQL Database is database-scoped and does not support instance-level features. If the scenario only requires a single isolated database with no cross-database dependencies, Azure SQL Database may be sufficient. But if instance-level features are needed, choose Managed Instance.
7. Backup and restore: Managed Instance supports native backup and restore from Azure Blob Storage, which Azure SQL Database does not support in the same way. This is a frequent exam topic.
8. Pricing model: Managed Instance uses a vCore-based purchasing model. Remember that you pay for compute (vCores), storage, and backup storage. There is no DTU-based option for Managed Instance (DTU is only for Azure SQL Database).
9. Elimination strategy: In multiple-choice questions, eliminate options that suggest IaaS when the question clearly wants a managed/PaaS solution. Similarly, eliminate Azure SQL Database when instance-scoped features are mentioned.
10. Watch for tricky wording: Some questions may describe a scenario that seems to need a full VM but actually only needs Managed Instance. Focus on whether the requirement is about SQL Server features (Managed Instance can handle most) or about OS/infrastructure access (which requires a VM).
By thoroughly understanding what Azure SQL Managed Instance is, how it differs from other Azure SQL options, and when to recommend it, you will be well-prepared to answer DP-900 exam questions on this topic confidently.
