Azure SQL Database best practices: a production checklist for design, security, performance, resilience and cost

Azure SQL Database takes care of patching, backups and high availability. It does not decide your tier, network exposure, identity model, DR or cost. Those are where production problems come from.

Use this checklist when you build a new database, or review an existing one. Each section ends with the commands to apply or check the setting.

#1. Choose the right purchasing model and tier

Best practice:

• Prefer vCore for production. It gives clear CPU and memory, Azure Hybrid Benefit and reserved capacity.
• Size from a measured baseline, not the on-premises server size (see the sizing post in the SQL Server series).
• Turn on zone redundancy where the region supports it.
• Set backup storage redundancy deliberately: geo-redundant if you rely on geo-restore, zone or local otherwise.
• Check the current size, IO and connection limits for your tier in the documentation. They change with hardware generations.

az sql db create -g rg-app-prod -s sql-app-prod-weu -n appdb \
  --edition GeneralPurpose --family Gen5 --capacity 4 \
  --zone-redundant true --backup-storage-redundancy Zone \
  --maint-config-id SQL_WestEurope_DB_1

#2. Network: no public exposure

• Use a private endpoint for the logical server, with the privatelink.database.windows.net private DNS zone linked to your VNets.
• Disable public network access once the private path works.
• If public access must stay on, use specific firewall rules only. Never 0.0.0.0–255.255.255.255, and avoid "Allow Azure services" unless you understand that it allows any Azure tenant's traffic through the firewall.
• Set the minimum TLS version to 1.2 or higher.
• Inside Azure, use the Redirect connection policy for lower latency. Allow outbound 11000–11999 to the SQL service tag (or the private endpoint) as well as 1433.

az sql server update -g rg-app-prod -n sql-app-prod-weu --enable-public-network false --minimal-tls-version 1.2
az sql server conn-policy update -g rg-app-prod -s sql-app-prod-weu --connection-type Redirect

#3. Identity: Microsoft Entra only

• Set a Microsoft Entra group as the server admin, not a person.
• Enable Microsoft Entra-only authentication. That disables SQL logins, including the SQL admin.
• Applications connect with managed identities. No passwords in connection strings.
• Grant least privilege with database roles. Use db_owner only for deployment identities.

az sql server ad-admin create -g rg-app-prod -s sql-app-prod-weu --display-name "SQL-Admins-Prod" --object-id 00000000-0000-0000-0000-000000000000
az sql server ad-only-auth enable -g rg-app-prod -n sql-app-prod-weu

-- in the user database: the app's managed identity
CREATE USER [app-orders-prod] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [app-orders-prod];
ALTER ROLE db_datawriter ADD MEMBER [app-orders-prod];
GRANT EXECUTE ON SCHEMA::app TO [app-orders-prod];

#4. Data protection and auditing

• TDE is on by default. Use a customer-managed key in Key Vault, with purge protection, only if policy requires it.
• Use Always Encrypted for columns that even DBAs must not read, such as card or national ID numbers.
• Add dynamic data masking and row-level security for least-privilege reads.
• Turn on auditing at server level and send it to Log Analytics (or storage) with a retention that meets policy.
• Enable Microsoft Defender for SQL for vulnerability assessment and threat alerts. Review the findings monthly.
• Consider ledger tables for tamper-evident data, such as audit trails or financial records.

az sql server audit-policy update -g rg-app-prod -n sql-app-prod-weu --state Enabled \
  --log-analytics-target-state Enabled \
  --log-analytics-workspace-resource-id /subscriptions/0000/resourceGroups/rg-mon/providers/Microsoft.OperationalInsights/workspaces/law-prod

#5. Performance

• Query Store is on by default. Keep it in READ_WRITE, and check that it isn't stuck read-only because it's full.
• Automatic tuning: FORCE_LAST_GOOD_PLAN is on by default. Turn on CREATE_INDEX / DROP_INDEX only if you review what it does, or leave them as recommendations.
• Use the latest compatibility level your application supports, and test before changing it.
• Offload reports to the read replica (Business Critical, Hyperscale or geo-secondary) with ApplicationIntent=ReadOnly.
• Batch work, use set-based statements, and avoid chatty row-by-row calls. Network round trips cost more in the cloud.

-- current resource use (last hour, 15-second samples)
SELECT TOP 20 end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent, max_worker_percent
FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

-- Query Store health and automatic tuning state
SELECT actual_state_desc, desired_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options;
SELECT name, desired_state_desc, actual_state_desc FROM sys.database_automatic_tuning_options;

-- what automatic tuning recommends
SELECT name, reason, score, JSON_VALUE(details, '$.implementationDetails.script') AS script FROM sys.dm_db_tuning_recommendations;

If you're hitting limits, see the post on Azure SQL Database high CPU and throttling before you scale up.

#6. Application resilience: retry logic

Azure SQL Database reconfigures during maintenance, scaling and failover. Connections drop for a few seconds. Applications must retry transient errors:

• Use the retry support in your driver or ORM (for example SqlClient configurable retry logic, or EF Core EnableRetryOnFailure), with exponential backoff.
• Use connection pooling, and set the connect timeout to 30 s or more.
• Make writes idempotent, so retrying a command is safe.
• Choose a maintenance window (weekday or weekend night) so planned events happen off-peak.

#7. Backups, restore and DR

• Point-in-time restore: set the retention to what the business needs (1–35 days; default 7).
• Long-term retention for compliance, for example weekly for 4 weeks, monthly for 12 months, yearly for 5 years.
• Geo-DR: put critical databases in a failover group, so applications use one listener name that follows the primary.
• Test restores every quarter: PITR to a new database, check row counts, and time it against your RTO.
• Put a resource lock (CanNotDelete) on production servers. A deleted server takes its databases with it, so recovery then depends on deleted-database restore and backup retention.

az sql db str-policy set -g rg-app-prod -s sql-app-prod-weu -n appdb --retention-days 14 --diffbackup-hours 12
az sql db ltr-policy set -g rg-app-prod -s sql-app-prod-weu -n appdb --weekly-retention P4W --monthly-retention P12M --yearly-retention P5Y --week-of-year 1

az sql failover-group create -g rg-app-prod -s sql-app-prod-weu -n fog-app-prod \
  --partner-server sql-app-prod-neu --partner-resource-group rg-app-dr \
  --add-db appdb --failover-policy Automatic --grace-period 1

az lock create -g rg-app-prod -n no-delete-sql --lock-type CanNotDelete \
  --resource-type Microsoft.Sql/servers --resource sql-app-prod-weu

Applications connect to fog-app-prod.database.windows.net (read-write), and to fog-app-prod.secondary.database.windows.net for read-only work. See the failover groups post for planned versus forced failover.

#8. Monitoring and alerts

• Send diagnostic settings (metrics plus SQLInsights, QueryStoreRuntimeStatistics, Errors, Deadlocks, Timeouts, Blocks) to Log Analytics.
• Alert on: CPU > 80% for 15 min, data IO and log write > 80%, storage used > 85%, workers > 80%, deadlocks, failed connections, and failover group health.
• Use database watcher where it's available, or your existing SQL monitoring tool, for query-level history across many databases.

AzureDiagnostics
| where Category == "Deadlocks" and TimeGenerated > ago(1d)
| project TimeGenerated, Resource, deadlock_xml_s

az monitor metrics alert create -g rg-app-prod -n sql-appdb-storage-85 \
  --scopes $(az sql db show -g rg-app-prod -s sql-app-prod-weu -n appdb --query id -o tsv) \
  --condition "avg storage_percent > 85" --window-size 15m --evaluation-frequency 5m \
  --action ag-dba-oncall

#9. Maintenance jobs (there is no SQL Agent)

• Statistics: auto-create and auto-update are on. Add a scheduled UPDATE STATISTICS for large, frequently changing tables.
• Index maintenance: run only where it helps, such as heavy fragmentation on large indexes used for scans. Use resumable online rebuilds.
• Schedule with elastic jobs, Azure Automation, or your existing scheduler. Ola Hallengren's scripts work on Azure SQL Database.
• Shrinking is rarely needed. Never schedule a regular shrink.

ALTER INDEX ix_orders_customer ON app.orders REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

#10. Cost

• Azure Hybrid Benefit on vCore provisioned databases if you have SQL Server licences with Software Assurance.
• Reserved capacity (1 or 3 years) for steady production compute.
• Serverless for intermittent workloads; check the auto-pause delay and the cold-start impact on the app.
• Elastic pools to share capacity between many databases with different peaks.
• Review utilisation monthly, using sys.resource_stats in master (about 14 days of history) and Azure Monitor. Scale down where the 95th percentile is low.
• Tag every server and database with env, app, owner and costCenter.

-- in master: daily average and peak per database
SELECT database_name, CAST(start_time AS date) AS day, AVG(avg_cpu_percent) AS avg_cpu, MAX(avg_cpu_percent) AS peak_cpu
FROM sys.resource_stats
WHERE start_time > DATEADD(day, -14, GETUTCDATE())
GROUP BY database_name, CAST(start_time AS date) ORDER BY database_name, day;

See the Azure SQL cost transformation post for a worked example.

#11. Governance and deployment

• Infrastructure as code (Bicep or Terraform) for servers, databases, private endpoints, auditing and alerts.
• Schema changes through SQL projects (DACPAC) or migrations in CI/CD, never by hand in production.
• Azure Policy: deny public network access, require auditing and Entra-only authentication, require a minimum TLS version.
• Naming standard, for example sql-{app}-{env}-{region} for servers and {app}db for databases.
• One logical server per application and environment. Don't mix prod and non-prod on one server.

#Quick review: the ten things to check first

1. vCore tier sized from a measured baseline; zone redundant
2. Public network access disabled; private endpoint and DNS work
3. Entra-only authentication; apps use managed identities
4. Auditing and Defender for SQL on
5. Query Store read-write; automatic plan correction on
6. App retry logic for transient errors
7. PITR and LTR retention set; restore tested this quarter
8. Failover group for critical databases; lock on the server
9. Alerts on CPU, IO, storage, workers and deadlocks
10. Hybrid Benefit or reservations applied; monthly right-sizing

Tip

Run this checklist as an Azure Policy initiative plus a quarterly review. Policies stop new drift. The review catches what policy can't, such as untested restores, missing retry logic and oversized tiers.