9. Glossary and Cheatsheet
The ten-second lookup.
Glossary
Active geo-replication — Asynchronous readable secondary databases in other regions, failed over manually, per database.
Always Encrypted — Client-side column encryption where keys never reach the service; protects against a privileged DBA, at the cost of severe query restrictions on encrypted columns.
Auto-pause delay — Minutes of inactivity after which a serverless database deallocates compute.
Set to -1 to disable pausing entirely.
Azure Hybrid Benefit — Discount for bringing SQL Server licences with Software Assurance. vCore model only.
Azure SQL Managed Instance — Sibling product giving near-full SQL Server instance compatibility inside your VNet. The answer when instance-scope features block you.
Business Critical — vCore service tier with four replicas on local NVMe SSD in an Always On availability group, plus a free readable secondary. DTU-model equivalent: Premium.
Change Data Capture (CDC) — Before/after row change records in change tables. ⚠️ verify current tier support.
Change Tracking — Lightweight record of which rows changed since a version; enough for a sync job.
Collation — Character set and sort/comparison rules. Set at creation; changing it forces replacement of the database.
Connection policy — Redirect (client connects straight to the node on ports 11000–11999, lower
latency), Proxy (all traffic through the gateway on 1433), or Default.
Contained database user — A user created inside the database with no server login; required for managed-identity access and portable across failover.
DTU (Database Transaction Unit) — Opaque blended unit of CPU, memory and I/O in the older purchasing model (Basic / Standard / Premium). eDTU is the elastic-pool equivalent.
Dynamic data masking — Presentation-layer masking of results for unprivileged users. Not a security boundary — values are inferable.
Elastic Jobs — Scheduled T-SQL execution across one or many databases; the replacement for SQL Agent, which does not exist here.
Elastic pool — Shared compute allocation for a set of databases on one logical server, with per-database min/max caps.
Failover group — Grouping over databases providing a listener endpoint that follows the primary, plus optional automatic failover. Applications should connect to the listener, not the server name.
General Purpose — Default vCore service tier: one compute replica, data files on remote Azure Premium Storage. DTU-model equivalent: Standard.
Geo-restore — Restore from geo-redundant backups into another region; slower than a failover group, and the fallback when you don't have one.
Hyperscale — vCore service tier with the engine decomposed into compute nodes, a log service, and page servers. Multi-TB sizes and near-constant-time backup/restore. Historically hard to leave.
Logical server (Microsoft.Sql/servers) — A DNS name, an administrator identity, firewall rules,
auditing configuration and a private endpoint attachment. No compute, no cost.
Long-term retention (LTR) — Weekly/monthly/yearly backup copies kept for years, billed separately, and not deleted when the database is deleted.
master — System database exposing logins, firewall rules and sys.databases. Not usable for
your own objects.
Point-in-time restore (PITR) — Restore to any second within the retention window. Always creates a new database.
Private endpoint — A NIC in your subnet with a private IP mapped to the sqlServer
sub-resource, paired with the privatelink.database.windows.net DNS zone.
Provisioned compute — Fixed vCore allocation billed per hour regardless of use. The alternative to serverless.
Query Store — On by default; retains query plans and their runtime statistics. The single most useful diagnostic surface this service has.
RBPEX — Resilient Buffer Pool Extension; the local SSD cache in front of page servers in Hyperscale.
RCSI (Read Committed Snapshot Isolation) — On by default here, unlike a default on-premises SQL Server install. Changes blocking behaviour for migrating workloads.
Serverless compute — Autoscaling vCore range billed per vCore-second, with auto-pause to zero compute. Costs more per vCore-second than provisioned, so it only saves money if it actually pauses.
Service tier — General Purpose / Business Critical / Hyperscale (vCore), or Basic / Standard / Premium (DTU). Selects a storage architecture, not just a speed.
sku_name — The Terraform/ARM string encoding tier, compute model, hardware family and size:
GP_S_Gen5_2 = General Purpose, Serverless, Gen5, 2 vCores.
SQL Data Warehouse / Synapse dedicated SQL pool — A different service. Shares T-SQL, shares no architecture. Not this.
TDE (Transparent Data Encryption) — Encryption at rest, on by default, platform-managed key unless you configure a customer-managed key in Key Vault.
vCore — Virtual core on a named hardware family; the modern purchasing model, and the only one supporting Hyperscale, Serverless, and Azure Hybrid Benefit.
Virtual network rule — Allows a subnet via a service endpoint; traffic still uses the public endpoint address. Weaker than a private endpoint.
Zone redundancy — Spreads replicas across availability zones in a region. Synchronous, zero data loss, and the best resilience value on the price list.
Cheatsheet — the commands you'll actually type
# --- provision ------------------------------------------------------------
az sql server create -g $RG -n $SRV -l $LOC --enable-ad-only-auth \
--external-admin-principal-type Group --external-admin-name "sql-admins" --external-admin-sid $GID
az sql db create -g $RG -s $SRV -n $DB --edition GeneralPurpose \
--compute-model Serverless --family Gen5 --capacity 2 --min-capacity 0.5 --auto-pause-delay 60
# --- inspect --------------------------------------------------------------
az sql db list -g $RG -s $SRV -o table
az sql db show -g $RG -s $SRV -n $DB --query "{sku:currentSku.name, size:maxSizeBytes, zone:zoneRedundant}"
az sql db list-editions -l $LOC --edition GeneralPurpose --query "[].supportedServiceLevelObjectives[].name" -o tsv
# --- scale (online; brief connection drop at the end) ---------------------
az sql db update -g $RG -s $SRV -n $DB --service-objective GP_Gen5_4
az sql db update -g $RG -s $SRV -n $DB --zone-redundant true
# --- lock it down ---------------------------------------------------------
az sql server update -g $RG -n $SRV --enable-public-network false
az sql server ad-only-auth enable -g $RG -n $SRV
az sql server firewall-rule list -g $RG -s $SRV -o table # audit what's open
# --- restore (always lands as a NEW database) -----------------------------
az sql db restore -g $RG -s $SRV -n $DB --dest-name ${DB}-restored --time "2026-07-28T14:30:00Z"
az sql db rename -g $RG -s $SRV -n $DB --new-name ${DB}-damaged
az sql db rename -g $RG -s $SRV -n ${DB}-restored --new-name $DB
# --- geo / failover -------------------------------------------------------
az sql failover-group create -g $RG -s $SRV -n $FOG --partner-server $SRV_DR --add-db $DB
az sql failover-group set-primary -g $RG -s $SRV_DR -n $FOG # planned failover, no data loss
# --- long-term retention (survives database deletion — check this) --------
az sql db ltr-policy set -g $RG -s $SRV -n $DB --weekly-retention P4W --yearly-retention P7Y --week-of-year 1
az sql db ltr-backup list -l $LOC --server $SRV --database $DB -o table
# --- connect (Entra auth, no password) ------------------------------------
sqlcmd -S ${SRV}.database.windows.net -d $DB -G -Q "SELECT @@VERSION;"
# --- teardown -------------------------------------------------------------
az group delete -n $RG --yes --no-waitThe T-SQL worth memorising
-- What is this database using, right now? (20s granularity, ~1h 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's running, and what's blocking what?
SELECT r.session_id, r.status, r.wait_type, r.wait_time, r.blocking_session_id, t.text
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID;
-- Grant an app's managed identity the least it needs
CREATE USER [id-orders-api-prod] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [id-orders-api-prod];
ALTER ROLE db_datawriter ADD MEMBER [id-orders-api-prod];
-- Who has access to this database?
SELECT p.name, p.type_desc, r.name AS role_name
FROM sys.database_principals p
LEFT JOIN sys.database_role_members m ON m.member_principal_id = p.principal_id
LEFT JOIN sys.database_principals r ON r.principal_id = m.role_principal_id
WHERE p.type NOT IN ('R') AND p.name NOT LIKE '##%';
-- Current service tier, from inside the database
SELECT DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') AS slo,
DATABASEPROPERTYEX(DB_NAME(), 'Edition') AS edition;Connection strings
# Managed identity — the target state, no secret anywhere
Server=tcp:sql-orders-prod.database.windows.net,1433;Database=sqldb-orders-prod;
Authentication=Active Directory Managed Identity;User Id=<client-id>;
Encrypt=True;TrustServerCertificate=False;ConnectRetryCount=3;ConnectRetryInterval=10;
# Read replica (free on Business Critical, named replicas on Hyperscale)
...;ApplicationIntent=ReadOnly;
# Failover group listener — survives a regional failover with no config change
Server=tcp:fog-orders.database.windows.net,1433;...Resource ID shape
/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server}
/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server}/databases/{db}
/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server}/elasticPools/{pool}This is the string every role assignment scope, policy assignment, diagnostic setting target, and error message is written against.
Limits worth knowing — and the scope each is counted at
| Limit | Scope | Hard/soft |
|---|---|---|
| Max vCores / DTUs | Per database or elastic pool | Hard — set by tier and hardware family |
| Max database size | Per database | Hard — very different for Hyperscale |
| Max concurrent workers | Per database or elastic pool | Hard — proportional to compute; 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 logical server | Hard |
| Logical servers | Per subscription, per region | Soft — raise by request |
| Total vCore quota | Per subscription, per region, per tier family | Soft — fails a fresh apply with a permissions-looking error |
⚠️ Every number above varies by region, tier, and subscription type. Verify against current Azure docs before designing to any of them. The scopes are stable; the values are not.
Error numbers worth recognising
| Error | Means | Do |
|---|---|---|
| 40613 | Database not currently available (failover, scaling, resume) | Retry with backoff — expected, not exceptional |
| 40615 | Client IP not allowed by the firewall | Read the IP in the message before touching credentials |
| 18456 | Login failed | Genuine auth problem — different from 40615 |
| 10928 / 10929 | Resource ID limit reached (workers/sessions) | Check the connection pool before scaling |
| 40501 | Service is busy (throttling) | Retry with backoff |
| 49918 / 49919 / 49920 | Cannot process request, not enough resources | Retry with backoff |
| 4060 | Cannot open database requested by the login | Usually a missing contained user or wrong database name |
That's the topic. Back to the Azure SQL Database overview, or on to another topic from the article's main page.
← Back to the Azure SQL Database overview · ← Previous: Interview Questions