cert-bank

Guides

이 글을 한국어로 읽기

Azure SQL Database vs SQL Managed Instance vs SQL Server on Azure VMs: The Feature Clues That Decide

Azure offers three ways to run SQL Server workloads: Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure Virtual Machines. All three run the same engine family, so exam options that name them all look plausible. They differ in one thing, how much of SQL Server you manage yourself, and the stem nearly always names a feature that settles it. This comparison shows that ladder, the clues for each step, and the case where the right answer is not SQL at all.

Concept comparison · AZ-305, DP-900 and other Azure exams

Azure SQL Database vs SQL Managed Instance vs SQL Server on Azure VMs: The Feature Clues That Decide

One question: what does the workload need to control?

Think of a ladder with three steps. At the top, Azure SQL Database is platform as a service at the database level: Microsoft runs the engine, patching, backups and high availability, and you manage databases. In the middle, Azure SQL Managed Instance is platform as a service at the instance level: Microsoft still runs the platform, but you get a whole SQL Server instance with near-complete compatibility. At the bottom, SQL Server on Azure VMs is infrastructure as a service: SQL Server installed on a Windows or Linux VM, with full control of the engine and the operating system. Each step down gives more control and more work. The exam rewards the highest step that still meets every requirement.

In this question bank, 32 Azure questions name Azure SQL Managed Instance or SQL Server on Azure VMs in the stem or the options (18 in DP-900, 10 in AZ-305, 2 each in AZ-104 and AZ-900), and the ones that ask you to choose between the options nearly always name a specific feature rather than asking in general terms.

Side by side

SQL DatabaseSQL Managed InstanceSQL Server on VMs
ModelPaaS, database scopePaaS, instance scopeIaaS
Instance features (SQL Server Agent, cross-database queries, CLR, linked servers)No; elastic jobs replace AgentYes, plus Service Broker, distributed transactions and native restore of .bak files from Blob StorageYes, everything
Operating system accessNoneNoneFull: install agents, FILESTREAM, other software on the same server
Engine versionAlways the latest stable engine (evergreen)Updated by Microsoft; evergreen like SQL Database with the always-up-to-date update policyAny supported version and edition you install; you patch it
High availabilityBuilt inBuilt inYours to design (for example an Always On availability group)
NetworkingLogical server endpoint; private endpoint optionalDeployed into a dedicated subnet of your virtual network, with private IP addressesA VM in your virtual network
Cost optionsDTU or vCore; serverless; elastic pools; HyperscalevCore onlyVM pricing, with a licence included in the image or your own

Azure Hybrid Benefit, which lets you reuse SQL Server licences you already own, is available for all three. A distractor that says only one of them supports it is wrong.

The feature clues

  • Stay at SQL Database when the stem describes a new cloud app, one database that is used only now and then (serverless), many databases with peaks at different times (an elastic pool), or a single database growing to around 100 TB with fast snapshot backups (Hyperscale). None of these exist on a managed instance.
  • Step down to Managed Instance when the stem lists SQL Server Agent jobs, cross-database queries with three-part names, CLR assemblies, Database Mail, Service Broker, linked servers or MS DTC, and also says "no operating system to patch" or "a PaaS service".
  • Step down to a VM when the stem needs something only the server itself provides: FILESTREAM filegroups, a vendor agent installed on the database server's operating system, other software on the same server, a specific older SQL Server version, or control over when updates are installed.

Beginner tip: for every feature named in a stem, ask one question out loud: "does this live in a database, in the instance, or on the server's operating system?" Write D, I or S next to it. The lowest letter you write decides the service: only D means SQL Database, any I means Managed Instance, any S means a VM. Try it on the list above before you open a practice set, and the two-answer questions ("which TWO requirements can only a VM meet?") become quick.

Which way to run the SQL workload does the question want?
Global users, JSON whose shape changes often, writes in several regions?
YES →
Azure Cosmos DB, not SQL Server
↓ no
Needs FILESTREAM, an agent on the server's OS, a specific older version or update control?
YES →
SQL Server on Azure VMs
↓ no
Needs SQL Server Agent, cross-database queries, CLR or linked servers, with no OS to patch?
YES →
Azure SQL Managed Instance
↓ no
OTHERWISE →
Azure SQL Database: serverless, elastic pools or Hyperscale as the stem asks
Go down the ladder only as far as a named feature forces you; the first yes decides.

Who keeps it running

With the two PaaS options, high availability of the database is part of the service. On a VM it is not, and this produces a classic troubleshooting question.

Going deeper: the VM stayed up, but the database went down

On IaaS, Microsoft's SLA covers the virtual machine, not the SQL Server processes running on it. If the SQL Server service crashes while the VM keeps running, the platform sees a healthy VM and does nothing. Database availability is therefore the customer's design: SQL Server's own high-availability features across two or more VMs, in an availability set or across availability zones. For zone failures with automatic, no-data-loss failover, the answer is an Always On availability group with synchronous-commit replicas in different zones. Azure Site Recovery to another zone is a disaster-recovery tool with a non-zero RPO and is not a replacement. The SQL IaaS Agent extension helps with backups and patching, but it does not make the database highly available.

On the PaaS side, Managed Instance can be made zone redundant and placed in a failover group with a secondary instance in another region; the failover group's listeners keep the application's connection string unchanged after a regional failover.

Getting there

Before migrating, find out which features the databases use: an Azure SQL assessment with Azure Migrate (or SQL Server enabled by Azure Arc) reports what SQL Database or Managed Instance would not support. For a move to Managed Instance with only minutes of downtime, this question bank's questions point to Azure Database Migration Service in online mode or Log Replay Service in continuous mode. When the business wants a readable copy in Azure for months before cutting over, the Managed Instance link replicates through a distributed availability group. A BACPAC export after stopping the app works, but it is not a minimal-downtime method.

Going deeper: "we need older behaviour" is not "we need an older version"

Azure SQL Database is evergreen: Azure updates the engine for you, so there is no end-of-support upgrade project, and you cannot choose an older engine. If an application depends on the behaviour of an older release, you set the database compatibility level, which keeps query processing behaving like that release on the current engine. Only on a VM can you install and stay on a specific SQL Server version, at the price of planning upgrades yourself. So a stem that asks for "the latest features with no upgrade work" points up the ladder, and one that requires "SQL Server 2019 exactly, updated on our schedule" points down.

When the answer is not SQL Server at all

Some questions on the same topic have Azure Cosmos DB as the answer, and the clue is the shape of the data and where the users are. Shoppers on four continents, JSON whose shape changes often, fast reads and writes from a nearby region: Cosmos DB with several regions and multi-region writes, not a single Azure SQL database in one region. An existing Cassandra application that must keep its drivers: Azure Cosmos DB for Apache Cassandra. Table storage data that now needs global replication and indexes on every property: Azure Cosmos DB for Table. Going the other way, a fixed relational schema with joins and transactions stays in Azure SQL. And Azure Cosmos DB for PostgreSQL is on a retirement path, so it is not the answer for a new distributed relational app.

Practise

Practise in the question bank: every question explains each option, right and wrong. Start with the domain you just studied, then take a timed mock exam.

Based on Microsoft Learn's comparison of the Azure SQL options and on the questions in this question bank. Feature support changes; check the current feature comparison on Microsoft Learn.