This chapter covers Azure SQL Database, a fully managed Platform-as-a-Service (PaaS) relational database service in Azure. For the AZ-900 exam, understanding the differences between PaaS and IaaS database options, the purchasing models (DTU vs. vCore), and key features like elastic pools and serverless is critical. This objective area (Describe Azure architecture and services) carries approximately 30-35% of the exam weight, and database services frequently appear in scenario-based questions. By the end of this chapter, you will be able to explain what Azure SQL Database is, how it differs from SQL Server on a VM, and which deployment options fit common business needs.
Jump to a section
Imagine you want to live in a high-rise apartment. In the on-premises world, you would have to buy a plot of land, pour concrete, run plumbing and electricity, and build the entire building yourself — that's like installing SQL Server on your own servers. With Azure SQL Database, you instead buy a condo unit in a managed high-rise. The building owner (Microsoft) handles all the structural maintenance: roof repairs, elevator inspections, plumbing backups, and security guards. You just bring your furniture (your data schema) and decide on the floor plan (performance tier). If you need more space, you call the management and they expand your unit in minutes — no construction crew. The condo association (Azure platform) automatically updates the building code (patching SQL Server), monitors for fire hazards (threat detection), and makes backups of your unit's contents (automated backups). You never worry about the foundation collapsing or the boiler breaking — you just live and work. This is PaaS (Platform as a Service): you manage only your data and queries; Microsoft manages the OS, hardware, and SQL engine updates.
What is Azure SQL Database and What Business Problem Does It Solve?
Azure SQL Database is a fully managed relational database service built on the latest stable version of Microsoft SQL Server. It is a PaaS offering, meaning Microsoft handles all infrastructure management: hardware provisioning, OS patching, SQL engine updates, backups, replication, and high availability. The business problem it solves is the high cost and complexity of running your own SQL Server instance on-premises or on a virtual machine (IaaS). Organizations no longer need to hire DBAs to maintain servers, worry about storage capacity planning, or deal with disaster recovery setup. Azure SQL Database provides built-in intelligence that automatically tunes performance, applies security patches, and offers 99.99% availability SLA (for certain tiers).
How It Works – Step by Step
When you provision an Azure SQL Database, you are essentially creating a logical server (a management container) and one or more databases within it. The logical server sits on top of a physical cluster managed by Microsoft. Here is the mechanism:
Provisioning: You choose a resource group, region, server name, admin credentials, and database name. Azure allocates compute and storage resources from its global fleet. Behind the scenes, the SQL engine is deployed on a virtual machine, but you never see or manage that VM.
Connectivity: Your applications connect using standard SQL Server connection strings. The database appears as a regular SQL Server database, so existing client libraries (ADO.NET, JDBC, ODBC) work without modification.
Storage: Data is stored on premium managed disks (SSD-backed) with local redundancy. By default, Azure automatically creates three replicas of the data within the same region to ensure high availability. The service handles replication transparently.
Backups: Azure SQL Database automatically performs full backups weekly, differential backups every 12 hours, and transaction log backups every 5-10 minutes. These backups are stored in geo-redundant storage (RA-GRS) by default, providing protection against regional disasters.
Patching: Microsoft applies updates to the underlying OS and SQL engine during maintenance windows. You can configure a maintenance window to minimize impact.
Key Components and Deployment Options
Azure SQL Database offers several deployment options and purchasing models:
Single Database: A single database with its own dedicated resources. Best for applications that need isolated performance and predictable workload.
Elastic Pool: A collection of databases sharing a pool of resources (eDTUs or vCores). Ideal for SaaS applications with many databases that have unpredictable usage spikes. You pay for the pool, and databases automatically consume resources as needed.
Managed Instance: A deployment option that provides near 100% compatibility with on-premises SQL Server, including features like SQL Agent, cross-database queries, and linked servers. It runs in its own VNet and is suitable for lift-and-shift migrations.
Serverless: A compute tier for single databases that auto-pauses when idle (after 1 hour of inactivity) and auto-resumes on first connection. You pay only for storage and compute used per second. Great for intermittent workloads.
#### Purchasing Models: DTU vs. vCore
DTU (Database Transaction Unit): A bundled measure of compute, storage, and IO. Tiers: Basic (5 DTUs), Standard (10-3000 DTUs), Premium (125-4000 DTUs). Simple but less flexible. The Basic tier is often used for small workloads and provides 2 GB storage.
vCore: You choose the number of virtual cores, memory, and storage separately. Offers more granular control and is required for some features like Azure Hybrid Benefit (using existing SQL Server licenses). Tiers: General Purpose (balanced), Business Critical (low latency, high availability), Hyperscale (up to 100 TB storage, fast scaling).
Comparison to On-Premises SQL Server
| Feature | On-Premises SQL Server | Azure SQL Database (PaaS) | |---------|------------------------|---------------------------| | Hardware management | You buy, install, maintain | Microsoft manages | | OS/DB patching | You schedule and apply | Automatic (configurable window) | | High Availability | You configure (Always On, clustering) | Built-in (99.99% SLA) | | Backup management | You create and store | Automatic, point-in-time restore | | Scalability | Add hardware (slow, costly) | Scale up/down instantly | | Licensing | Full license cost | Included in hourly rate (or BYOL) |
Azure Portal and CLI Touchpoints
In the Azure portal, you can create an Azure SQL Database by navigating to "Azure SQL" and selecting "Create". You choose the resource group, database name, server, compute + storage tier, and backup redundancy. The portal also provides monitoring through SQL Insights, query performance insight, and automatic tuning recommendations.
Using Azure CLI, you can provision a database with a single command:
az sql db create --resource-group myRG --server myServer --name myDB --service-objective S0To create a server:
az sql server create --resource-group myRG --name myServer --admin-user myAdmin --admin-password myPassword --location eastusAzure PowerShell equivalents exist. These tools allow automation of database creation, scaling, and backup management.
Concrete Business Scenarios
E-commerce website: A company uses a Standard tier single database (S2, 50 DTUs) for its product catalog. During Black Friday, they temporarily scale up to S4 (200 DTUs) in minutes via the portal. After the sale, they scale back down. They pay only for the higher tier during the peak hours.
SaaS Multi-Tenant App: A software vendor hosts 100 customer databases. Each customer has light usage, but some customers have occasional bursts. They deploy an elastic pool with 100 eDTUs. All 100 databases share the pool. When one customer runs a heavy report, they use more eDTUs temporarily; other customers' performance is minimally affected. The vendor pays a predictable monthly cost.
Lift-and-Shift Migration: A company wants to move its on-premises SQL Server database to Azure with minimal changes. They choose Azure SQL Managed Instance because it supports SQL Agent jobs and cross-database queries. They use the Azure Database Migration Service to migrate with near-zero downtime.
1. Create a Logical Server
First, you need a logical server that acts as a container for your databases. In the Azure portal, search for 'SQL servers' and click 'Create'. Fill in the server name (globally unique), admin login and password, and choose a region. The server itself has no cost; you only pay for databases. The server provides a DNS name (e.g., myserver.database.windows.net) and firewall rules to control access. Behind the scenes, Azure creates a virtual network endpoint but does not expose the underlying infrastructure.
2. Choose Deployment Option and Purchasing Model
After creating the server (or during creation), you choose the deployment option: Single Database, Elastic Pool, or Managed Instance. For single database, you also pick the purchasing model (DTU or vCore). The DTU model is simpler and recommended for beginners. You then select a service tier: Basic (5 DTUs, 2GB), Standard (10-3000 DTUs, up to 1TB), or Premium (125-4000 DTUs, up to 4TB). Each tier has different SLAs and features. For example, Basic has no point-in-time restore, while Standard and Premium do.
3. Configure Compute, Storage, and Backup
In the vCore model, you specify the number of virtual cores (2 to 80) and the amount of storage (up to 4TB for General Purpose, up to 4TB for Business Critical, and up to 100TB for Hyperscale). You also choose backup storage redundancy: Locally-redundant (LRS), Zone-redundant (ZRS), or Geo-redundant (GRS). GRS is default and provides protection against region-wide failures. The backup retention period is 7 days by default (configurable up to 35 days). Longer retention requires long-term retention (LTR) policies.
4. Set Firewall Rules and Connectivity
By default, no external connections are allowed. You must add firewall rules to allow your client IP address or an entire Azure service. In the portal, go to 'Set server firewall' and add a rule with a name, start IP, and end IP. For applications running in Azure (e.g., App Service), you can enable 'Allow Azure services and resources to access this server'. This adds a special firewall rule that allows connections from other Azure services. Note: This is a security risk if not needed.
5. Connect and Manage the Database
Once the database is provisioned, you can connect using SQL Server Management Studio (SSMS), Azure Data Studio, or any application with the connection string. The connection string is available in the portal under 'Connection strings'. You must replace 'your_username' and 'your_password'. For example: 'Server=tcp:myserver.database.windows.net,1433;Initial Catalog=mydb;Persist Security Info=False;User ID=myadmin;Password=mypassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;'. Note the encryption requirement: Azure SQL Database always encrypts connections (TLS 1.2).
Scenario 1: E-commerce Platform with Seasonal Spikes
A mid-sized online retailer runs its product catalog and order management on SQL Server on-premises. During holiday sales, the database slows down due to increased traffic, and the IT team struggles to provision more hardware quickly. They migrate to Azure SQL Database single database in Standard tier (S2, 50 DTUs). During the holiday season, they use the portal to scale up to S4 (200 DTUs) with zero downtime. After the season, they scale back down. The built-in automatic tuning identifies slow queries and creates indexes. They also enable geo-replication to a secondary region for disaster recovery. The team now spends no time on hardware maintenance and reduces monthly costs by 40% compared to their on-premises licensing and power costs.
Scenario 2: SaaS Multi-Tenant Application
A software company provides a CRM to 500 small businesses. Each customer has a separate database. Workloads are light but erratic; some customers run monthly reports that spike CPU. Using elastic pools, they create a pool of 100 eDTUs and add all 500 databases to the pool. The cost is predictable (the pool price) and customers never experience performance issues because the pool absorbs spikes. The company can add new customers by simply creating a new database in the pool without provisioning new servers. They use the serverless option for a few trial customers to minimize cost. The built-in threat detection alerts them to SQL injection attempts across all databases.
Scenario 3: Lift-and-Shift Migration of Legacy Application
A financial services firm has a monolithic application that relies on SQL Agent jobs, cross-database queries, and linked servers. They cannot modify the application to use PaaS features. They choose Azure SQL Managed Instance, which provides near 100% compatibility. They use the Azure Database Migration Service to migrate the 500 GB database with minimal downtime. The Managed Instance is deployed in a VNet, allowing secure connectivity to their Azure VMs. The firm benefits from automatic backups, patching, and a 99.99% SLA. The only downside is that Managed Instance is more expensive than single database, but it avoids re-architecting costs.
What Goes Wrong When Set Up Incorrectly?
Firewall misconfiguration: If the 'Allow Azure services' checkbox is left unchecked, applications hosted on Azure App Service may not connect. Conversely, leaving it enabled for production can be a security risk.
Scaling without understanding DTU limits: A user might scale to a tier that doesn't support the required storage size (e.g., Basic only has 2 GB).
Forgetting to configure point-in-time restore: By default, backups are retained for 7 days. If a user deletes data, they can restore to any point within that window. But if they reduce retention to 0, they lose the ability to restore.
Choosing wrong redundancy: Using LRS for a critical application means a single region outage could cause data loss. GRS is recommended for production.
What AZ-900 Tests on This Objective (Objective 2.4)
AZ-900 expects you to understand the differences between Azure SQL Database (PaaS) and SQL Server on an Azure VM (IaaS). You should know the three deployment options: Single Database, Elastic Pool, and Managed Instance. You must be able to identify scenarios where each is appropriate. The exam also covers the DTU vs. vCore purchasing models and the basic service tiers (Basic, Standard, Premium). You do not need to memorize specific DTU numbers, but you should know that Basic is for small workloads, Standard for production, and Premium for high-performance and high-availability needs.
Common Wrong Answers and Why Candidates Choose Them
"Azure SQL Database requires you to manage the operating system." – This is a trap. Candidates confuse PaaS with IaaS. Azure SQL Database is PaaS; Microsoft manages the OS. The correct answer is that you only manage data and access.
"Elastic pools are used to scale a single database beyond 4 TB." – Elastic pools are for sharing resources among multiple databases, not for scaling a single database. For large single databases, Hyperscale tier is used.
"Managed Instance is the same as SQL Server on a VM." – Managed Instance is PaaS, not IaaS. It provides more compatibility than single database but still has managed infrastructure. Candidates think because it runs in a VNet, it's IaaS.
"All tiers of Azure SQL Database provide 99.99% SLA." – Only Premium and Business Critical tiers offer 99.99% SLA. Basic and Standard offer 99.95% and 99.99% respectively (Standard has a 99.99% SLA if configured with zone redundancy). The exact SLA varies, and the exam may ask which tier guarantees 99.99%.
Specific Terms and Values That Appear Verbatim
DTU: Database Transaction Unit – a blended measure of CPU, memory, and IO.
vCore: Virtual Core – allows separate scaling of compute and storage.
Elastic Pool: Shared resource pool for multiple databases.
Hyperscale: Tier that supports up to 100 TB of storage.
Point-in-time restore (PITR): Restore to any point within retention period (default 7 days).
Active geo-replication: Create readable secondary databases in different regions.
Azure Hybrid Benefit: Use existing SQL Server licenses to reduce cost.
Edge Cases and Tricky Distinctions
Serverless vs. Elastic Pool: Both are cost-saving, but serverless is for single databases with infrequent usage, while elastic pools are for multiple databases with variable usage.
Managed Instance vs. SQL Server on VM: Both can run in a VNet, but Managed Instance is PaaS (no OS access), while VM is IaaS (full control).
Backup storage redundancy: GRS is default, but LRS is cheaper. The exam may ask which redundancy provides protection against a regional disaster (answer: GRS).
Memory Trick / Decision Tree
Use the acronym S.E.M. for deployment options: Single database (isolated), Elastic pool (shared), Managed instance (compatibility). For purchasing models: DTU (simple, bundled) vs. vCore (flexible, granular). When asked which tier supports 100 TB, think Hyperscale (H for huge).
Azure SQL Database is a PaaS relational database service based on SQL Server.
Three deployment options: Single Database, Elastic Pool, Managed Instance.
Two purchasing models: DTU (bundled) and vCore (granular).
Service tiers: Basic (small, 2GB), Standard (production, up to 1TB), Premium (high performance, up to 4TB).
Hyperscale tier supports up to 100 TB storage.
99.99% SLA is available only in Premium and Business Critical tiers.
Automatic backups with 7-day retention (configurable to 35 days).
Elastic pools allow multiple databases to share resources, reducing cost.
These come up on the exam all the time. Here's how to tell them apart.
Azure SQL Database (PaaS)
Managed by Microsoft – no OS or SQL patching
Built-in high availability and disaster recovery
Automatic backups with point-in-time restore
Scaling is instant (minutes) via tier change
Pricing per hour based on DTU or vCore
SQL Server on Azure VM (IaaS)
Full control over OS and SQL Server configuration
You must configure HA (e.g., Always On) manually
Backups must be set up manually (or use Azure Backup)
Scaling requires resizing VM or adding storage (slower)
Pricing includes VM cost plus SQL Server license
Mistake
Azure SQL Database is exactly the same as SQL Server on a VM.
Correct
Azure SQL Database is a PaaS service; you do not manage the OS or SQL Server installation. SQL Server on a VM is IaaS, where you have full control but must manage everything. The exam distinguishes these clearly.
Mistake
Elastic pools allow a single database to scale beyond its tier limits.
Correct
Elastic pools provide shared resources for multiple databases, not unlimited scaling for one. Each database within a pool still has per-database limits (e.g., max DTU per database).
Mistake
You can only connect to Azure SQL Database from Azure services.
Correct
You can connect from anywhere (on-premises, other clouds) by adding firewall rules to allow specific IP addresses. However, all connections must use TLS 1.2 encryption.
Mistake
Azure SQL Database automatically scales without any configuration.
Correct
Scaling is manual or can be automated via scripts or autoscale settings (for some tiers), but it is not automatic by default. You must choose a tier and scale up/down as needed.
Mistake
Basic tier is suitable for production workloads.
Correct
Basic tier is designed for small workloads, development, or testing. It has limited storage (2 GB), no point-in-time restore, and lower performance. Production workloads should use Standard or Premium tiers.
Azure SQL Database is a fully managed PaaS service where Microsoft handles the OS, SQL engine, backups, and high availability. You only manage the data and schema. SQL Server on an Azure VM is IaaS – you manage the VM, OS, and SQL Server installation, giving you more control but more administrative overhead. For AZ-900, remember that PaaS reduces management effort.
Use an elastic pool when you have multiple databases with varying usage patterns, such as in a multi-tenant SaaS application. The pool allows databases to share resources, reducing overall cost compared to provisioning separate single databases for each peak. For AZ-900, think of elastic pools as a cost-saving measure for many databases with unpredictable workloads.
DTU stands for Database Transaction Unit. It is a bundled measure of compute, storage, and IO. Tiers include Basic (5 DTUs), Standard (10-3000 DTUs), and Premium (125-4000 DTUs). It is simpler than vCore but less flexible. For the exam, know that DTU is a blended metric and that Basic is for small workloads.
In single database and elastic pool, cross-database queries are limited. For full support (e.g., linked servers, SQL Agent jobs), you need Azure SQL Managed Instance or SQL Server on a VM. This is a key differentiator for migration scenarios.
Azure SQL Database automatically performs full backups weekly, differential backups every 12 hours, and transaction log backups every 5-10 minutes. Backups are stored in geo-redundant storage (RA-GRS) by default. You can restore to any point within the retention period (default 7 days, configurable up to 35 days). For long-term retention, you can configure a policy to keep backups for up to 10 years.
Hyperscale is a service tier for Azure SQL Database that supports up to 100 TB of storage, fast scaling, and near-instantaneous backups. It is designed for large databases that need high performance and scalability. For AZ-900, remember that Hyperscale is the tier for very large databases (over 4 TB).
Yes, through Azure Hybrid Benefit. You can apply your existing SQL Server licenses with Software Assurance to reduce the cost of Azure SQL Database in the vCore purchasing model. This is not available in the DTU model. For the exam, know that Azure Hybrid Benefit applies to vCore-based databases.
You've just covered Azure SQL Database — now see how well it sticks with free AZ-900 practice questions. Full explanations included, no account needed.
Done with this chapter?