Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 57 ยท Databases and Data

Azure SQL Database

Learn Azure SQL Database: deployment options, DTU vs vCore, serverless auto-pause, firewall and Entra auth, geo-replication and connection strings, with SQL examples in Python.

Azure SQL Database is Microsoft's fully managed relational database service, built on the SQL Server engine. You get a database, not a server to look after: Microsoft patches the engine, takes backups, keeps replicas for high availability and handles failover. You focus on schema, queries and performance. If you already know SQL from the site's SQL tutorial, nearly everything transfers; the dialect is Transact-SQL (T-SQL), and the Azure-specific parts are about how the service is sized, secured and connected.

The real service needs an Azure account, so the CLI commands here are read-only. The runnable examples use Python: one runs ordinary SQL against an in-memory SQLite database as a stand-in for the query side, and one simulates how serverless billing works. SQLite is not SQL Server, so the examples stay with SQL that is the same in both, and the lesson points out where T-SQL differs.

Deployment options

Azure has several ways to run SQL Server technology. Choosing the right one saves a migration later.

Option What you get Choose it when
Azure SQL Database (single database) One managed database with its own resources New cloud applications, one database per app or per tenant
Elastic pool Many databases sharing a pool of resources Many databases with different, uncorrelated peaks, such as multitenant SaaS
Azure SQL Managed Instance Nearly 100% SQL Server compatibility, instance-level features, in a VNet Lifting an existing SQL Server estate to the cloud with minimal changes
SQL Server on a VM Full control of the OS and instance Features or versions the PaaS offerings lack, or special licensing and tuning needs

Unless you have a reason otherwise, start with a single database. A logical server is the administrative container for databases: it holds the server name (<name>.database.windows.net), the firewall rules, and the admin sign-in. The server itself costs nothing; you pay for the databases on it.

DTU versus vCore

How big is your database? Azure answers with two purchasing models.

  • DTU model: a DTU (database transaction unit) is a bundled measure of CPU, memory and I/O. You pick a tier (Basic, Standard, Premium) and a size such as S1 or S3. Simple, with predictable prices, but you cannot see or tune the individual resources.
  • vCore model: you choose the number of virtual cores, the hardware generation and the memory per core separately, plus storage. It gives more control, transparency and eligibility for reserved capacity and Azure Hybrid Benefit (bringing existing SQL Server licenses). It is the model Microsoft recommends for most new workloads.

Within vCore you also choose a service tier: General Purpose (remote storage, budget-friendly), Business Critical (local SSD, several replicas, lowest latency, can serve read-only queries from a replica), and Hyperscale (storage that scales into the hundreds of terabytes with fast backups and rapid scale-out of read replicas).

Provisioned versus serverless

In the General Purpose vCore tier you can choose serverless compute. You set a minimum and maximum number of vCores; the database scales between them with load, bills per second of compute used, and can auto-pause after a period of inactivity. While paused you pay only for storage; the first connection after a pause takes a while to wake it up (typically tens of seconds), and the first query may fail or time out if the client does not retry. Serverless suits development databases, intermittent workloads and unpredictable spikes. A database with steady traffic all day is usually cheaper on provisioned compute.

The stand-in below compares the two billing styles over one day. The prices are invented round numbers so you can follow the arithmetic; real prices vary by region and change over time, so use the Azure pricing calculator for decisions.

Python Serverless vs provisioned: a billing model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

For a database that works about 11 hours and sleeps the rest, serverless wins even though each busy hour is more expensive. For a database that never sleeps, the higher per-hour rate loses. The shape of your load decides, so measure before choosing.

Creating a database

These commands change a real subscription, so they are read-only here:

Terminal
az sql server create \
  --resource-group rg-data --name sql-contoso-prod --location westeurope \
  --enable-ad-only-auth \
  --external-admin-principal-type Group \
  --external-admin-name "sql-admins" --external-admin-sid <group-object-id>

az sql db create \
  --resource-group rg-data --server sql-contoso-prod --name appdb \
  --edition GeneralPurpose --family Gen5 --capacity 2 \
  --compute-model Serverless --auto-pause-delay 60 --min-capacity 0.5

Here --auto-pause-delay 60 means 60 minutes of inactivity before pausing, and --enable-ad-only-auth turns off password-based SQL logins entirely, which brings us to security.

Network access and authentication

By default a new server is reachable on its public endpoint, but it denies all connections until you allow them. You have several layers:

  • Server firewall rules: IP address ranges that may connect. There is a convenient but broad switch, "Allow Azure services and resources to access this server", which admits every Azure customer's resources; prefer narrower options.
  • Virtual network rules and Private Endpoints: allow a specific subnet, or give the server a private IP and then disable public access, as in Private Endpoints, VPN and ExpressRoute.
  • Authentication: SQL logins (username and password) or Microsoft Entra authentication (users, groups, and managed identities). Applications on Azure should use a managed identity, so no password exists at all (see Managed Identities and Service Principals).
  • Encryption: connections require TLS, and data is encrypted at rest with Transparent Data Encryption by default.
Terminal
# allow one office address (read-only example)
az sql server firewall-rule create \
  --resource-group rg-data --server sql-contoso-prod \
  --name office --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10
SQL
-- Give a managed identity access to one database (run as the Entra admin)
CREATE USER [app-contoso-api] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-contoso-api];
ALTER ROLE db_datawriter ADD MEMBER [app-contoso-api];

Connection strings

A connection string tells a driver where to go and how to authenticate. Two typical forms for ADO.NET-style clients (read-only; the values are invented):

TEXT
Server=tcp:sql-contoso-prod.database.windows.net,1433;Database=appdb;Authentication=Active Directory Default;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Server=tcp:sql-contoso-prod.database.windows.net,1433;Database=appdb;User ID=appuser;Password=<secret>;Encrypt=True;

The first uses Entra authentication and contains no secret, which is the safe default. If you do need a password, never put it in source code; keep it in Key Vault. Because Azure SQL can move your database between nodes during maintenance, applications must retry transient errors (a short exponential backoff is enough). Most official drivers and libraries provide this.

Resilience and recovery

Azure SQL Database includes automated backups with point-in-time restore (a retention window you configure, default several days) and optional long-term retention for months or years. For availability, zone-redundant configurations spread replicas across availability zones in one region. For disaster recovery across regions you can use:

  • Active geo-replication: up to four readable secondary databases in other regions for a single database. You fail over manually (or with your own automation).
  • Failover groups: a group of databases with read-write and read-only listener endpoints (<group>.database.windows.net). The names stay the same after failover, so connection strings do not change, and failover can be automatic.

Replication to another region is asynchronous, so a forced failover can lose the most recent transactions; the reliability lesson later on covers RPO and RTO.

Managed Instance, auditing and threat protection

Choosing between the three SQL Server options

The table near the top of this lesson lists the options; the real question is who runs what. With a single database you get a database only: no SQL Agent, no cross-database queries between separate databases, no instance-level settings. Managed Instance is a whole SQL Server instance run as PaaS: it supports SQL Agent jobs, cross-database queries, linked servers, Database Mail and other instance features, and it lives inside your own virtual network on a dedicated subnet (see Virtual Networks and Subnets), so it has a private address from day one. Creating the first instance in a subnet can take hours, and it costs more than a single database, so choose it when an application depends on those instance features. SQL Server on a VM is the IaaS option: you patch, back up and configure everything, in exchange for full control and any version or feature. As a rule: single database for new applications, Managed Instance for lift and shift of an existing estate with minimal changes, a VM only when neither PaaS option fits.

Auditing

Auditing records database events (logins, queries, schema changes, permission changes) so you can answer "who did what, and when?". You enable it on the logical server (applies to every database) or on a single database, and choose a destination: a storage account, a Log Analytics workspace (queryable with KQL, see Azure Monitor, Log Analytics and Application Insights) or Event Hubs for streaming to other tools.

Terminal
az sql server audit-policy update --resource-group rg-data --name sql-shop-4821 \
  --state Enabled --blob-storage-target-state Enabled --storage-account stauditdata4821 \
  --retention-days 90

Microsoft Defender for SQL (advanced threat protection)

Microsoft Defender for SQL adds two things: vulnerability assessment (a scan that flags weak configuration such as excessive permissions or missing auditing) and advanced threat protection, which looks for suspicious behavior such as probable SQL injection, access from an unusual location, brute-force logins or unusual data export. Alerts appear in Microsoft Defender for Cloud and can be emailed. It is a paid plan, and it detects and alerts rather than blocks, so someone must respond.

Encryption and sign-in

  • Transparent Data Encryption (TDE) encrypts the data and backup files at rest and is on by default with a service-managed key. If rules require it you can switch to a customer-managed key stored in Azure Key Vault.
  • Microsoft Entra authentication lets people and managed identities sign in without SQL passwords; set an Entra admin, and consider Entra-only authentication, which turns SQL logins off entirely:
Terminal
az sql server ad-admin create --resource-group rg-data --server-name sql-shop-4821 \
  --display-name "DB Admins" --object-id <group-object-id>
az sql server ad-only-auth enable --resource-group rg-data --name sql-shop-4821

Detecting suspicious sign-ins: a stand-in

Defender for SQL uses far richer models than this, but the idea is simple: compare audit records with a baseline. The stand-in below flags three patterns in invented audit rows: repeated failed logins, a login from a new country, and a classic injection fragment in a statement.

JS Flag suspicious audit records

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The real service alerts you; the actual fix for the injection alert is in the application: use parameterized queries, never string concatenation.

The SQL itself

Everything you learned about relational design applies. This runnable stand-in uses Python's sqlite3 module and plain ANSI SQL that behaves the same in T-SQL. It shows a join with aggregation, why parameterized queries matter, and how a transaction undoes a half-finished change.

Python SQL basics with a SQLite stand-in

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The string-built query returns every customer because the quote characters turned the input into SQL, while the parameterized version treats the whole text as a value and finds nothing. In real Azure SQL code you would use parameters in the same way (@name placeholders in ADO.NET, ? in most Python drivers). The failed insert breaks a foreign key, so the earlier update in the same transaction is undone and order 1 keeps its paid status.

Where T-SQL differs from the SQLite you just ran: limiting rows uses SELECT TOP (10) ... or OFFSET ... FETCH NEXT, not LIMIT; strings are concatenated with + or CONCAT; identity columns use IDENTITY(1,1); and the current time is SYSUTCDATETIME(). The SQL Reference pages cover dialect differences in detail.

Managed Instance features, network security and PaaS versus IaaS

What Managed Instance gives you

SQL Managed Instance is the choice when you need near-complete SQL Server compatibility without managing a VM. Features that applications often rely on are available: stored procedures and user-defined functions, full-text search, In-Memory OLTP (memory-optimized tables for very fast transactions), columnstore indexes for analytics, temporal tables that keep row history automatically, dynamic data masking and row-level security (RLS). It is bought with the vCore model (General Purpose or Business Critical tiers), supports instance-level features such as SQL Agent, cross-database queries and linked servers, and is placed inside your own virtual network. Regulatory compliance certifications (ISO, SOC, HIPAA and others) cover the platform, but they certify Azure, not your application.

Two of those features are about showing the right data to the right person:

  • Dynamic data masking rewrites query results for users without the unmask permission, for example showing [email protected] instead of an email. The stored data is unchanged, so it is a convenience layer, not encryption.
  • Row-level security adds a filter predicate so each user sees only the rows they may see, such as their own tenant's orders, enforced by the database for every query.
JS Masking and row-level security, simulated

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Network-level protection of the service

Before a request reaches your database it passes through a gateway layer that Azure operates. The gateway terminates the TDS protocol (the wire protocol SQL Server clients speak), applies packet filtering against your firewall rules, and uses DoSGuard to detect and temporarily block sources that hammer the service with repeated failed sign-ins or floods of requests. Tenants are kept apart with network segregation, so one customer's traffic never shares a segment with another's. You do not configure these layers; your job is the layer above them: firewall rules, private endpoints, Microsoft Entra authentication, auditing and encryption (see Private Connectivity).

Availability and "versionless" SQL

Azure SQL services build high availability in. General Purpose uses remote storage plus a spare compute node that takes over after a failure; Business Critical keeps several synchronized replicas, one of which can serve read-only queries; zone redundancy spreads those replicas across availability zones so a zone outage does not stop the database. Planned work (patching, upgrades) is done by moving the database to a replica, so planned downtime is a brief reconnect, and your client should retry on transient errors. This is also why PaaS SQL is called versionless: Azure always runs a current engine and patches it, unlike SQL Server on a VM, where you choose the version and own the upgrades.

SQL Server on a VM (IaaS) Azure SQL Database / Managed Instance (PaaS)
You manage OS, SQL version, patching, backups, HA design Schema, queries, security settings
Scaling Resize the VM or add replicas yourself Change the tier or vCores, or serverless auto-scale
Compatibility Everything High (Managed Instance) or good (Database)
Best for Unusual features, full OS access Most new and migrated workloads

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A SaaS product has 200 small customer databases whose busy periods rarely overlap. Which option fits best?

    Elastic pools let databases with uncorrelated peaks share resources, which is cheaper than sizing each one for its own peak.
  2. 2. What happens to a serverless database after its auto-pause delay passes with no activity?

    Paused databases have no compute charge. The first connection triggers resume, so clients should retry.
  3. 3. Why is Entra authentication with a managed identity preferable to a SQL login in an application's connection string?

    A managed identity gets tokens from Azure, so there is no secret in code or configuration.
  4. 4. What is the advantage of a failover group over plain active geo-replication?

    Failover groups provide read-write and read-only listener names that follow the primary, and can fail over automatically.
  5. 5. Which query style protects against SQL injection?

    With parameters the database never interprets the input as SQL syntax.

Summary

  • Azure SQL Database is a managed T-SQL database; choose single database, elastic pool, Managed Instance or SQL Server on a VM by compatibility and workload shape.
  • The vCore model gives control over cores and storage tier; DTU is a simple bundle; serverless auto-pauses and bills per second, ideal for intermittent load.
  • A new server denies connections until you add firewall rules, virtual network rules or a Private Endpoint; prefer Entra authentication and managed identities over passwords.
  • Connection strings should carry no secrets, and clients should retry transient errors.
  • Backups, zone redundancy, geo-replication and failover groups provide resilience; cross-region replication is asynchronous.

Next: Azure Cosmos DB.