Choosing the right Azure SQL option
The first design decision for any Azure data platform project is which SQL service to use. Most exam scenarios hide the answer in one feature the application needs.
The three options
| Option | Type | Choose it when |
|---|---|---|
| Azure SQL Database | PaaS, per database | New cloud apps, elastic scale, least administration |
| Azure SQL Managed Instance | PaaS, per instance | Lift and shift needing instance features with high compatibility |
| SQL Server on Azure VMs | IaaS | You need OS access or features PaaS does not offer |
Managed Instance supports SQL Server Agent, cross-database queries, linked servers, Database Mail and CLR. Azure SQL Database does not have SQL Server Agent. Use SQL Server on Azure VMs for FILESTREAM, FileTable, merge replication, SSRS or SSIS on the same server, or a specific SQL Server version.
Purchasing models and tiers
Azure SQL Database uses the vCore model (recommended) or the DTU model (a bundled measure of CPU, memory and I/O). Service tiers in the vCore model:
- General Purpose: remote storage, budget-friendly, the default for most workloads.
- Business Critical: local SSD, built-in readable secondary replica, lowest latency, supports zone redundancy with no data loss.
- Hyperscale: very large databases (up to 100 TB), fast scale and fast restore, read scale-out with named replicas.
In the DTU model the equivalents are Basic, Standard and Premium.
Compute options
- Provisioned: fixed vCores, billed continuously.
- Serverless (General Purpose, and Hyperscale): scales between min and max vCores, and can auto-pause after inactivity. Good for intermittent use.
- Elastic pool: many databases share one pool of resources. Best when peaks do not coincide.
Scaling a single database up or down is an online operation with only a short connection drop at the end.
Deployment tools
Resources can be deployed with the Azure portal, ARM or Bicep templates, Azure CLI or PowerShell (New-AzSqlDatabase). The logical server holds firewall rules, the Microsoft Entra admin and auditing settings that apply to all of its databases.
Exam traps
- Auto-pause exists only in the serverless compute tier, not in an elastic pool or Business Critical.
- Azure SQL Database has no SQL Server Agent. Managed Instance has it.
- FILESTREAM, FileTable and merge replication point to SQL Server on Azure VMs.
- Hyperscale is for size and fast restore, not for the lowest cost.
- An elastic pool saves money only when databases peak at different times.
Practice questions
Choose an answer, then review the explanation to check your understanding.
1. An on-premises database uses FILESTREAM and FileTables. You want to move it to Azure. Which option supports it?
Show answer
Answer: C.FILESTREAM and FileTable need full control of the operating system, so only SQL Server on Azure VMs supports them.
2. A new database has an unpredictable, intermittent workload. CPU must scale automatically and you want to stop paying for compute when it is idle. What do you choose?
Show answer
Answer: A.The serverless tier scales vCores between a minimum and a maximum and can auto-pause, so you pay only for storage while paused.
3. Twenty databases from one application peak at different times. You want them to share a fixed set of resources to reduce cost. What do you use?
Show answer
Answer: B.An elastic pool lets databases with varying, non-overlapping peaks share eDTUs or vCores.
4. A legacy app uses cross-database queries, SQL Server Agent and linked servers, and you want minimal code changes with a PaaS service. Which service fits?
Show answer
Answer: B.Azure SQL Managed Instance offers near 100% compatibility with the SQL Server engine, including SQL Server Agent and cross-database queries.