AZ-104

Deep Dive

Back to guides
All guides
Practice now
D2 · Storage

Azure SQL Database and SQL Managed Instance

Azure's relational database services. The AZ-104 tests purchasing models, service tiers, high availability, security, and the differences between and SQL Managed Instance.

Contents

Icon-databases-130

SQL options in Azure

ServiceTypeSQL Server compatibilityManagementBest for
Azure SQL DatabasePaaSHigh (latest cloud-first features)Fully managed — OS, patches, automatic backupsNew cloud-native apps, modernization without legacy dependencies
SQL Managed InstancePaaSNearly 100% (full instance)Managed — but with more control than SQL DBLift-and-shift of on-prem SQL Server with minimal changes
SQL Server on VM (IaaS)IaaS100% (you control the version)You manage OS, patches, HA, backupsMaximum control, features not available in PaaS, specific compliance
Icon-databases-132

Azure SQL Database — key concepts

Main components

SQL Server (logical)

An administrative container with no resources of its own. Groups databases, and configures logins, firewall, and auditing. A server can have up to 5000 databases.

SQL Database

The individual database. Has its own resources (DTUs or vCores), backup configuration, and independent geo-replication.

Elastic Pool

A resource pool shared by multiple databases. Ideal when the DBs have usage peaks at different times.

Included PaaS features

Automatic backupsFull: weekly, Differential: every 12h, Log: every 5-12 min. 1-35 day retention.
Point-in-time restoreRecover to any point within the retention period (min. 5-min granularity).
Automatic patchingOS and SQL Server patched automatically with a configurable maintenance window.
Built-in HAAt least a 99.99% SLA with automatic standby replicas.
ScalingChange tier/vCores live (brief interruption on General Purpose, none on Business Critical).
AuditingLog all operations to a Storage Account, Log Analytics, or Event Hub.

Limitations vs full SQL Server

  • • No SQL Server Agent (use Elastic Jobs or Azure Automation)
  • • No access to the server's file system
  • • No traditional linked servers
  • • No unsafe CLR assemblies
  • • No cross-database Service Broker (SQL MI supports it within the instance)

Purchasing models and service tiers

DTU model (Database Transaction Unit)

Simplified model: a DTU is a blended measure of CPU, memory, and I/O. Easier to understand, less flexible.

Basic

5 DTUs

Dev/test, small apps with few transactions

Standard (S0-S12)

10-3000 DTUs

Production with predictable workload

Premium (P1-P15)

125-4000 DTUs

OLTP apps with high transaction density

vCore model (recommended for new deployments)

Choose vCores and memory independently. Lets you use Azure Hybrid Benefit (existing SQL Server licenses).

General Purpose

Remote storage (Azure Premium Storage). HA: standby replica. Ideal for 99% of production workloads.

Business Critical

Local SSD (NVMe). 3-4 synchronous replicas (Always On). 1 replica can be used for Read Scale-out or failover. Very low I/O latency.

Hyperscale

Up to 100 TB+. Distributed page architecture, instant backups, read scaling with extra replicas. SQL Database only (not MI).

Serverless (vCore): automatically scales compute between a minimum and maximum vCore count. Pauses automatically and bills only for storage while idle. Ideal for DBs with intermittent use.

Icon-databases-134

Elastic Pools — multiple databases sharing resources

An Elastic Pool is a set of resources (DTUs or vCores) shared across multiple databases. DBs draw the resources they need from the pool without exceeding the pool's maximum.

When to use Elastic Pools

  • ✓Multiple databases with usage peaks at different times (non-simultaneous peaks).
  • ✓Multi-tenant SaaS where each customer has its own DB — you isolate data but share resources.
  • ✓Forecasting resources for the pool is easier than for each individual DB.
  • ✓Centralized administration: patching, backup, monitoring for all DBs in the pool.
  • ✓Lower cost than allocating premium resources to each DB individually.

When NOT to use Elastic Pools

  • ✗Few DBs (fewer than 2-3) — not enough to amortize the overhead.
  • ✗All DBs peak at the same time — they compete for resources.
  • ✗A DB needs more resources than the pool can offer.
  • ✗Compliance requirements demanding total resource isolation between tenants.
Icon-databases-136

SQL Managed Instance (SQL MI)

SQL MI is an instance nearly identical to SQL Server, but delivered as a service. Designed for lift-and-shift of applications that need 100% SQL Server compatibility.

FeatureAzure SQL DatabaseSQL Managed Instance
SQL Server Agent✗ (Elastic Jobs)✓ Native
Linked Servers✗✓ (to other SQL Server instances)
Service Broker✗ cross-database✓ within the instance
CLR (Common Language Runtime)Safe assemblies only✓ including unsafe
Database Mail✗✓
Backup to URLManaged by Azure✓ you can run BACKUP TO URL manually
Instance-level collationFixed per DB✓ configurable when creating the instance
Deployment modelIndividual DB / Elastic PoolFull instance in a dedicated VNet
Network accessPublic endpoint or Private EndpointOnly within a VNet (default design)
Available vCores1-80 vCores4-80 vCores (General Purpose) / 4-32 (Business Critical)
Number of DBsOne per resourceUp to 100 per instance
Azure Hybrid Benefit✓✓

SQL MI — important for the exam

• SQL MI is deployed inside a dedicated — it requires a specific subnet delegated to Microsoft.Sql/managedInstances

• The subnet must have a minimum address space of /27

• Instance Pools: several small SQL MIs share a cluster (to reduce costs in dev/test)

• Provisioning a new instance can initially take 4-6 hours (an infrastructure operation)

Security: auditing, TDE, Advanced Threat Protection

Transparent Data Encryption (TDE)

Encrypts the DB, backups, and transaction logs at rest. Enabled by default on all new DBs since 2017.

  • →TDE with a Service-managed key: Microsoft manages the DEK (Database Encryption Key)
  • →TDE with a Customer-managed key (BYOK): the master key lives in , you control rotation and revocation
  • →BYOK enables "revoke and deny" — if the key is revoked in AKV, the DB becomes inaccessible

Azure SQL Auditing

Logs DB events: queries, logins, schema changes. Destination: Storage Account, Log Analytics, or Event Hub.

  • →A server-level audit policy applies to all DBs on the server
  • →A DB-level policy can extend (not replace) the server's policy
  • →Configurable retention on the Storage Account (0 = indefinite)
  • →Entra ID auditing: also logs Entra ID identity logins

Advanced Threat Protection (ATP)

Detects security anomalies: SQL injection, access from unusual locations, excessive data access, potentially compromised privileges.

  • →Part of Microsoft Defender for SQL
  • →Generates alerts in Microsoft Defender for Cloud (Azure Security Center)
  • →Does not replace auditing — they are complementary: ATP detects threats, auditing logs everything

Dynamic Data Masking

Masks sensitive data in query results for unprivileged users. Does not modify the stored data.

  • →Masking functions: Default (XXXX), Email (aXXX@XXXX.com), Random (for numbers), Custom String
  • →Excluded users (admins) see the real data
  • →Configured per column in the Azure Portal or T-SQL: ALTER TABLE ... ALTER COLUMN ... MASKED WITH

Authentication: SQL vs Entra ID

ModeHow it worksProsCons
SQL AuthenticationUsername/password managed in the DBSimple, works with any SQL clientManual credential management, no MFA
Entra ID AuthenticationOAuth token from Entra ID, can use MFA/Conditional AccessCentralized, supports groups and MFA, passwordless Managed IdentityRequires support in the app/driver
Mixed ModeBoth availableCompatible with legacy appsLarger attack surface

High availability and BCDR

Built-in HA by tier

General Purpose / Standard

Compute separated from storage (Azure Premium Storage). On failover, new compute is created that connects to the remote storage. RTO ~25-30 seconds. No active compute replica.

Business Critical / Premium

Always On Availability Group with 3-4 replicas on local SSD. One secondary replica available for Read Scale-out at no extra cost. Failover <8 seconds. Minimal data loss.

Hyperscale

Unique architecture: multiple read-only replicas accessing the same distributed storage. Instant backup (no full copy). Fast restore independent of size.

Active Geo-Replication

Creates up to 4 readable secondary replicas in different regions. Continuous asynchronous replication. Replicas are active read-only databases — you can redirect read queries to relieve the primary.

• Manual failover: you promote a secondary to primary whenever you decide

• Forced failover: possible with potential data loss (RPO ~5 sec)

• Available only on Azure SQL Database (not SQL MI — use Auto-failover groups)

Auto-Failover Groups

Groups multiple DBs for coordinated failover. Provides a listener endpoint that automatically redirects to the primary or secondary (read-write and read-only).

• Read-write listener: static DNS name — point your app here, it doesn't change on failover

• Read-only listener: redirects to the secondary for read-only queries

• Grace period: time before automatic failover (1h by default, configurable)

• Available on SQL DB and SQL MI

Networking — access and firewall

Public Endpoint + Firewall

The SQL server has a public endpoint. The firewall allows/blocks by IP or ranges. "Allow Azure services" opens access to all Azure services (not just yours — use with caution).

Private Endpoint

A private IP in your VNet for the SQL Server. Traffic never goes out to the internet. Requires a Private DNS Zone (privatelink.database.windows.net). The most secure option for production.

VNet Service Endpoint

Extends the subnet's identity toward the SQL service. Traffic travels over the Azure backbone. Configured in SQL Server Firewall → Virtual Networks. Less granular than Private Endpoint.

Migration — Azure Database Migration Service

Azure DMS (Database Migration Service)

A managed service for migrating on-premises or other-cloud databases to Azure with minimal downtime. Supports SQL Server, MySQL, PostgreSQL, Oracle, MongoDB.

Online (minimal downtime)

Continuous migration with change replication. Manual cutover when you are ready. Ideal for production.

Offline (snapshot)

Backup + restore + schema migration. Downtime during the window. Simpler, for non-critical DBs.

Data Migration Assistant (DMA)

A free tool that assesses the compatibility of on-premises SQL Server with Azure SQL Database or SQL MI. Identifies blocking issues and breaking changes before you migrate.

Recommended migration flow

1. Assess with DMA → identify incompatibilities

2. Choose destination: SQL DB (if compatibility is OK) or SQL MI (if you need full features)

3. Set up DMS → create a migration project

4. Migrate schema → migrate data

5. Validate in parallel → cut over during a maintenance window

Did you get this topic?

Test what you just learned

A company wants to migrate its on-premises application to Azure. The app uses SQL Server Agent Jobs, Service Broker, and CLR assemblies. Which Azure SQL service should they choose?

Ready to test "SQL Database and MI"?

Put what you learned to the test with original AZ-104 exam questions.

Start AZ-104 mock exam

Unlimited mock exams with the Pro plan

Log in to track your progress.

PreviousStorage AccountsStorageNextAzure Cosmos DBStorage