7. Production

The difference between "I made it work in the portal" and "I run this, and someone else can run it when I'm on holiday". Five pillars, always the same five.

[Image Prompt: 2D minimalistic diagram of five production pillars — security, cost, scaling, observability, and reliability — arranged around a central Azure SQL Database resource, flat design, clean vector art style, white background]

Security

Identity: the whole game

Ranked from worst to best, and most estates are still somewhere near the top of this list:

1. A SQL login and password in a config file. The default path, and the one every credential leak starts from.
2. The same, but the password lives in Key Vault. Better — the secret is centralised and rotatable — but there is still a password, it still gets copied into memory and logs, and rotation is still a job someone forgets.
3. Entra authentication with a service principal and a client secret. Now it's an Entra identity, but you've reinvented the password.
4. Managed identity + contained database user. No secret exists. Rotation is Azure's problem. Revocation is deleting the user. This is the target state.

Then close the door behind you:

# Entra-only authentication: SQL logins stop working entirely,
# which also removes the "reset the SQL admin password" escalation path.
az sql server ad-only-auth enable --resource-group $RG --name $SQL_SERVER

Set the Entra admin to a group, governed by Privileged Identity Management so membership is time-bound and approved, not permanent.

Authorisation: below db_owner

The default reflex is db_owner and it is almost always too much. The useful ladder:

Need	Grant
Read everything	db_datareader
Read and write everything	db_datareader + db_datawriter
Only what the app should do	GRANT EXECUTE ON SCHEMA::app — stored procedures only, no direct table access
Read a subset of rows	Row-level security policy on the table
Hide columns from support staff	Column-level DENY, or dynamic data masking (presentation only — see below)
Change schema	db_ddladmin, for the deployment identity only, never the runtime app

The separation that matters most: the identity that runs migrations and the identity the application uses at runtime should be different, and the runtime one should not be able to change schema. If the app's identity can DROP TABLE, a SQL injection bug becomes a data-loss incident rather than a data-disclosure incident.

Network

• public_network_access_enabled = false plus a private endpoint. Everything else is a compensating control for not having done this.
• If you must keep the public endpoint, never enable Allow Azure services and resources to access this server. It permits any IP in Azure — including other tenants' resources. It is a checkbox that reads like a scoping control and behaves like "public".
• Minimum TLS 1.2, and prefer Encrypt=True;TrustServerCertificate=False in every connection string. TrustServerCertificate=True in a connection string is a man-in-the-middle waiting to happen, and it appears in a depressing number of tutorials.
• Outbound firewall rules exist too, and matter if the database can initiate connections (external tables, OPENROWSET against storage).

Data protection

• TDE is on by default with a platform-managed key. Move to a customer-managed key in Key Vault only if you have a real requirement — it buys you the ability to revoke access to your own data, and it buys you a new way to take production down if the key or vault becomes unavailable. Enable purge protection and soft delete on that vault, and understand that a deleted key means an inaccessible database.
• Always Encrypted for genuinely sensitive columns where even a DBA must not see plaintext. Price it honestly: equality-only comparisons on deterministic columns, no range queries, no LIKE, and significant application changes.
• Dynamic data masking is not a security boundary. It masks the presentation of results for unprivileged users; a determined user can infer values with WHERE predicates. Use it to reduce casual exposure, never to satisfy a compliance control on its own.
• Auditing to a Log Analytics workspace or storage account, and Microsoft Defender for SQL for vulnerability assessment and anomalous-access alerting. Both are off by default.

Cost

What you actually pay for

Component	Billed as	Notes
Compute	vCores × hours (provisioned) or vCore-seconds (serverless)	The dominant line, usually by a lot
Storage	GB/month of allocated max size	Cheap relative to compute; not free
Backup storage	GB/month beyond the included allowance	The stealth line item. Grows with retention × database size × churn
Long-term retention	GB/month, separately	Outlives the database. Delete deliberately
Geo-replication	A full second database's compute	A geo-secondary costs roughly what the primary costs
Zone redundancy	A premium on compute	Cheap insurance relative to Business Critical

The traps, in the order they bite

1. Business Critical chosen for resilience. You are buying four copies of the compute for a latency benefit you may not need. Zone-redundant General Purpose delivers most of the availability story at a fraction of the cost. Choose Business Critical for latency or for the free read replica — not as a synonym for "important".
2. Serverless that never pauses. The vCore-second rate carries a premium over provisioned. If the database is warm 24 hours a day because a health check pings it, you are paying more for less. Measure actual pause hours before assuming savings.
3. Geo-replication left on after a migration. A forgotten secondary is a full second bill, indefinitely, and nothing in the portal shouts about it.
4. Over-allocated max size. You are billed on allocated storage, not used.
5. Long-term retention on a large, churning database. Seven years of yearly backups on a 2 TB database is a real number.
6. Non-production running at production size. Dev at GP_S_Gen5_1 with a 60-minute auto-pause costs a rounding error. Dev at BC_Gen5_4 costs a salary.

Concrete optimisations

• Elastic pools for many small databases with staggered peaks — the single biggest saving available in multi-tenant SaaS.
• Reserved capacity (1 or 3 years) on steady production compute, and Azure Hybrid Benefit if you own SQL Server licences with Software Assurance. These stack, and together they are a large discount ⚠️ verify current discount levels against current Azure pricing.
• Dev/test subscription pricing for non-production.
• Auto-pause plus a min_capacity of 0.5 for anything not on the critical path.
• Right-size from data, not from feel — sys.dm_db_resource_stats and the Azure Advisor recommendations tell you what the database actually used.

What keeps billing when nothing is using it: a provisioned database bills every hour whether or not a query runs; storage and backup storage bill regardless; a geo-secondary bills regardless. Only a paused serverless database stops billing compute — and it still bills storage. Azure has more always-on, plan-shaped billing than AWS does, and this is one of the places it shows.

Scaling and limits

How it scales

Vertically and online: change the SKU and the platform moves you, with a brief connection drop at the failover moment (see Architecture). Automate it if the load is predictable — scale up before the nightly batch, down after — with a scheduled Automation runbook, a Logic App, or a Function calling az sql db update.

Horizontally: read scale-out on Business Critical and Hyperscale, elastic pools for many databases, and sharding as an application design decision the platform will not make for you.

The limits, and the scope each is counted at

A number without a scope is useless in Azure. The ones worth internalising:

Limit	Scope	Hard or soft
Max vCores / DTUs	Per database (or per pool)	Hard, set by tier and hardware family
Max database size	Per database	Hard, set by tier
Max concurrent workers	Per database or elastic pool	Hard, proportional to compute size — the most common invisible ceiling
Max concurrent sessions	Per database or elastic pool	Hard
tempdb size	Per database, derived from compute	Hard, not separately configurable
Databases per logical server	Per server	Hard
Logical servers	Per subscription, per region	Soft — raise by request
Total vCore quota	Per subscription, per region, per tier family	Soft — and the one that fails a Terraform apply in a fresh subscription with an error that reads like a permissions problem

⚠️ Every specific figure varies by region, tier, and subscription type. Verify against current Azure docs before designing to any of them; do not treat a remembered number as fact.

Raise soft quotas via Subscription → Usage + quotas in the portal, az quota, or a support request. Do it before the migration weekend, not during it.

Observability

Diagnostic settings are not on by default. A database with no diagnostic setting produces almost no durable telemetry, and you will discover this while trying to explain last Tuesday's incident.

Route these to a Log Analytics workspace:

Category	Answers
SQLInsights	Intelligent Insights — the platform's own analysis of what degraded
QueryStoreRuntimeStatistics	Which queries, how often, how expensive
QueryStoreWaitStatistics	What they waited on
Errors	Failed queries and login failures
Timeouts, Blocks, Deadlocks	The three classic OLTP pathologies
AutomaticTuning	What the platform changed on your behalf
Metrics: Basic	CPU, DTU/vCore percent, storage, connections, deadlocks

The metrics worth alerting on

• CPU percentage sustained above ~80% ⚠️ tune to your workload — the leading indicator of everything else.
• Log IO percentage — the ceiling on write-heavy workloads, and the one people forget exists.
• Data IO percentage — the General Purpose remote-storage tell.
• Worker percentage / sessions percentage — approaching the invisible ceiling above.
• Deadlocks — a rate, not a threshold; any sustained increase is a code change's fault.
• Failed connections — the signal for a firewall change, an expired secret you shouldn't have, or a serverless resume problem.
• Storage percentage above ~85% — hitting the max size stops writes dead.

The KQL you'll actually run

// The most expensive queries in the last 24 hours, by total CPU
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category == "QueryStoreRuntimeStatistics"
| where TimeGenerated > ago(24h)
| extend cpu_ms = todouble(cpu_time_d) / 1000
| summarize total_cpu_sec = sum(cpu_ms) / 1000,
            executions   = sum(toreal(count_executions_d)),
            avg_duration_ms = avg(todouble(duration_d) / 1000)
        by query_hash_s
| top 20 by total_cpu_sec desc

// Errors, timeouts, blocks and deadlocks over time — the shape of a bad afternoon
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Category in ("Errors", "Timeouts", "Blocks", "Deadlocks")
| summarize count() by Category, bin(TimeGenerated, 15m)
| render timechart

And the in-database views that need no configuration at all — the first thing to run during an incident:

-- Resource use, 20-second granularity, ~1 hour of history
SELECT TOP 60 end_time, avg_cpu_percent, avg_data_io_percent,
       avg_log_write_percent, max_worker_percent, max_session_percent
FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

-- What is running right now, and what is it waiting on?
SELECT r.session_id, r.status, r.wait_type, r.wait_time, r.blocking_session_id,
       DB_NAME(r.database_id) AS db, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;

Query Store is on by default and is the single most useful diagnostic surface this service has — it retains plans and their runtime statistics, which is how you prove a query got slower after a plan change rather than after a code change. Application Insights on the calling application closes the loop, because "the database is slow" is usually "one query in one endpoint is slow".

Reliability

Choose the level deliberately

Requirement	Configuration	Recovery characteristic
Survive a node failure	Any tier, by default	Automatic, seconds to tens of seconds
Survive a datacentre failure	Zone redundancy on (General Purpose, Business Critical, Hyperscale in supported regions)	Automatic, in-region, no data loss
Survive a region failure	Failover group with a geo-secondary	Manual or automatic; non-zero RPO on an unplanned failover
Recover from human error	Point-in-time restore	To a new database, inside the retention window
Meet a compliance retention rule	Long-term retention	Weekly/monthly/yearly, restored as a new database

Zone redundancy is the best value on this table and it is a single boolean. Turn it on for production unless the region doesn't support it.

Backup, and the part people skip

Backups are automatic. Restores are not tested. The drill worth rehearsing, written down:

# Restore to a point in time — always lands as a NEW database
az sql db restore \
  --dest-name sqldb-orders-prod-restored \
  --resource-group rg-sql-orders-prod \
  --server sql-orders-prod \
  --name sqldb-orders-prod \
  --time "2026-07-28T14:30:00Z"

Then the step that actually matters and that nobody has practised: renaming the restored database into place. Rename the damaged one aside, rename the restored one in, and reconnect. Do it in a non-production environment with a stopwatch, once a quarter, and write down how long it took. That number is your real RTO; everything else is an aspiration.

For a regional drill, run a planned failover of the failover group and then fail back. A planned failover drains the log first, so it is non-destructive — which is exactly why it is safe to practise, and why practising it does not prove your unplanned RPO.

Connection resilience is part of reliability

Transient failures are expected, not exceptional — planned failovers, scaling, and maintenance all produce them. An application without retry logic will have an outage every time Azure does routine maintenance, and the postmortem will incorrectly blame Azure.

• Use the driver's built-in retry (ConnectRetryCount / ConnectRetryInterval in .NET, EnableRetryOnFailure in EF Core).
• Exponential backoff with jitter, and a cap.
• Treat error numbers 40613, 40197, 40501, 49918/49919/49920, 4060 and 10928/10929 as retryable.
• Connect to the failover group listener, not the server name.
• Keep connection pooling on and connection lifetimes bounded, so a failover doesn't leave the pool full of dead connections.

What you should be able to do now

Take a database someone else built and, in an afternoon, tell them: which of the five pillars is weakest, what it will cost to fix, and which single change (almost always "disable public access" or "turn on zone redundancy") gives the most safety per pound.

---

Next: Interview Questions →

← Back to the Azure SQL Database overview · ← Previous: Integrations