3. Architecture

The machinery most tutorials skip. If Core Concepts told you the tier is the most consequential choice, this page tells you why: each tier is a genuinely different database architecture wearing the same T-SQL surface.

The path of one query

Trace a single SELECT from an application to a row and back:

1. DNS. The client resolves myserver.database.windows.net. With a private endpoint, a Private DNS zone (privatelink.database.windows.net) answers with a private IP inside your VNet; without one, it answers with a regional gateway IP.
2. Gateway. The connection lands on a regional gateway cluster on TCP 1433. The gateway terminates TLS, authenticates the login, and decides where the database actually lives.
3. Redirect or proxy. Under the Redirect connection policy the gateway hands the client the address of the node hosting the database and the client reconnects directly on a port in the 11000–11999 range — one extra round trip, then no gateway in the data path. Under Proxy, every packet keeps flowing through the gateway on 1433. Redirect is measurably lower latency; Proxy needs one firewall rule instead of a range. This is the single most common cause of "it works from my laptop but not from the corporate network".
4. Authentication. A SQL login is validated against master. An Entra token is validated against Entra ID and mapped to a contained database user. Firewall and VNet rules are evaluated before this — a firewall rejection looks nothing like a login failure, which is useful when debugging.
5. The engine. The SQL Server engine parses, binds, optimises (with the same cardinality estimator and plan cache you know), and executes. Query Store is on by default and records the plan and its runtime statistics.
6. Storage. Where the pages come from depends entirely on the tier — the next section.
7. Return. Rows stream back over TDS. Telemetry lands in sys.dm_db_resource_stats and, if you have configured a diagnostic setting, in Log Analytics.

[Image Prompt: 2D minimalistic numbered sequence diagram tracing a client query through DNS resolution, the regional gateway, the redirect handoff, authentication against Microsoft Entra ID, the SQL engine with its query optimiser and Query Store, and finally the storage layer, flat design, clean vector art style, white background]

Three architectures wearing one name

General Purpose — compute and storage separated

One compute replica runs the engine. The database files (.mdf, .ldf) live on remote Azure Premium Storage, attached over the network. A separate standby is not kept warm; instead, when the compute node dies, the platform starts a new node and re-attaches the same remote files.

• Consequence — latency. Every page read that misses the buffer pool is a network round trip. This is the tier's defining trade-off: cheap, durable, and slower on I/O than local disk.
• Consequence — failover time. Recovery is measured in tens of seconds, because a new node must start and the log must be recovered. ⚠️ verify current failover time expectations against current Azure docs.
• Zone redundancy is a separate toggle that spreads the compute and storage replicas across availability zones within the region. It is the cheapest meaningful resilience upgrade available.

Business Critical — a local availability group

Four replicas, each with the database files on local NVMe SSD, arranged as an Always On availability group: one primary and three secondaries, with synchronous commit.

• Consequence — latency. Local SSD, so I/O latency drops by an order of magnitude versus General Purpose for read-heavy workloads that miss the buffer pool.
• Consequence — failover time. A secondary is already warm, so failover is measured in seconds.
• Consequence — a free read replica. One secondary is exposed for read-only workloads by setting ApplicationIntent=ReadOnly in the connection string. This is genuinely free capacity that most teams never use: reporting queries, exports, and dashboards belong there.
• Consequence — cost. You are paying for four copies of the compute. It is the most expensive non-Hyperscale option and it is frequently chosen for resilience when zone-redundant General Purpose would have been the better value.

Hyperscale — the engine taken apart

The most architecturally interesting of the three. The monolithic engine is split into layers:

• Compute nodes — one primary plus optional secondary replicas, each with a local SSD RBPEX cache (a resilient buffer pool extension) in front of the data.
• The log service — the durability boundary. The primary writes log records here; the log service makes them durable and fans them out to page servers and secondary replicas.
• Page servers — each owns a slice of the database, serving pages to compute nodes and keeping its slice up to date from the log stream.
• Azure Storage — the long-term home of the data and the source of snapshot-based backups.

The payoffs follow directly:

• Size grows without a rebuild, because storage is a fleet of page servers rather than a file.
• Backup and restore are near-constant-time, because they are snapshots at the page-server layer rather than a copy proportional to database size. For a multi-TB database this is the whole reason to choose the tier.
• Read scale-out is a matter of adding named replicas.

The costs: the log service is a throughput ceiling on write-heavy workloads, some features behave differently, and getting back out of Hyperscale is hard.

[Image Prompt: 2D minimalistic side-by-side comparison of three database architectures — General Purpose with one compute node over remote storage, Business Critical with four compute nodes each holding local SSD in an availability group, and Hyperscale with compute nodes over a log service and a row of page servers backed by remote storage, flat design, clean vector art style, white background]

Control plane vs. data plane

Azure's split is sharper than AWS's and this service is one of the places it catches people hardest. Say it plainly:

	Control plane	Data plane
Endpoint	management.azure.com (ARM)	<server>.database.windows.net on TCP 1433 (TDS)
Governs	Creating the server and database, changing tier, configuring firewall, geo-replication, auditing, backup retention	Every SELECT, INSERT, CREATE TABLE, and permission inside the database
Authorised by	Azure RBAC — SQL DB Contributor, SQL Server Contributor, Contributor, Owner	SQL permissions — database roles (db_datareader, db_owner), GRANT/DENY, and the server's SQL or Entra admin
The classic mistake	Assuming Owner on the subscription lets you read a table. It does not — and it never will by that route	Assuming a db_owner can change the service tier. It cannot; that is an ARM operation

The nuance that makes it worse: an Azure Owner can reach the data indirectly — by setting themselves as the Entra admin on the logical server, or by resetting the SQL administrator password. So the control plane is a privilege-escalation path into the data plane, even though it grants no data permission directly. That is exactly the point an interviewer is fishing for, and it is why the SQL Security Manager and SQL Server Contributor roles deserve scrutiny in a review.

The practical production posture:

• Set the Entra admin to a group, not a person, and manage membership through PIM.
• Disable SQL authentication entirely where you can (Microsoft Entra-only authentication is a server-level setting), which removes the password-reset escalation path.
• Give applications managed identities mapped to contained users with the narrowest role that works — usually not db_owner.

[Image Prompt: 2D minimalistic diagram of two separate doors into one Azure SQL database — a control plane door at management.azure.com governed by Azure RBAC roles, and a data plane door at the database.windows.net TDS endpoint governed by SQL permissions and Microsoft Entra authentication — with a dotted escalation arrow from the control plane door showing an administrator resetting credentials, flat design, clean vector art style, white background]

Consistency, durability, and replication

• Inside a database, consistency is strong and transactional. This is SQL Server: ACID, the same isolation levels, and read committed snapshot isolation (RCSI) on by default — which differs from a default on-premises SQL Server install and changes blocking behaviour for anyone migrating. It is usually a pleasant surprise, but it is a behaviour change, and it makes tempdb do more work.
• Local high availability is synchronous. A committed transaction is durable on the local replica set before the commit returns.
• Geo-replication is asynchronous. A geo-secondary lags the primary. A forced failover after a regional outage therefore has a non-zero RPO — you can lose recently committed transactions. Anyone who tells you Azure SQL geo-replication is zero-data-loss is describing the planned failover, which drains the log first, not the unplanned one.
• Backups are geo-redundant by default in most configurations, and the backup storage redundancy setting is fixed at database creation for practical purposes ⚠️ verify current mutability against current Azure docs. Getting it wrong means recreating the database.

Scaling model, and where the ceilings are

Scaling is a vertical operation: change the vCore count, the tier, or the max size, and the platform performs it online. Mechanically it usually means preparing a new replica at the new size and failing over to it, which is why:

• The operation takes minutes, proportional to database size in General Purpose and near-constant in Hyperscale.
• There is a brief connection drop at the end — seconds, at the failover moment. Scaling is not transparent to a client without retry logic. Treat every scale operation as a planned micro-outage.

Horizontal scale is limited and deliberate:

• Read scale-out — Business Critical's free readable secondary and Hyperscale's named replicas, reached with ApplicationIntent=ReadOnly.
• Sharding — your application's problem, not the platform's. Elastic Database Tools exist but sharding is a design decision you own.
• Elastic pools share compute between databases; they do not make one database bigger.

The ceilings, and the scope each is counted at — this is the Azure-specific bit, because a number without a scope is useless:

Limit	Counted at	Note
Max vCores / DTUs per database	Per database	Set by tier and hardware family
Max database size	Per database	Very different for Hyperscale vs. the others
Max concurrent workers and sessions	Per database or pool	Proportional to the compute size — the most common invisible ceiling
Databases per logical server	Per server	Independent of compute
Logical servers per subscription per region	Per subscription, per region	A quota you raise by request
Total vCore quota	Per subscription, per region, per tier family	The one that blocks a Terraform apply in a new subscription and looks like a permissions error

⚠️ All specific numbers vary by region, tier, and subscription type — verify against current Azure docs before designing to any of them.

Failure modes worth recognising

• Throttling on compute. Not an HTTP 429 here — it shows up as queries queuing on worker threads and rising wait_type values. sys.dm_db_resource_stats (20-second granularity, ~1 hour of history) and sys.resource_stats in master (5-minute, longer history) are where you look.
• Session and worker exhaustion. Error 10928/10929. Almost always a connection-pool misconfiguration or a leak rather than a genuine capacity problem.
• Transient connection drops. Error 40613 ("database is not currently available") and friends. These are expected — planned failovers, scaling, and maintenance all produce them. Retry logic with exponential backoff is mandatory, not a nicety. Modern drivers can do this for you (ConnectRetryCount in the .NET connection string; the Azure SQL retry providers in EF Core).
• tempdb pressure. Sized by the compute allocation and not separately adjustable. Heavy sorting, row versioning under RCSI, and spills push against a ceiling you cannot raise except by scaling up.
• Serverless resume timeouts. The first connection after auto-pause fails without retry.
• Firewall rejections that look like auth failures. Error 40615 names the client IP — read it before touching credentials.
• Regional outage. Without a failover group, your recovery is a geo-restore into another region, which is measured in hours for a large database. With one, it is a listener failover in minutes — with the non-zero RPO noted above.

What you should be able to do now

Given a latency complaint, a cost complaint, or a failover requirement, name which tier's architecture is responsible and what the alternative would trade away — and explain why being subscription Owner does not let you read a table, but does let you make it so.

---

Next: Getting Started →

← Back to the Azure SQL Database overview · ← Previous: Core Concepts