Azure SQL Database Advanced Patterns: Hyperscale, Elastic Pools, Ledger, and Always Encrypted with Secure Enclaves

Most teams treat Azure SQL Database as “SQL Server, but someone else patches it.” That mental model leaves enormous capability on the table. The parts that change how you architect have no on-prem equivalent: a storage engine that decouples compute from a distributed log and page-server tier, read replicas you can size independently, tamper-evident ledger tables backed by a Merkle tree, and Always Encrypted enclaves that run WHERE name LIKE over data the engine itself cannot decrypt. This is a tour of those patterns, wired correctly, with the gotchas I have hit in production.

Everything below assumes a logical server (Microsoft.Sql/servers) already exists. I use sql-prod-eastus2 as the logical server and rg-data-eastus2 as the resource group throughout.

1. Hyperscale architecture: page servers, the log service, and named replicas

Hyperscale is a fundamentally different engine from General Purpose or Business Critical. Instead of a monolithic data file on a managed disk, it splits the database into four cooperating tiers:

• Compute nodes run the relational engine and a local SSD cache (the RBPEX – resilient buffer pool extension). The primary compute serves writes and reads; secondary computes serve reads.
• Page servers each own a slice of the database (covering up to 1 TB of data) and serve 8 KB pages on demand to compute nodes that miss their local cache. Adding data adds page servers transparently – this is why Hyperscale scales to 128 TB without you ever pre-provisioning storage.
• The log service is the heart of the design. The primary writes log records to a durable landing zone (Azure Premium Storage), and the log service then fans those records out to page servers and to secondary replicas, and offloads them to long-term storage for backups. There is no checkpoint-and-flush of data pages from compute; durability is the log.
• Azure Storage holds the long-term log and the snapshot-based backups.

The consequence that matters: backups are snapshot-based and effectively size-independent, so a 50 TB database backs up in roughly the same wall-clock time as a 500 GB one, and a restore is fast because it is rehydrating page-server snapshots, not replaying a full backup chain.

Provision a Hyperscale database with secondary replicas in one shot:

az sql db create \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name appdb \
  --edition Hyperscale \
  --family Gen5 \
  --capacity 8 \
  --ha-replicas 2 \
  --zone-redundant true \
  --backup-storage-redundancy Zone

--ha-replicas (0-4) provisions HA secondary replicas. These share the same page servers and log as the primary, give you fast failover targets, and can serve read-only traffic via ApplicationIntent=ReadOnly. Zone redundancy on Hyperscale spreads the primary and HA replicas across availability zones; do not skip it for production.

Named replicas: the feature that changes read-scale architecture

HA replicas are coupled to the primary’s compute size and serve generic read traffic. Named replicas are different and far more powerful: each is a separate database resource with its own name, its own compute SKU, and its own access control, but reading from the same page servers as the primary (so they cost compute, not a second copy of storage). You can attach up to 30 of them.

This is how you build true read-scale-out: a small primary sized for OLTP writes, and named replicas individually sized – a big one for the BI/Power BI workload, a small one for an ad-hoc reporting team, each isolated so a runaway report cannot starve the OLTP primary.

# A large named replica dedicated to the analytics workload,
# pointed at the SAME primary database (--partner-database).
az sql db replica create \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name appdb \
  --partner-server sql-prod-eastus2 \
  --partner-database appdb-bi-replica \
  --secondary-type Named \
  --family Gen5 \
  --capacity 16

Named replicas are read-only and lag the primary by milliseconds (they consume the same log stream). Because each has its own logins and DENY-able surface, you can hand a reporting team a connection to appdb-bi-replica with zero write capability and zero blast radius onto production – something you cannot cleanly do with a plain HA replica.

2. Rapid scale, fast restore, and read-scale-out in practice

Because storage is disaggregated, scaling compute up or down does not move data – it reattaches a new compute node to the existing page servers and warms the cache. A vCore resize that would take hours on a Business Critical database (it has to seed a new local copy) completes in minutes on Hyperscale.

# Vertical scale: 8 -> 16 vCores. No data movement; warm-up is the only cost.
az sql db update \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name appdb \
  --capacity 16

Point-in-time restore (PITR) leans on the snapshot architecture and is the cheap insurance you should rehearse:

az sql db restore \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name appdb \
  --dest-name appdb-restored \
  --time "2026-06-08T03:30:00Z" \
  --edition Hyperscale --family Gen5 --capacity 8

For read-scale, point your application’s reporting connections at a named replica’s FQDN, or use ApplicationIntent=ReadOnly against the primary’s server to be routed to an HA replica:

Server=tcp:sql-prod-eastus2.database.windows.net,1433;
Database=appdb;Authentication=Active Directory Default;
ApplicationIntent=ReadOnly;Encrypt=True;

3. Elastic pools, vCore density, and tenant-per-database design

If you run a SaaS product with hundreds or thousands of tenants, the cleanest isolation model is one database per tenant. The cost objection – “I cannot pay for a dedicated database SKU per tenant” – is exactly what elastic pools solve. A pool is a shared budget of vCores (or DTUs) and storage that all databases in the pool draw from. A tenant idle at 2 a.m. lends its capacity to a tenant under load; you provision for the aggregate peak, not the sum of individual peaks.

# A vCore elastic pool with per-database min/max guardrails so no single
# noisy tenant can monopolise the whole pool.
az sql elastic-pool create \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name pool-tenants-01 \
  --edition GeneralPurpose --family Gen5 \
  --capacity 16 \
  --db-min-capacity 0.5 \
  --db-max-capacity 4 \
  --zone-redundant true

The two knobs that make or break density:

Move databases in and out of the pool without downtime – this is just a metadata operation:

az sql db update \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name tenant-0457 \
  --elastic-pool pool-tenants-01

Watch out: a database can only join a pool of the same logical server, edition family, and (for Hyperscale) Hyperscale has its own pool type. Mixing service tiers in one pool is not allowed. Size the pool from real telemetry – the pool’s eDTU/vCore and storage percentage metrics in Azure Monitor are your sizing evidence, not a guess.

4. Ledger tables for tamper-evidence and cryptographic verification

Ledger gives you blockchain-style integrity inside a relational database: every change is recorded such that any tampering – even by a sysadmin with full rights, even by Microsoft – is cryptographically detectable. It works by hashing rows into a Merkle tree, periodically sealing the tree root into a database digest, and letting you publish that digest to immutable storage you control. Verification recomputes the tree and compares it to the digest you trusted.

Two flavours:

• Updatable ledger tables allow UPDATE/DELETE, but the engine keeps the full history in a paired history table and surfaces it through a _Ledger view. Nothing is ever truly destroyed.
• Append-only ledger tables reject UPDATE and DELETE entirely – ideal for an audit log.

-- Append-only ledger: an immutable audit trail. UPDATE/DELETE will fail.
CREATE TABLE dbo.PaymentAudit
(
    AuditId     BIGINT IDENTITY(1,1) PRIMARY KEY,
    AccountId   INT          NOT NULL,
    Action      NVARCHAR(50) NOT NULL,
    Amount      DECIMAL(19,4) NOT NULL,
    OccurredAt  DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Updatable ledger: full history is retained automatically and queryable.
CREATE TABLE dbo.AccountBalance
(
    AccountId INT PRIMARY KEY,
    Balance   DECIMAL(19,4) NOT NULL
)
WITH (LEDGER = ON);  -- creates AccountBalance_Ledger view + history table

Generate and verify a digest. In production you configure automatic digest storage to an immutable Azure Storage container or Confidential Ledger, so digests are written to a location even Azure operators cannot rewrite:

-- Generate the current database digest (the value you persist off-box).
EXEC sys.sp_generate_database_ledger_digest;

-- Verify the entire database against the digests you trust.
-- @digests is the JSON array of previously stored digest blocks.
EXEC sys.sp_verify_database_ledger @digests;

If a single byte of any ledger row was altered outside the normal transaction path, sp_verify_database_ledger raises an error instead of returning cleanly. That is the whole value proposition: you can hand an auditor a verification procedure rather than a promise.

Ledger is tamper-evidence, not tamper-prevention. It does not stop a privileged user from changing data; it guarantees you will find out. Pair it with Encrypt=True, RBAC, and auditing – and configure automatic digest upload, because a digest you never stored off-box proves nothing.

5. Always Encrypted with secure enclaves and rich computations

Always Encrypted keeps column data encrypted all the way through the database engine – the keys live with the client (or in a key store the client controls), and the SQL service literally never sees plaintext. Classic Always Encrypted has a brutal limitation: because the engine sees only ciphertext, it can do equality comparisons (with deterministic encryption) and nothing else. No LIKE, no range queries, no in-place re-encryption.

Secure enclaves lift that limitation. A VBS (Virtualization-Based Security) enclave is a protected memory region on the SQL node that the host OS and DBAs cannot inspect. The client establishes attestation (via Microsoft Azure Attestation), then delegates the plaintext keys into the enclave. Inside the enclave – and only there – the engine can decrypt, run LIKE and range predicates, and even re-encrypt columns in place. The DBA still never sees plaintext; the enclave is a sealed black box.

Enable the enclave on the database and create an enclave-enabled key:

-- Column Master Key must allow enclave computations.
CREATE COLUMN MASTER KEY CMK_Enclave
WITH (
    KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
    KEY_PATH = 'https://kv-data-eastus2.vault.azure.net/keys/cmk-ae/abc123',
    ENCLAVE_COMPUTATIONS (SIGNATURE = 0x...)  -- signature emitted by the tooling
);

CREATE COLUMN ENCRYPTION KEY CEK_Enclave
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_Enclave,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x...
);

Encrypt a column with randomized encryption (the strongest mode) and still query it with rich predicates – something impossible without the enclave:

ALTER TABLE dbo.Customer
ALTER COLUMN [SSN] NVARCHAR(11)
    ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = CEK_Enclave,
        ENCRYPTION_TYPE = RANDOMIZED,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    )
WITH (ONLINE = ON);

The client connection must opt into attestation. With the sqlcmd/ODBC stack and Azure Attestation:

Server=tcp:sql-prod-eastus2.database.windows.net,1433;Database=appdb;
Authentication=Active Directory Default;Encrypt=True;
ColumnEncryptionSetting=Enabled;
AttestationProtocol=AAS;
EnclaveAttestationUrl=https://attest-eastus2.eus.attest.azure.net/attest/SgxEnclave;

The enclave processes plaintext in protected memory only. To benefit, your column master key must be marked ENCLAVE_COMPUTATIONS and the client must successfully attest – otherwise the engine falls back to ciphertext-only behaviour and your LIKE query fails. Test the attestation path in a lower environment first; misconfigured attestation URLs are the number-one cause of “it works on my machine but not in CI.”

6. Private connectivity, Entra-only auth, and least-privilege RBAC

None of the above matters if the database is reachable from the public internet with SQL logins. Lock the perimeter and the identity layer:

# Kill public network access; the database is reachable only via Private Endpoint.
az sql server update \
  --resource-group rg-data-eastus2 \
  --name sql-prod-eastus2 \
  --enable-public-network false

# Private Endpoint into your data subnet, plus the privatelink DNS zone.
az network private-endpoint create \
  --resource-group rg-data-eastus2 \
  --name pe-sql-prod \
  --vnet-name vnet-data-eastus2 --subnet snet-data \
  --private-connection-resource-id "/subscriptions/<sub>/resourceGroups/rg-data-eastus2/providers/Microsoft.Sql/servers/sql-prod-eastus2" \
  --group-id sqlServer \
  --connection-name pe-sql-prod-conn

Enforce Microsoft Entra-only authentication so SQL logins and passwords cannot be used at all – this single setting eliminates an entire class of credential-leak incidents:

az sql server ad-only-auth enable \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2

Then grant database access to managed identities and Entra groups, with the least role that works – prefer the granular fixed database roles over db_owner:

-- App's user-assigned managed identity gets reader/writer, NOT db_owner.
CREATE USER [mi-appdb-writer] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [mi-appdb-writer];
ALTER ROLE db_datawriter ADD MEMBER [mi-appdb-writer];

-- Reporting Entra group gets read-only, scoped to the named replica.
CREATE USER [grp-bi-readers] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [grp-bi-readers];

7. Performance: automatic tuning and Query Store baselines

Hyperscale and elastic pools do not absolve you of query hygiene – they amplify the cost of a bad plan because more workloads share the resource. Query Store is on by default and is your flight recorder: it captures query text, plans, and runtime stats so you can see which plan regressed and when. Enforce sensible retention and read-write mode:

ALTER DATABASE appdb SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE,
     QUERY_CAPTURE_MODE = AUTO,
     MAX_STORAGE_SIZE_MB = 1024,
     STALE_QUERY_THRESHOLD_DAYS = 30);

Turn on automatic tuning so the platform forces the last good plan when a regression is detected and creates/drops indexes from its recommendations – inherited from the server unless overridden:

az sql db update \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --name appdb

-- Enable plan regression correction and index automation at the DB level.
ALTER DATABASE appdb SET AUTOMATIC_TUNING
    (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = ON);

Find the regressions Query Store is hiding for you:

-- Top 10 queries by total duration over the last 24h, with their plan count.
SELECT TOP 10
    qsq.query_id,
    qst.query_sql_text,
    COUNT(DISTINCT p.plan_id)        AS plan_count,
    SUM(rs.avg_duration * rs.count_executions) AS total_duration_us
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qst ON qst.query_text_id = qsq.query_text_id
JOIN sys.query_store_plan p         ON p.query_id = qsq.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time > DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY qsq.query_id, qst.query_sql_text
ORDER BY total_duration_us DESC;

A plan_count greater than 1 on a hot query is your signal to investigate parameter-sensitivity and consider forcing a plan.

8. HA/DR with zone redundancy, geo-replication, and failover groups

Layer your resilience; do not pick one and call it done.

• Zone redundancy (set at provisioning, Section 1) survives a single-AZ outage with no data loss and no action.
• Active geo-replication creates a readable secondary in another region you manage explicitly.
• Failover groups wrap geo-replication with a managed listener endpoint, so applications use a stable FQDN that survives failover, and add automatic-failover policy.

# A failover group across regions with automatic failover after a grace period.
az sql failover-group create \
  --resource-group rg-data-eastus2 \
  --server sql-prod-eastus2 \
  --partner-server sql-dr-westus2 \
  --name fog-appdb \
  --add-db appdb \
  --failover-policy Automatic \
  --grace-period 1

Applications then connect to the failover-group listener – never a regional server name:

Server=tcp:fog-appdb.database.windows.net,1433;Database=appdb;
Authentication=Active Directory Default;Encrypt=True;

A read-write listener (fog-appdb.database.windows.net) and a read-only listener (fog-appdb.secondary.database.windows.net) are created for you. Route reporting at the read-only listener so a region failover transparently follows the secondary. And rehearse a real failover quarterly – an untested DR plan is a hypothesis, not a control.

Verify

Prove each layer end to end, not just that resources exist.

# Hyperscale tier, replica count, and zone redundancy in one view.
az sql db show \
  --resource-group rg-data-eastus2 --server sql-prod-eastus2 --name appdb \
  --query "{edition:currentServiceObjectiveName, replicas:highAvailabilityReplicaCount, zone:zoneRedundant}" -o table

# Confirm public access is off and Entra-only auth is on.
az sql server show -g rg-data-eastus2 -n sql-prod-eastus2 \
  --query "{publicAccess:publicNetworkAccess}" -o table
az sql server ad-only-auth get -g rg-data-eastus2 -s sql-prod-eastus2

-- Which physical role am I on? (Primary is READ_WRITE; replicas READ_ONLY.)
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS rw_or_ro;

-- Ledger digest verifies clean (no error == no tampering detected).
EXEC sys.sp_generate_database_ledger_digest;

-- Confirm Always Encrypted enclave columns are RANDOMIZED, not ciphertext-only.
SELECT c.name, ek.name AS cek, c.encryption_type_desc
FROM sys.columns c
JOIN sys.column_encryption_keys ek ON ek.column_encryption_key_id = c.column_encryption_key_id
WHERE c.encryption_type IS NOT NULL;

Expected: Hyperscale edition with the requested replica count and zoneRedundant=true; publicNetworkAccess=Disabled and Entra-only enabled; the primary reports READ_WRITE while named/HA replicas report READ_ONLY; the ledger digest procedure returns without error; and your enclave columns show RANDOMIZED with rich predicates working through an attested client connection.

Enterprise scenario

A health-tech platform team ran a multi-tenant clinical records SaaS: ~1,400 clinic tenants, one database per tenant for hard data isolation, and a compliance mandate that patient identifiers (SSN-equivalent national IDs) be encrypted such that no operator – theirs or the cloud’s – could read them, while clinicians still needed to search patients by partial name and ID. Two constraints collided. Classic Always Encrypted gave them confidentiality but broke LIKE search; running 1,400 standalone database SKUs would have multiplied their bill roughly tenfold over their actual aggregate load.

They solved it as a stack. Tenants went into elastic pools (a few pools of ~200 databases each, sized from real Azure Monitor vCore-percentage telemetry rather than per-tenant peaks), which let them provision for aggregate peak and cut the dedicated-SKU cost projection by about 80%. The national-ID columns moved to Always Encrypted with secure enclaves, RANDOMIZED encryption with a VBS enclave and Azure Attestation – so clinicians’ partial-match queries ran inside the enclave on plaintext that never left protected memory, and DBAs querying the same column saw only ciphertext. The patient-record audit trail became an append-only ledger table with automatic digest upload to immutable storage, giving auditors a sp_verify_database_ledger procedure instead of a trust-us assertion.

The trap they hit was the attestation policy, not the crypto. Their enclave worked perfectly in dev but every CI integration test failed with attestation errors – because the CI runners’ connection string pointed at the dev region’s attestation URL while talking to a test region’s database, and attestation is region-bound. The fix was to template the attestation URL per environment and gate deployment on a smoke test that actually issues an enclave query:

-- CI smoke gate: must succeed THROUGH the enclave or the deploy fails.
-- A range/LIKE predicate on a RANDOMIZED column only works if attestation succeeded.
IF NOT EXISTS (
    SELECT 1 FROM dbo.Customer WHERE [SSN] LIKE N'%0000'  -- enclave-evaluated
)
    THROW 50010, 'Enclave query returned nothing -- attestation likely misconfigured.', 1;

The lesson generalises: with enclaves, the encryption is rarely what breaks – the attestation wiring is, and it must be environment-aware and smoke-tested as a hard gate, not assumed.

Checklist

• Hyperscale provisioned with --ha-replicas sized for read load and --zone-redundant true for production
• Read-scale workloads isolated onto named replicas with their own SKU and least-privilege logins
• PITR restore rehearsed end to end, not just assumed from the snapshot architecture
• Multi-tenant databases consolidated into elastic pools sized from Azure Monitor telemetry, with db-max-capacity guardrails
• Audit/compliance tables use append-only ledger; mutable-but-traceable tables use updatable ledger
• Ledger automatic digest upload configured to immutable storage; sp_verify_database_ledger runs on a schedule
• Sensitive columns use Always Encrypted with secure enclaves (RANDOMIZED) where rich queries are required
• Column master key marked ENCLAVE_COMPUTATIONS; attestation URL templated per environment and smoke-tested
• Public network access disabled; access only via Private Endpoint with the privatelink DNS zone
• Microsoft Entra-only authentication enabled; SQL logins eliminated; managed identities granted least-privilege roles (no db_owner)
• Query Store in READ_WRITE with sane retention; automatic tuning (FORCE_LAST_GOOD_PLAN) enabled
• Cross-region failover group with managed listener; apps connect to the listener FQDN, never a regional server name
• Region failover drilled quarterly; reporting routed at the read-only listener so it follows the secondary