Topic 4 of 5
The Azure SQL family
Azure offers three ways to run the SQL Server engine in the cloud, from a virtual machine you manage to a fully managed database. The typical exam question describes a migration or a new application and asks you to pick the right service.
Contents
The Azure SQL family
Azure SQL is the collective name for a family of database services based on Microsoft SQL Server. Most Azure database services are managed: they include high availability, scaling, monitoring, threat detection, and automatic performance tuning.
SQL Server on a VM
IaaS: a virtual machine with SQL Server installed.
SQL Managed Instance
PaaS with near-100% compatibility with SQL Server.
Azure SQL Database
Fully managed, highly scalable PaaS designed for the cloud.
Comparing the three services
| SQL Server on a VM | SQL Managed Instance | SQL Database | |
|---|---|---|---|
| Type | IaaS | PaaS | PaaS |
| Compatibility | Full: migration without changes | Near 100%, minimal changes | Core database-level capabilities; some features may be missing |
| Architecture | Instances on the VM, several databases per instance | Several databases per instance; instance pools | Single database on a logical server, or elastic pool |
| Availability | VM SLA, not the SQL Server processes | Enterprise SLA, higher with zone redundancy | Enterprise SLA, higher with zone redundancy |
| Management | You: OS, updates, backups… | Automated updates, backups, and recovery | Automated updates, backups, and recovery |
| Use case | Migrate or extend while keeping full control | Most migrations with minimal changes | New solutions or apps with few instance dependencies |
SQL Server on virtual machines
It lets you use full versions of SQL Server in the cloud without managing on-premises hardware. It effectively replicates the database running on an on-premises server: migrating is like moving the databases from one server to another.
- Ideal for lift and shift of applications that need to migrate quickly with minimal or no changes.
- For apps that need operating system features unavailable at the PaaS level.
- For hybrid deployments: part of the system on-premises and part in the cloud.
- For development and test environments without buying on-premises hardware.
- It scales by allocating more memory, CPU, and disk to the VM, without reinstalling software.
You have full administrative rights over the DBMS and the operating system, but that doesn't remove the need to administer it as carefully as on-premises.
Azure SQL Managed Instance
It runs an instance of SQL Server in the cloud. You can create multiple databases on the same instance and you have instance-level control, as on-premises. The service automates backups, patching, monitoring, and other tasks, while you control security and resource allocation.
- Near-100% compatibility with SQL Server Enterprise Edition, higher than Azure SQL Database.
- A native virtual network (VNet) implementation.
- Supports instance features such as cross-database queries and transactions and Database Mail.
- SQL Server logins (username and password) and Microsoft Entra ID logins, with single sign-on.
- All communications are encrypted and signed with certificates.
When to choose it
To move an on-premises SQL Server instance with all its databases without the overhead of managing a VM. Compatibility can be checked with an Azure SQL assessment through Azure Migrate or SQL Server enabled by Azure Arc.
Azure SQL Database
A PaaS offering: you create a logical server in the cloud and deploy your databases on it. That server is a central administrative point for single or pooled databases, logins, firewall rules, auditing rules, threat detection policies, and failover groups.
- It isn't fully compatible with on-premises SQL Server; it's used mostly in new cloud projects.
- Typical uses: modern apps that use the latest SQL Server features, apps that need high availability, and systems with variable load that must scale quickly.
- It updates and patches the software automatically.
- It has a free tier with monthly limits of 100,000 vCore seconds of serverless compute and 32 GB of storage per database.
Single database, elastic pool, and Hyperscale
Single database
One database with its own resources, preallocated and billed per hour. It also supports the serverless tier: compute scales within a range and, in General Purpose, it pauses itself when idle.
Elastic pool
Several databases share the memory, storage, and CPU of a pool only your databases use. Useful when their needs vary over time, such as payroll at month-end and reports mid-month.
Hyperscale
A tier for very large databases, up to 128 TB, with rapid on-demand scaling and fast backup and restore regardless of size.
Purchasing models and service tiers
| vCore (recommended) | DTU | |
|---|---|---|
| What you choose | Compute and storage independently | Bundles of compute, storage, and I/O |
| Tiers | General Purpose, Business Critical, Hyperscale | Basic, Standard, Premium |
| Extras | Provisioned or serverless compute; Azure Hybrid Benefit | Simple, fixed payments |
| For whom | Those who value flexibility, control, and transparency | Those who want simple, preconfigured options |
General Purpose
The default tier for most workloads. It separates compute and storage (on Blob Storage), with 5-10 ms storage latency.
Business Critical
Local SSD (1-2 ms), three extra replicas for high availability, and a free readable secondary replica for reports.
Hyperscale
Independently scalable compute and storage; you choose 0 to 4 high availability replicas.
Availability and security
Point-in-time restore
Automatic full, differential, and transaction log backups to recover the database to any point in the past.
Geo-replication
Databases can be replicated to other regions for resiliency and disaster recovery.
Microsoft Defender for SQL
Vulnerability assessment and Advanced Threat Protection, which detects anomalous access, SQL injection, and suspicious patterns.
Auditing and encryption
Auditing writes events to a destination you choose; data is encrypted at rest and in motion.
Microsoft Copilot in Azure with Azure SQL Database (preview) answers natural language questions about the database in the portal, such as troubleshooting performance.
Exam scenarios
| Situation | Answer | Why |
|---|---|---|
| Migrate SQL Server as-is and keep full control of the operating system | SQL Server on an Azure VM (IaaS) | Lift and shift without changes, all under your management. |
| Migrate an instance with many databases and advanced features with minimal changes | Azure SQL Managed Instance | Near-total compatibility with SQL Server. |
| Build a new cloud-native application | Azure SQL Database | Fully managed PaaS. |
| Several databases with usage peaks at different times of the month | Elastic pool | They share resources and reduce cost. |
| An 80 TB database that must scale quickly | Hyperscale | Up to 128 TB with rapid scaling. |
| A rarely used database that should pause itself when idle | Serverless compute tier | It scales automatically and can pause. |
| Read-only reports without affecting the primary replica | Business Critical | A free-of-charge readable secondary replica. |
| Check whether on-premises databases can migrate to Azure SQL | Assessment with Azure Migrate or SQL Server enabled by Azure Arc | It reports migration readiness. |
Common exam traps
❓ Is SQL Server on an Azure VM PaaS?
No. It's IaaS: you manage the operating system, updates, and backups.
❓ Which service is more compatible with on-premises SQL Server, SQL Database or Managed Instance?
SQL Managed Instance (near 100%). SQL Database may lack some features.
❓ Does the SQL Server on a VM SLA cover the SQL Server processes?
No. It covers the virtual machines, not the processes running on them.
❓ What is the maximum size of a Hyperscale database?
Up to 128 TB, according to the official training.
❓ Does an elastic pool share resources with other customers' databases?
No. The databases in your pool share resources with each other; only your databases use it.
❓ Which purchasing model does Microsoft recommend?
vCore, which separates compute and storage. DTU bundles both together.
❓ Which tier offers a free readable replica for reports?
Business Critical.
Did you get this topic?
Test what you just learned
A company wants to migrate to Azure a SQL Server instance with 15 databases that use cross-database queries, with minimal changes and without managing the operating system. Which service does it choose?
Ready to test "The Azure SQL family"?
Original questions, each with a full explanation.
Start a mock exam