Azure SQL, Explained: Picking the Right Option and Running It Well

If you’re new to Azure SQL, it can feel like a lot: Single databases, elastic pools, serverless, Hyperscale, Managed Instance, and full SQL Server on Azure VMs. The good news is the family is all built on the SQL Server engine, so your SQL skills carry over. The trick is choosing the right shape for your workload and setting it up with a few guardrails. Microsoft Learn Microsoft Azure

This guide walks through each option with when-to-use, quick starts, and gotchas. At the end you’ll find migration paths and a short checklist for security, backups, and cost control.

The Azure SQL family at a glance

Azure SQL Database (single database): Fully managed database as a service. You manage a database, not a server. Great for new apps, SaaS tenants, and microservices.
Elastic Pools: A group of Azure SQL databases that share compute for bursty, multi-tenant workloads. One budget, many databases. Microsoft Learn
Serverless compute tier: Auto-scales compute and can auto-pause when idle. You pay per second of compute + storage. Best for spiky or dev/test databases. Microsoft Azure Microsoft Learn
Hyperscale: Azure SQL’s scale-out architecture with very fast backup/restore and huge database sizes (up to 128 TB for a single DB; 100 TB for Hyperscale elastic pools). Good for read-heavy or large OLTP/HTAP systems. Microsoft Learn+2Microsoft Learn+2
Azure SQL Managed Instance (MI): Near-full SQL Server compatibility (SQL Agent, cross-DB transactions, CLR, etc.) with a managed service. Deployed inside your VNet for network isolation. Ideal for “lift-and-shift” from on-prem without major changes. Microsoft Learn+1
SQL Server on Azure Virtual Machines (IaaS): You get the full OS and instance. Use when you need OS-level access, third-party agents, or features not available in PaaS. Microsoft Azure+1

Common building blocks you’ll use everywhere

Backups and PITR: Azure SQL takes automatic backups. In most tiers you can point-in-time restore within 7–35 days; you can also enable long-term retention (up to 10 years). MI has similar behavior. Learn restores before you need them. Microsoft Learn+2Microsoft Learn+2
Encryption at rest: Transparent Data Encryption (TDE) is on by default for new Azure SQL databases. You can bring your own key from Key Vault if you need customer-managed keys. Microsoft Learn+1
Resource limits: vCore sizing, IO, and memory caps vary by tier and generation—check limits before you pick sizes. Microsoft Learn

1) Azure SQL Database (single)

Good for: Apps with a clear DB-per-app or DB-per-tenant model. Simple operations, built-in HA.

Create a database (CLI)

# Variables
RG=prod-sql-rg
LOC=westus3
SERVER=prod-sql-srv-01
DB=appdb

# Create logical server
az sql server create -g $RG -n $SERVER -l $LOC \
  -u sqladmin -p 'S3cureP@ss!'

# Allow your client IP (adjust start/end)
az sql server firewall-rule create -g $RG -s $SERVER \
  -n allow-my-ip --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10

# Create a General Purpose vCore database
az sql db create -g $RG -s $SERVER -n $DB \
  --service-objective GP_Gen5_2

Day-one T-SQL checklist

-- Create a user for the app (after connecting as sqladmin)
CREATE USER app_login WITH PASSWORD = 'AnotherS3cureP@ss!';
EXEC sp_addrolemember 'db_datareader', 'app_login';
EXEC sp_addrolemember 'db_datawriter', 'app_login';

-- Optional: set a robust compatibility level for new features
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

When to move on: If your app has many small tenant databases or bursty load, consider elastic pools or serverless.

2) Elastic Pools

Why: Keep dozens or hundreds of small DBs fast without overpaying. All databases share a pool of compute inside one budget. Great for SaaS where each customer has a database. Microsoft Learn

Create a pool and add a database (CLI)

POOL=saas-pool

# Create the pool (vCore model)
az sql elastic-pool create -g $RG -s $SERVER -n $POOL \
  --edition GeneralPurpose --family Gen5 --capacity 8 \
  --db-min-capacity 0.25 --db-max-capacity 2

# Move an existing DB into the pool
az sql db update -g $RG -s $SERVER -n $DB --elastic-pool $POOL

Tuning tip: Watch the pool’s CPU and IO metrics. If a few tenants are noisy, cap them with per-DB limits or move out the heavy tenants.

3) Serverless compute (auto-scale and auto-pause)

Why: Spiky or infrequent workloads. Compute auto-scales between min and max vCores and can auto-pause when idle, billing per second of compute plus storage. Minimum auto-pause delay can be as low as 15 minutes. Microsoft Azure Microsoft Learn TECHCOMMUNITY.MICROSOFT.COM

Convert an existing DB to serverless (CLI)

az sql db update -g $RG -s $SERVER -n $DB \
  --edition GeneralPurpose --compute-model Serverless --family Gen5 \
  --min-capacity 0.5 --capacity 4 --auto-pause-delay 60

Cost guardrails

Disconnect idle tools like SSMS Object Explorer to allow auto-pause. Open connections can block it. Microsoft Learn
Keep min vCores low unless you truly need them.

4) Hyperscale (big, fast, flexible)

Why: Very large databases, fast restores, high log throughput, and read scale. Single DBs up to 128 TB; Hyperscale elastic pools up to 100 TB per database. Snapshots make backups/restores scale independent of size. Microsoft Learn+1

Move a database to Hyperscale (PowerShell)

Set-AzSqlDatabase -ResourceGroupName $RG -ServerName $SERVER -DatabaseName $DB `
  -Edition Hyperscale -ComputeModel Provisioned -VCore 8

Pattern to know: Put write traffic on the primary and offload reporting to read replicas. Hyperscale supports multiple read replicas and rapid scale-out. Microsoft Learn

5) Azure SQL Managed Instance (MI)

Why: You want “almost SQL Server” with PaaS benefits. MI gives you near 100% SQL Server compatibility (Agent, cross-DB transactions, Service Broker) and lands inside your VNet. That helps with private connectivity and lift-and-shift. Microsoft Learn

Notes before you pick MI

Provisioning takes longer than a single DB. Plan ahead for windowed rollouts.
Understand connectivity (private endpoint, routing, and DNS) so apps can reach it. Microsoft Learn

Create a Managed Instance (CLI sketch)

# MI creation typically includes vNet/subnet prep. This is a minimal sketch.
az sql mi create -g $RG -n prod-mi-01 -l $LOC \
  --subnet /subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Network/virtualNetworks/<vnet>/subnets/<subnet> \
  --storage 512GB --vcores 16 --tier GeneralPurpose --license-type BasePrice

When MI fits best

Existing apps that rely on SQL Agent jobs, cross-database queries, or CLR.
You want a managed service, but need more control and compatibility than Azure SQL Database.

6) SQL Server on Azure Virtual Machines

Why: You need OS-level control and full instance features, or you’re running legacy dependencies. It’s SQL Server running in an Azure VM you manage. You patch it, you tune it, and you can install whatever agents you need. Microsoft Azure

Quick start

Use Azure Marketplace images with SQL preinstalled.
Put data/log/tempdb on separate managed disks and enable Write Accelerator where supported.
Consider Azure Automanage for routine hygiene.

When to choose a VM: SSIS/SSRS on the same box, third-party drivers, or features not available in PaaS.

Migrations: getting to Azure the right way

There are three mainstream paths. Pick based on downtime tolerance and target:

Azure Database Migration Service (DMS): Microsoft’s recommended path for online migrations with minimal downtime. You’ll typically assess with Data Migration Assistant (DMA), fix issues, then run the move with DMS. It’s available in the Azure portal and as an Azure Data Studio extension. Microsoft Learn+2Microsoft Learn+2
SSMA (SQL Server Migration Assistant): If you’re coming from non-SQL Server engines (like PostgreSQL or Oracle) into Azure SQL or SQL Server, SSMA converts schema and moves data.
Backup/restore or export/import: For smaller databases or where downtime is fine, export to BACPAC, or use native backup/restore to MI and to SQL on VMs.

Small example: migrate SQL Server → Azure SQL Database (offline)

Run DMA assessment to catch compatibility issues and feature usage.
Fix blocking issues and set a target tier.
Use DMS offline migration or BACPAC export/import for a one-time cutover. Microsoft Learn

Operations: security, backups, cost, and performance

Security

TDE is on by default for new Azure SQL databases. Use customer-managed keys (BYOK) when required. Microsoft Learn+1
Lock down public access with firewall rules and private endpoints where possible.

Backups and restores

Know your PITR window (often 7–35 days) and enable Long-Term Retention for compliance archives (up to 10 years). Practice restores. Microsoft Learn+1

Cost guardrails

On serverless, keep min vCores low and set auto-pause to a realistic number. Remember the 15-minute minimum now available in many regions. Microsoft Learn TECHCOMMUNITY.MICROSOFT.COM
In elastic pools, watch the few tenants who hog the pool and consi

-- Find top queries by CPU in the last 24 hours (Query Store must be on)
SELECT TOP 20
    qsq.query_id,
    SUM(rs.cpu_time) AS total_cpu_ms,
    COUNT(*) AS plan_execs
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_plan AS qsp ON rs.plan_id = qsp.plan_id
JOIN sys.query_store_query AS qsq ON qsp.query_id = qsq.query_id
WHERE rs.last_execution_time >= DATEADD(day, -1, SYSUTCDATETIME())
GROUP BY qsq.query_id
ORDER BY total_cpu_ms DESC;

Quick “how-to” snippets

Flip a single database to Serverless with auto-pause

az sql db update -g $RG -s $SERVER -n $DB \
  --edition GeneralPurpose --compute-model Serverless --family Gen5 \
  --min-capacity 0.5 --capacity 4 --auto-pause-delay 30

Microsoft Learn

Create a Hyperscale database

az sql db create -g $RG -s $SERVER -n bigdb \
  --edition Hyperscale --family Gen5 --capacity 8

Microsoft Learn

Put several tenant DBs into an elastic pool

az sql elastic-pool create -g $RG -s $SERVER -n tenants-pool \
  --edition GeneralPurpose --family Gen5 --capacity 16 --db-min-capacity 0.25 --db-max-capacity 2

for DBNAME in tenant1 tenant2 tenant3; do
  az sql db create -g $RG -s $SERVER -n $DBNAME --elastic-pool tenants-pool
done

How to choose, in one minute

Net-new app, single DB: Azure SQL Database.
Many small DBs that spike at different times: Elastic Pools. Microsoft Learn
Spiky dev/test or unpredictable compute: Serverless. Microsoft Azure
Very large DB, fast restore, read scale: Hyperscale. Microsoft Learn
Minimal code change from on-prem SQL: Managed Instance. Microsoft Learn
Full control and legacy dependencies: SQL Server on Azure VM. Microsoft Azure

References and further reading

Azure SQL family overview and products. Microsoft Learn Microsoft Azure
Elastic pools. Microsoft Learn
Serverless compute (overview, per-second billing, auto-pause). Microsoft Azure Microsoft Learn
Hyperscale (architecture, limits, elastic pools). Microsoft Learn+2Microsoft Learn+2
Managed Instance overview and connectivity. Microsoft Learn+1
Resource limits for single databases (vCore). Microsoft Learn
Automatic backups and PITR; Long-Term Retention. Microsoft Learn+1
TDE defaults and customer-managed keys. Microsoft Learn

Discover more from SQLYARD

Subscribe to get the latest posts sent to your email.

Azure SQL, Explained: Picking the Right Option and Running It Well

The Azure SQL family at a glance

Common building blocks you’ll use everywhere

1) Azure SQL Database (single)

Create a database (CLI)

2) Elastic Pools

Create a pool and add a database (CLI)

3) Serverless compute (auto-scale and auto-pause)

Convert an existing DB to serverless (CLI)

4) Hyperscale (big, fast, flexible)

Move a database to Hyperscale (PowerShell)

5) Azure SQL Managed Instance (MI)

Create a Managed Instance (CLI sketch)

6) SQL Server on Azure Virtual Machines

Migrations: getting to Azure the right way

Operations: security, backups, cost, and performance

Quick “how-to” snippets

How to choose, in one minute

References and further reading

Like this:

Related

Discover more from SQLYARD

Leave a ReplyCancel reply

Sign up to receive email updates, fresh news and more!

The Azure SQL family at a glance

Common building blocks you’ll use everywhere

1) Azure SQL Database (single)

Create a database (CLI)

2) Elastic Pools

Create a pool and add a database (CLI)

3) Serverless compute (auto-scale and auto-pause)

Convert an existing DB to serverless (CLI)

4) Hyperscale (big, fast, flexible)

Move a database to Hyperscale (PowerShell)

5) Azure SQL Managed Instance (MI)

Create a Managed Instance (CLI sketch)

6) SQL Server on Azure Virtual Machines

Migrations: getting to Azure the right way

Operations: security, backups, cost, and performance

Quick “how-to” snippets

How to choose, in one minute

References and further reading

Share this:

Like this:

Related

Discover more from SQLYARD

Related Posts

Leave a ReplyCancel reply

Discover more from SQLYARD