Azure SQL DBA Glossary & Quick Reference
One-line definitions for every key term used across this wiki. Ctrl+F to find what you need.
Deployment & Architecture
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| Azure SQL Database | Fully managed PaaS database engine — no OS access | Most common Azure SQL deployment. Microsoft manages everything except your data and queries. | Azure SQL Options |
| SQL Managed Instance | PaaS with near 100% SQL Server compatibility — SQL Agent, CLR, cross-DB queries | Best for lift-and-shift migrations from on-prem SQL Server. | Azure SQL Options |
| SQL Server on Azure VM | Full SQL Server on IaaS — you manage everything | Maximum control. Required for features not available in PaaS (SSIS, linked servers to non-Azure). | SQL VM Configuration |
| Logical Server | Administrative container for Azure SQL databases — holds firewall rules, Entra admin, auditing | Not a running SQL instance — just a management boundary. Multiple databases share one logical server. | Azure SQL Database |
| Elastic Pool | Multiple databases sharing a pool of vCores or DTUs | Cost-effective for multi-tenant SaaS with spiky, non-overlapping usage patterns. | vCore vs DTU |
Purchasing Models
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| DTU | Database Transaction Unit — bundled measure of CPU + I/O + log I/O | Simple pricing, preset tiers. Can't scale components independently. | vCore vs DTU |
| vCore | Virtual core — pick exact CPU, memory, storage independently | Azure Hybrid Benefit, Serverless, Hyperscale. Recommended for most workloads. | vCore vs DTU |
| General Purpose (GP) | Cost-effective tier — remote storage, single compute node, Service Fabric HA | ~60-70% cheaper than BC. Higher I/O latency (5-10ms). No read replicas. | Azure SQL DB Config |
| Business Critical (BC) | Premium tier — 4-node Always On AG, local NVMe SSD, 1 free read replica | Sub-2ms latency, < 10s failover. Oracle RAC equivalent (shared-nothing). | Azure SQL DB Config |
| Hyperscale | Distributed storage tier — page servers, log service, up to 100 TB | Only tier > 4 TB. Near-instant backups. Up to 30 named replicas. | vCore vs DTU |
| Serverless | Auto-scaling compute that can auto-pause when idle | Pay per second of vCores used. ~1 min cold start. Best for dev/test and intermittent workloads. | Azure SQL DB Config |
High Availability & Disaster Recovery
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| RPO | Recovery Point Objective — maximum acceptable data loss | "How old can the restored data be?" Measured in seconds/minutes. | Backup & Restore |
| RTO | Recovery Time Objective — maximum acceptable downtime | "How fast must we be back online?" Measured in seconds/hours. | Backup & Restore |
| PITR | Point-in-Time Restore — restore to any second within retention (7-35 days) | Creates a NEW database. Never overwrites original. Primary defense against accidental deletion/corruption. | Backup & Restore |
| LTR | Long-Term Retention — keep weekly/monthly/yearly backups up to 10 years | For compliance requirements beyond 35-day PITR window. Full backups only (no PITR granularity). | Backup & Restore |
| Geo-restore | Restore from GRS backup copy in paired region | Free cross-region recovery. RPO up to 1 hour, RTO hours. | Backup & Restore |
| Active Geo-Replication | Async readable secondary in another region (up to 4) | Manual failover, per-database, each has own endpoint. | Failover Groups vs Geo-Rep |
| Failover Group | Managed group with auto-failover and single DNS endpoint | Recommended for production DR. Endpoint never changes on failover. | Failover Groups vs Geo-Rep |
| Availability Group (AG) | SQL Server HA feature — primary + secondaries with sync/async commit | Used on Azure VMs. Requires WSFC + ILB. Automatic failover within cluster. | SQL VM Always On |
| WSFC | Windows Server Failover Clustering — underlying cluster for AGs | Provides health monitoring, quorum, automatic failover. Required for AG on VMs. | SQL VM Always On |
| Quorum | Voting mechanism that prevents split-brain in a cluster | Determines which nodes form majority. Cloud Witness recommended for Azure. | Log Shipping & FCI |
| Cloud Witness | Azure Blob-based quorum witness for WSFC clusters | No extra VM needed. Region-independent. Recommended tiebreaker for 2-node clusters. | Log Shipping & FCI |
| FCI | Failover Cluster Instance — shared-storage SQL Server HA | Requires shared disks (Azure Shared Disks or S2D). Single instance, not per-database. | Log Shipping & FCI |
| ILB | Internal Load Balancer — routes AG Listener traffic to primary in Azure | Required because Azure doesn't support gratuitous ARP. Holds the listener IP. | SQL VM Always On |
Security
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| TDE | Transparent Data Encryption — encrypts data at rest (AES-256) | ON by default in Azure SQL DB. Protects stolen files/backups. Transparent to queries. | Security Comparison |
| Always Encrypted | Column-level encryption — data encrypted before reaching SQL engine | Even DBAs see ciphertext. Client-side keys. Limited query support. | Security Comparison |
| RLS | Row-Level Security — filter rows per user/tenant via predicate function | Multi-tenant isolation. Same table, different views per user. | Security Comparison |
| DDM | Dynamic Data Masking — obfuscates column display (not encryption) | Hides PII from casual viewers. Bypassable with UNMASK permission. NOT true security. | Security Comparison |
| Managed Identity | Passwordless Azure AD identity for apps connecting to Azure resources | No stored passwords, no rotation. System-assigned or user-assigned. Best for app-to-DB auth. | Authentication |
| Entra ID | Microsoft's identity platform (formerly Azure AD) — SSO, MFA, Conditional Access | Centralized authentication for Azure SQL. Replaces SQL Auth for production. | Authentication |
| BYOK | Bring Your Own Key — customer-managed TDE key in Azure Key Vault | You control the encryption key, not Microsoft. Required for some compliance frameworks. | Encryption & Network |
| Private Endpoint | Private IP in your VNet for Azure SQL — no public internet exposure | Highest security. Public endpoint can be fully disabled. Recommended for production. | Networking |
Performance & Monitoring
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| Query Store | Built-in flight recorder — captures every query plan + runtime stats over time | Detects plan regressions. Enables FORCE_LAST_GOOD_PLAN. ON by default in Azure SQL DB. | Performance Tuning |
| DMVs | Dynamic Management Views — real-time SQL Server diagnostics | Show current sessions, waits, running queries, missing indexes. No history — use Query Store for that. | Monitoring Matrix |
| IQP | Intelligent Query Processing — optimizer features enabled by compatibility level | Batch mode on rowstore, adaptive joins, memory grant feedback. Compat 150+ = SQL 2019 features. | Azure SQL DB Config |
| MAXDOP | Max Degree of Parallelism — controls how many CPUs a query can use | Set via ALTER DATABASE SCOPED CONFIGURATION in SQL DB, sp_configure in MI/VM. | Azure SQL DB Config |
| Compatibility Level | Determines which query optimizer behavior SQL uses | Changing compat changes optimizer, not engine. Keep old compat on migration to avoid regressions. | Azure SQL DB Config |
| Extended Events | Lightweight tracing engine replacing deprecated SQL Profiler | Capture deadlocks, long queries, blocking. Lower overhead than Profiler. Works on all platforms. | Monitoring Matrix |
Automation
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| SQL Agent | Built-in job scheduler for SQL Server (VM + MI, not SQL DB) | Jobs, steps, schedules, alerts, operators. MI supports T-SQL steps only. | SQL Agent Jobs |
| Elastic Jobs | Cloud job scheduler for Azure SQL Database — T-SQL across multiple DBs | Replaces SQL Agent for SQL DB. Requires dedicated job database. | Elastic Jobs |
| Azure Automation | Cloud PowerShell/Python execution — runbooks on schedule or on-demand | No VM needed. Managed Identity auth. 500 free min/month. | Elastic Jobs |
| DACPAC | Data-tier Application Package — compiled database schema for CI/CD deployment | SqlPackage compares schema and generates diff. Standard for pipeline schema deployment. | Deployment Automation |
| Bicep | Azure IaC language — cleaner syntax than ARM JSON, compiles to ARM | Microsoft recommended for all new Azure deployments. 60-80% shorter than ARM JSON. | Deployment Automation |
| Action Group | Defines what happens when an Azure Monitor alert fires | Email, SMS, Logic App, Automation Runbook, webhook, ITSM ticket. | Alerts & Troubleshooting |
Migration
| Term | Definition | Why It Matters | Learn More |
|---|---|---|---|
| DMS | Azure Database Migration Service — managed migration tool | Online (minimal downtime) and offline migration modes. Supports SQL, MySQL, PostgreSQL, Oracle. | Migration Strategy |
| SSMA | SQL Server Migration Assistant — converts Oracle/MySQL schemas to T-SQL | Assesses migration complexity. Converts PL/SQL → T-SQL. Flags incompatible constructs. | Migration Strategy |
| BACPAC | Portable database format — schema + data in a single file | Used for import/export between Azure SQL databases or to/from on-prem. | Migration Strategy |
Exam Quick Reference
Most Tested Terms (by frequency)
| Tier | Terms |
|---|---|
| 🎯 Always know | Query Store, PITR, Failover Groups, TDE, RLS, MAXDOP, GP vs BC, vCore vs DTU |
| 📖 Know well | Always Encrypted, DDM, Elastic Jobs, Azure Automation, ILB, Quorum, LTR |
| 📚 Understand | Hyperscale, IQP, Extended Events, DACPAC, Bicep, Managed Identity, DMS |