Azure SQL Database, in depth: tiers, storage architecture, connectivity, HA/DR and operating it well

The building blocks

A logical server is a namespace and security boundary: a DNS name such as myserver.database.windows.net, firewall rules, Microsoft Entra administrators and auditing settings. It is not a machine; databases under one logical server can live on different hardware. A database has its own service tier and compute size. An elastic pool lets many databases share a pool of vCores, which suits SaaS designs with one database per tenant and uneven load.

There are two purchasing models. The older DTU model bundles CPU, memory and I/O into a single unit. The vCore model, which this article uses, lets you pick tier, hardware and vCores separately, enables reservations and Azure Hybrid Benefit, and maps more naturally to on-premises sizing. Within vCore you choose provisioned compute, billed per hour whether used or not, or serverless, which autoscales between a minimum and maximum, bills per second and can auto-pause idle databases. Serverless is supported only on standard-series (Gen5) hardware.

Three tiers, three storage architectures

General Purpose separates compute from storage. A stateless node runs the engine with its buffer pool and plan cache; data and log files sit in Azure premium remote storage, which provides durability. If the node fails or is patched, the service starts the engine on a spare node and attaches the same files. The cost is latency: Microsoft quotes storage latency of 5 to 10 ms, and a failover starts with a cold cache. Sizes are 2 to 128 vCores and 1 GB to 4 TB.

Business Critical works like an Always On availability group. Each database is a four-node cluster: one primary and three secondaries, every node with data on local SSD. The primary ships log to the secondaries, failover promotes an already-warm replica, and one secondary is offered free as a read-only endpoint. Storage latency averages 1 to 2 ms. The cost is roughly 2.7 times General Purpose compute for the same vCores, and storage is still capped at 4 TB.

Hyperscale splits the engine into services. Compute nodes run the engine with a local SSD cache (RBPEX). The log service accepts log records from the primary and fans them out. Page servers each own a slice of up to 128 GB, replay the log to keep pages current and serve pages to compute on a cache miss. Azure Storage holds the data files and backups as snapshots. Because no replica copies data, adding a replica or scaling compute is fast, backups do not touch compute, and the database can grow to 128 TB. You choose 0 to 4 high-availability replicas and can add up to 30 named replicas for read scale-out. Microsoft now recommends Hyperscale for new OLTP and HTAP workloads.

Connectivity: gateway, Redirect and Proxy

Clients connect to a regional gateway on TCP 1433. What happens next depends on the server's connection policy. With Redirect, the gateway tells the client the address of the node hosting the database and the client reconnects directly, on a port in the range 11000 to 11999. With Proxy, every packet flows through the gateway, adding latency and a shared hop. The Default policy uses Redirect for clients inside Azure and Proxy for clients outside.

Redirect is faster and Microsoft recommends it, but it needs outbound firewall rules for the whole port range to the region's SQL addresses; the Sql.<region> service tag makes that manageable in network security groups. A common incident is a corporate firewall that allows 1433 only: logins succeed against the gateway and then hang. For production, prefer a private endpoint in your virtual network and disable public network access, so the database has no internet-facing path at all.

Authenticate with Microsoft Entra identities, ideally managed identities for applications, instead of SQL logins with passwords in configuration.

The rest of the security baseline is mostly configuration. Transparent data encryption protects files and backups at rest and is enabled on new databases; switch to a customer-managed key if your compliance regime requires key control. Turn on auditing to a storage account or Log Analytics workspace, because the audit trail is what an incident review will ask for. For columns that even database administrators must not read, Always Encrypted keeps data encrypted inside the engine; its secure-enclave variant, which allows richer queries over encrypted columns, needs DC-series hardware for Intel SGX enclaves or VBS enclaves on other hardware.

Resilient client code

A managed database fails over for patching, scaling and hardware faults. Each event drops connections for seconds. Applications that treat every error as fatal turn routine maintenance into incidents. The fix is a retry policy for transient errors, with exponential backoff, applied to opening connections and to idempotent units of work. Error numbers such as 40613 (database not currently available), 40197 (service error processing the request) and 40501 (service busy) are the classic transient set; check Microsoft's transient-fault documentation for the current list.

import random, time, pyodbc

TRANSIENT = {40613, 40197, 40501}   # extend from Microsoft's transient-fault list

def sql_error_number(exc):
    for arg in exc.args:
        for num in TRANSIENT:
            if f"({num})" in str(arg):
                return num
    return None

def run_with_retry(conn_str, work, attempts=6, base=0.5, cap=20.0):
    for attempt in range(1, attempts + 1):
        conn = None
        try:
            conn = pyodbc.connect(conn_str, timeout=30)
            result = work(conn)              # must be idempotent or wrapped in one transaction
            conn.commit()
            return result
        except (pyodbc.OperationalError, pyodbc.Error) as exc:
            num = sql_error_number(exc)
            retryable = num is not None or isinstance(exc, pyodbc.OperationalError)
            if not retryable or attempt == attempts:
                raise
            time.sleep(min(cap, base * 2 ** attempt) * random.uniform(0.5, 1.0))
        finally:
            if conn is not None:
                conn.close()                 # pyodbc's context manager does not close

conn_str = ("Driver={ODBC Driver 18 for SQL Server};"
            "Server=tcp:myserver.database.windows.net,1433;Database=appdb;"
            "Authentication=ActiveDirectoryMsi;Encrypt=yes;")

Matching on the error number in the message text is crude but portable across drivers; in .NET, Microsoft.Data.SqlClient exposes the number directly and has a configurable built-in retry provider. Two rules matter more than the code. Never blindly retry a non-idempotent write that may have committed before the connection dropped; use an idempotency key or check before retrying. And keep connection pools modest: each database size has a worker limit, and a fleet of oversized pools can exhaust it during a retry storm. See connection pooling for sizing.

Read scale-out and replicas

Business Critical and Hyperscale expose readable secondaries. You route to them by adding ApplicationIntent=ReadOnly to the connection string; the gateway sends that session to a replica. To confirm where a session landed, query SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability'), which returns READ_ONLY on a replica.

Replicas are asynchronous for reads: a row you just committed on the primary may not be visible on a secondary for a short time. That breaks read-your-own-writes flows, such as redirecting to a details page after a save. Route those reads to the primary, or route by staleness tolerance per query. The general patterns are covered in database replication. Hyperscale named replicas have their own compute size and connection string, which makes them good for isolating a reporting or HTAP workload from OLTP.

Business continuity: backups, geo-replication and failover groups

Every database gets automatic full, differential and log backups with point-in-time restore. Retention is 1 to 35 days, 7 by default, and long-term retention keeps weekly, monthly or yearly full backups for up to 10 years. Backup storage redundancy is chosen per database: locally redundant, zone-redundant or geo-redundant. Geo-redundant backups enable geo-restore to another region, but restoring from backup is slow for large databases and loses recent changes.

For a real regional DR plan, use active geo-replication or, better, a failover group. A failover group replicates one or more databases to a partner server in another region and gives you two stable DNS listeners: <group>.database.windows.net for read-write and <group>.secondary.database.windows.net for read-only. After failover the names move, so applications need no connection-string change.

az sql db create -g rg-app -s sql-weu -n appdb \
  --edition Hyperscale --family Gen5 --capacity 4 \
  --ha-replicas 1 --zone-redundant true --backup-storage-redundancy Zone

az sql failover-group create -g rg-app -s sql-weu -n fg-app \
  --partner-server sql-neu --add-db appdb --failover-policy Manual

# drill: fail over to the secondary, then back
az sql failover-group set-primary -g rg-app -s sql-neu -n fg-app

Geo-replication is asynchronous, so a forced failover can lose the last seconds of transactions; Microsoft guarantees an RPO of 5 seconds and an RTO of 30 seconds for Business Critical with active geo-replication. Decide in advance who triggers failover and under what conditions, and rehearse it: a failover group that has never been failed over is a hypothesis.

Monitoring and tuning

Start with the resource view. sys.dm_db_resource_stats returns CPU, data I/O, log write and worker percentages relative to your service objective every 15 seconds for about an hour; Azure Monitor keeps longer history. If log write percentage is pinned at 100, more CPU will not help, because log rate is governed per service objective.

SELECT TOP (20) end_time, avg_cpu_percent, avg_data_io_percent,
       avg_log_write_percent, max_worker_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

Query Store is enabled by default and records plans and runtime statistics per query; use it to find regressions after deployments and to force a known good plan while you fix the root cause. Automatic tuning can force last-good plans for you. Plan quality is still your job: missing indexes, implicit conversions and parameter-sensitive plans behave exactly as in SQL Server, so the reasoning in query planners applies directly. Azure SQL uses read committed snapshot isolation by default, which changes blocking behaviour for code migrated from on-premises SQL Server; see isolation levels.

Failure modes and trade-offs

• Connection drops during maintenance look like outages to code without retries. Fix the client, and configure a maintenance window for predictable timing.
• Worker or session exhaustion from oversized pools or long transactions. Watch max_worker_percent and cap pools.
• Log rate throttling during bulk loads. Batch the load, use minimal logging where possible, or scale up for the load window.
• Serverless cold starts. Where auto-pause is enabled, the first connection after a pause waits for resume, and the buffer pool starts cold; disable auto-pause for latency-sensitive paths, and check the current serverless documentation for which tiers support it.
• Stale reads from replicas breaking read-your-writes, as above.
• Cost surprises: Business Critical replicas are billed, Hyperscale bills each replica's compute, and geo-secondaries are full databases. Price the topology, not the primary.
• Retired hardware. Fsv2-series is retired as of 1 October 2026; keep infrastructure-as-code templates on standard-series or Hyperscale premium-series.

What to do next

1. Pick the tier from latency, size and read-scale needs: Hyperscale for new or growing OLTP, Business Critical for the lowest steady I/O latency, General Purpose for budget workloads that tolerate 5-10 ms I/O.
2. Put the database behind a private endpoint, disable public access and authenticate with managed identities.
3. Add transient-fault retries with backoff and idempotency keys before go-live, and cap connection pools.
4. Route read-only work with ApplicationIntent=ReadOnly, keeping read-your-writes paths on the primary.
5. Create a failover group, point applications at its listeners and run a failover drill each quarter.
6. Alert on log write, worker and CPU percentages from sys.dm_db_resource_stats and review Query Store after each release.