Azure SQL Family: SQL Database, Managed Instance, SQL on VMs, Purchasing Models, Elastic Pools and Hyperscale
Three Ways to Run SQL Server
| Option | Type | Best for |
|---|---|---|
| Azure SQL Database | PaaS, single database or elastic pool | New cloud apps, SaaS, least management |
| SQL Managed Instance | PaaS, near 100% SQL Server compatibility | Lift and shift of apps needing SQL Agent, cross-database queries, linked servers, CLR |
| SQL Server on Azure VM | IaaS | Full OS or version control, unsupported features, custom setups |
Exam tip Less management and more compatibility are a trade-off. Prefer PaaS unless a requirement forces a VM.
Purchasing Models
| Model | How it works |
|---|---|
| DTU | Bundled compute, memory and IO units in Basic, Standard and Premium tiers. Simple |
| vCore | Choose vCores, memory and storage separately. Supports Azure Hybrid Benefit, reserved capacity and serverless. Recommended |
Service Tiers (vCore)
| Tier | Architecture | Use |
|---|---|---|
| General Purpose | Remote storage, compute and storage separated | Most business workloads. Zone redundancy optional |
| Business Critical | Local SSD, Always On style replicas, readable secondary | Low latency, high IO, fast failover |
| Hyperscale | Distributed page servers, up to about 100 TB, fast backup and scale, named replicas | Very large or fast-growing databases |
- Serverless (General Purpose and Hyperscale): auto-scales and auto-pauses after idle time. You pay per second. Good for dev/test and intermittent workloads
- Provisioned: fixed compute for steady loads
bash
az sql server create -g rg-data -n sql-shop-482 -l centralindia -u sqladmin -p '<StrongPassword>'
az sql db create -g rg-data -s sql-shop-482 -n shopdb --edition GeneralPurpose --family Gen5 --compute-model Serverless \
--capacity 4 --min-capacity 0.5 --auto-pause-delay 60 --zone-redundant false
az sql server firewall-rule create -g rg-data -s sql-shop-482 -n office --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10
Elastic Pools
- Many databases share a pool of compute. Good when each database has different peak times (SaaS with many tenants)
- Cheaper than sizing every database for its own peak
bash
az sql elastic-pool create -g rg-data -s sql-shop-482 -n pool1 --edition GeneralPurpose --family Gen5 --capacity 4
az sql db update -g rg-data -s sql-shop-482 -n shopdb --elastic-pool pool1
High Availability and Disaster Recovery
| Feature | Purpose |
|---|---|
| Built-in HA | Automatic, no config. Zone-redundant option for zone failures |
| Automated backups | Full, differential and log. Point-in-time restore, 7 to 35 days. Long-term retention up to 10 years |
| Active geo-replication | Readable secondary databases in other regions (up to 4) |
| Auto-failover groups | Group of databases with read-write and read-only listener endpoints that do not change at failover |
bash
az sql failover-group create -g rg-data -s sql-shop-482 -n fog-shop --partner-server sql-shop-482-dr --partner-resource-group rg-dr \
--add-db shopdb --failover-policy Automatic --grace-period 1
Applications connect to fog-shop.database.windows.net and do not need a new connection string after failover.
Security Features
- Entra ID authentication and Entra-only mode, managed identities for apps
- Transparent Data Encryption (TDE) on by default. Always Encrypted for column protection from admins
- Dynamic data masking, row-level security, auditing, Defender for SQL
- Private endpoint or VNet rules and disable public access
Choosing Quickly
| Scenario | Choice |
|---|---|
| New SaaS app, many small tenants | SQL Database elastic pool |
| Occasional dev/test database | SQL Database serverless |
| 10 TB database with fast restore needs | Hyperscale |
| Legacy app using SQL Agent and linked servers | Managed Instance |
| Needs OS access or old SQL version | SQL Server on VM |
| Cross-region failover with stable endpoint | Auto-failover group |
Practice
- Create a serverless database with auto-pause
- Restore it to a point in time into a new database
- Set up a failover group between two regions
- Pick a service for three given scenarios
Conclusion
Use SQL Database for most new apps (vCore, serverless for spiky loads, elastic pools for many tenants, Hyperscale for huge data), Managed Instance for compatibility and a VM only when needed. Add failover groups for regional DR.
Quick Summary: family; purchasing; tiers; pools; HA and DR; security; choosing.

