3.2 Configuring Azure SQL Managed Instance for Scale and Performance

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

3.2 Configuring Azure SQL Managed Instance for Scale and Performance

Key Takeaways Explain This Topic AI

SQL Managed Instance is vCore-only, with General Purpose (remote storage), Business Critical (local SSD plus built-in readable secondary), and Next-gen General Purpose service tiers

Instance pools let multiple small managed instances - including 2-vCore instances, which only exist inside pools - share a common vCore allocation on the same virtual network

Zone redundancy distributes replicas across availability zones; it is a Business Critical capability in supported regions and requires no application changes

Scaling vCores or storage is an online operation ending in a brief failover; changing service tier (General Purpose to Business Critical or back) requires a data copy, making it size-of-data

Hardware choices (standard-series Gen5, premium-series, memory-optimized premium-series) change the memory-to-vCore ratio and storage throughput without changing the tier

Last updated: August 2026

Service Tiers and Sizing Model

Azure SQL Managed Instance (SQL MI) is the platform-as-a-service offering for workloads that need instance-level features - cross-database queries, SQL Server Agent, linked servers, CLR - with minimal re-engineering. Unlike Azure SQL Database, it is purchased only in the vCore model ; there is no DTU option. Sizing has three independent levers: the number of vCores (4, 8, 16, 24, 32, 40, 64, or 80), the amount of reserved storage, and the hardware configuration.

There are three service tiers :

General Purpose stores data and log files on remote Azure premium storage. Compute and storage are scaled and billed independently, and storage can be reserved up to 16 TB depending on vCore count. Latency is higher (single-digit to low double-digit milliseconds) because I/O traverses the network. This is the default, budget-oriented tier.

Business Critical places the database on local SSD and maintains an Always On availability group with multiple replicas behind the scenes, one of which is a free readable secondary for read scale-out (accessed with ApplicationIntent=ReadOnly , exactly as with Azure SQL Database). Local storage caps capacity lower than General Purpose (a few TB depending on vCore count and hardware), but delivers 1-2 ms I/O latency and in-memory OLTP.

Next-gen General Purpose keeps the General Purpose pricing posture but moves storage to newer managed disk technology with substantially better IOPS and throughput, closing much of the performance gap to Business Critical. When an exam scenario needs General Purpose economics with better storage performance and does not require a readable secondary, this tier is the intended answer.

Hardware Generations

Within a tier you select a hardware configuration . Standard-series (Gen5) is the baseline. Premium-series offers better compute and storage performance per vCore, and memory-optimized premium-series roughly doubles the memory-to-vCore ratio for buffer-hungry workloads that do not need more CPU. Hardware choice also affects maximum storage throughput and IOPS caps, so a scenario describing I/O-bound performance on undersized memory often resolves to switching to memory-optimized hardware rather than adding vCores.

Instance Pools

An instance pool is a pre-provisioned compute envelope inside a virtual network into which you deploy multiple managed instances that share the pool's total vCore allocation . Two facts are heavily tested. First, instance pools are the only way to get a 2-vCore managed instance - standalone instances start at 4 vCores. Second, pools are a consolidation play: they suit migrations of many small SQL Server instances (each with a handful of databases) that individually need instance features but collectively underutilize their hardware. All instances in a pool must reside in the same virtual network and subnet, all share the pool's service tier, and the combined compute of the hosted instances cannot exceed the pool size. Creating the pool itself is a long-running operation, but deploying additional instances into an existing pool is fast - which makes pools attractive when you need rapid instance provisioning. The tradeoff to cite on the exam: sharing compute reintroduces the noisy-neighbor risk that dedicated instances avoid, so pools fit spiky, non-overlapping workloads rather than uniformly busy ones.