Whether you’re walking into your first Azure DBA interview or you’re a 10-year SQL Server veteran trying to prove you’ve actually made the jump to the cloud, this list is built to cover you. In this techmixing article, I’ve split it into three tiers — Beginner, Intermediate, and Advanced — because “explain Azure SQL” means something very different to a fresher than it does to someone being interviewed for a lead DBA role.
A tip before you dive in: don’t memorize these answers word for word. Read them, understand the why behind each one, and be ready to explain it in your own words with a real example from your own experience if you have one. Interviewers can always tell the difference between recited and understood.
Beginner Level (Q1–Q17)
1. What is Azure SQL? Azure SQL is Microsoft’s family of managed relational database services built on the SQL Server engine, running in Azure. It comes in three flavors: Azure SQL Database (single database, PaaS), Azure SQL Managed Instance (near-full SQL Server compatibility, PaaS), and SQL Server on Azure VMs (IaaS, full control).
2. What’s the difference between Azure SQL Database, Managed Instance, and SQL Server on Azure VM? Azure SQL Database is a fully managed single database or elastic pool with the least administrative overhead but some feature limitations (no cross-database queries, no SQL Agent in the classic sense). Managed Instance offers near-100% SQL Server engine compatibility, including cross-database queries, SQL Agent, and CLR, while still being PaaS (automated patching/backups). SQL Server on Azure VM is just SQL Server installed on a VM you fully control — maximum compatibility, maximum admin responsibility.
3. What is a DTU? A Database Transaction Unit is a blended measure of CPU, memory, and I/O combined into a single number, used in the DTU-based purchasing model. It’s simpler to reason about for beginners but less transparent than the vCore model, where you configure CPU and memory somewhat independently.
4. What is the vCore purchasing model, and how is it different from DTU? vCore lets you choose compute and storage independently, choose your hardware generation, and apply Azure Hybrid Benefit to reuse existing SQL Server licenses for a cost discount. DTU bundles everything into one number and one tier, which is simpler but less flexible and offers no licensing reuse benefit.
5. What service tiers are available in Azure SQL Database? General Purpose (balanced, most workloads), Business Critical (highest availability and I/O performance, uses local SSD storage with synchronous replicas), and Hyperscale (for very large databases, up to 100 TB, with fast backups and independent storage/compute scaling). There’s also a Basic/Standard/Premium naming under the DTU model that maps roughly to these.
6. What is elastic pool? An elastic pool lets multiple databases share a pool of resources (eDTUs or vCores) instead of each database having its own fixed allocation. It’s cost-effective when you have many databases with unpredictable, non-overlapping usage spikes.
7. How does backup work in Azure SQL Database? Backups are fully automatic — full backups weekly, differential backups roughly every 12–24 hours, and transaction log backups every 5–10 minutes, all managed by the platform. You use Point-In-Time Restore (PITR) to recover to any point within your retention window (7–35 days, configurable), rather than manually restoring a .bak file.
8. What is Point-In-Time Restore (PITR)? PITR lets you restore a database to any specific timestamp within the retention period, creating a new database at that state, without you ever having touched a backup file directly — the platform manages the underlying backup chain.
9. Can you connect to the underlying OS in Azure SQL Database? No. Azure SQL Database is a PaaS offering — there’s no OS-level or file-system access. You interact with it purely through the database engine (T-SQL, connection strings, the Azure portal/CLI/PowerShell). Managed Instance and SQL Server on Azure VM offer progressively more underlying access, with the VM option giving full RDP access.
10. What authentication methods does Azure SQL support? SQL Authentication (username/password local to the server) and Microsoft Entra ID authentication (formerly Azure AD) — the latter supports single sign-on, multi-factor authentication, and centralized identity management, and is the recommended approach for production environments.
11. What is a firewall rule in Azure SQL, and why do you need one? By default, Azure SQL blocks all external connections. Firewall rules whitelist specific IP addresses or ranges (server-level or database-level) that are allowed to connect. There’s also an option to allow Azure services to connect, useful for App Service or Function App integration, though it should be used carefully since it’s broad.
12. What is a logical server in Azure SQL Database? The logical server is a management and connection-point construct — it holds firewall rules, Entra ID admin settings, and logical grouping for one or more databases, but it isn’t a physical server you manage; it doesn’t run its own separate compute the way an on-prem instance would.
13. How do you scale an Azure SQL Database? You can scale up/down (change service tier or compute size) with minimal downtime through the portal, T-SQL (ALTER DATABASE ... MODIFY), PowerShell, or CLI. Scaling is mostly online, though there’s a brief connection drain/reconnect at the very end of the operation.
14. What is geo-replication in Azure SQL? Active Geo-Replication creates up to four readable secondary replicas in different regions, kept in sync asynchronously, for disaster recovery and read-scale-out. Auto-failover groups build on top of this to provide automatic failover with a single, stable connection endpoint your application doesn’t need to change after a failover.
15. What is SQL Database serverless? A compute tier where the database automatically pauses during inactivity (billing drops to just storage cost) and auto-scales compute within a configured min/max range based on workload — good for intermittent or unpredictable workloads like dev/test databases.
16. What is Transparent Data Encryption (TDE)? TDE encrypts the database files at rest (data and log files, backups) automatically, with zero application changes required. It’s enabled by default on new Azure SQL databases and protects against the scenario of someone gaining access to the raw physical storage.
17. How do you connect to Azure SQL Database from SSMS? Using the server’s fully qualified domain name (<servername>.database.windows.net), port 1433, with either SQL Authentication or Microsoft Entra ID authentication, provided your client IP is allowed through the firewall rules.
Intermediate Level (Q18–Q35)
18. What is Query Store, and why does it matter in Azure SQL? Query Store automatically captures query text, execution plans, and runtime statistics over time, letting you compare a query’s performance today against last week without needing to have proactively captured a baseline yourself. It’s the backbone of Automatic Tuning and Query Performance Insight, and it’s on by default in Azure SQL Database.
19. What is Automatic Tuning, and what actions can it take? A machine-learning-driven feature that can automatically apply CREATE INDEX, DROP INDEX (for unused/duplicate indexes), and FORCE LAST GOOD PLAN (reverting to a previous good plan when a regression is detected via Query Store). Each action can be set to automatic or “recommend only,” and Azure monitors the impact of any change it makes, rolling it back if it doesn’t help.
20. What’s the difference between failover groups and active geo-replication? Active geo-replication gives you up to four secondaries and manual control over failover, with each secondary needing its own connection string. Auto-failover groups group one or more databases together, provide a single stable read-write and read-only listener endpoint, and support automatic failover based on a policy — meaning your application’s connection string doesn’t need to change after a real failover event.
21. How does Azure SQL Managed Instance differ from Azure SQL Database in terms of networking? Managed Instance is deployed inside a VNet subnet, giving you private IP addressing, network isolation via NSGs/route tables, and the ability to connect via VPN/ExpressRoute the way you would to an on-prem network. Azure SQL Database, by contrast, is a public endpoint by default (though Private Link is available to bring it into a VNet too).
22. What is Data Masking (Dynamic Data Masking) in Azure SQL? A policy-based feature that obscures sensitive column data (like a partial credit card number or masked email) from non-privileged users at query time, without altering the actual stored data — useful for limiting exposure to support staff or reporting users who don’t need to see raw sensitive values.
23. Explain Row-Level Security (RLS) in Azure SQL. RLS uses a security predicate function tied to a security policy on a table, so different users querying the exact same table and query automatically see only the rows they’re permitted to see — commonly used in multi-tenant applications where each tenant should only see their own data.
24. What is Always Encrypted, and how is it different from TDE? Always Encrypted encrypts specific sensitive columns client-side, so the data is encrypted before it ever leaves the application and the database engine itself never sees the plaintext — even a DBA with full admin rights can’t view it in plaintext. TDE, by contrast, encrypts the entire database at rest but decrypts data automatically for any authorized query — the engine itself can see plaintext data.
25. What are wait statistics, and how do you view them in Azure SQL Database? Wait stats tell you what SQL Server was waiting on while a query wasn’t actively executing on the CPU — e.g., I/O, locks, parallelism. In Azure SQL Database you can query sys.dm_db_wait_stats (database-scoped, since you don’t have server-wide visibility the way you would on-prem) or pull DatabaseWaitStatistics from diagnostic logs via KQL for historical analysis.
26. How do you troubleshoot blocking in Azure SQL Database? Query sys.dm_exec_requests and sys.dm_exec_sessions joined to find the blocking chain and head blocker, check sys.dm_os_waiting_tasks for wait types, and — since you can’t use sp_who2 the traditional way in all contexts — rely on sys.dm_exec_session_wait_stats and the diagnostic Blocks category routed through Log Analytics for a historical view.
27. What is Hyperscale, and when would you use it? Hyperscale is a service tier that decouples storage from compute, supporting databases up to 100 TB with near-instant backups (snapshot-based, not full copies) and fast storage auto-scaling. It’s the right choice for very large or fast-growing databases where traditional backup/restore times or storage limits become a real operational problem.
28. How does In-Memory OLTP work in Azure SQL Database? Memory-optimized tables and natively compiled stored procedures keep hot data structures fully in memory with optimistic, lock-free concurrency control, dramatically reducing contention for very high-throughput OLTP workloads. It’s available on Business Critical and Premium tiers, and requires available memory-optimized storage (xtp_storage_percent is the metric to watch).
29. What is the difference between a clustered and non-clustered columnstore index, and when would you use one in Azure SQL? A clustered columnstore index stores the entire table in column-oriented, highly compressed format, ideal for large analytical/reporting workloads with heavy aggregation. A non-clustered columnstore index sits alongside a normal rowstore table, letting you run analytical queries fast without giving up the row-based OLTP access pattern — common in Hybrid Transactional/Analytical Processing (HTAP) scenarios.
30. How do you migrate an on-premises SQL Server database to Azure SQL? Common paths include the Data Migration Assistant (DMA) for compatibility assessment, Azure Database Migration Service (DMS) for both offline and near-zero-downtime online migrations, backup/restore to Managed Instance (via URL, supported for Managed Instance), transactional replication for minimal-downtime cutover, and BACPAC export/import for simpler, smaller databases.
31. What is the Azure SQL Database Advisor, and what does it recommend? Built on the same engine as Automatic Tuning, it surfaces create-index, drop-index, and parameterization recommendations directly in the portal, based on actual observed query patterns from Query Store — you can review and apply recommendations manually even if you don’t want full automatic tuning enabled.
32. How does connection resiliency work in Azure SQL, and why does it matter? Because Azure SQL is a shared, multi-tenant, cloud service, transient connectivity issues (brief network blips, load-balancer failovers) are expected, not exceptional. Applications should implement retry logic with exponential backoff for transient errors (e.g., error codes 40197, 40501, 49918), rather than treating every connection failure as fatal — this is a mindset most on-prem-trained developers have to unlearn.
33. What is the difference between a database-scoped credential and a server-level login in the context of Azure SQL security? A server-level login exists at the logical server and can be granted access across multiple databases on that server; a database user (often mapped from that login, or a “contained” database user with its own password/Entra identity) exists scoped to a single database — Azure SQL Database favors contained database users since there’s no true shared “instance” the way there is on-prem.
34. What is Long-Term Retention (LTR) in Azure SQL backups? LTR lets you retain full backups for up to 10 years, independent of the standard point-in-time restore window (max 35 days), for compliance and regulatory requirements — configured as a separate policy from your normal PITR retention setting.
35. Explain how auto-failover groups handle a regional outage. If the primary region becomes unavailable and the configured grace period (default 1 hour, adjustable) elapses without recovery, the failover group automatically promotes the secondary to primary and redirects the group’s read-write listener endpoint — your application, using the failover group’s DNS name rather than the individual server name, reconnects transparently once DNS propagates.
Advanced Level (Q36–Q50)
36. How would you design a monitoring and alerting strategy for a fleet of 100+ Azure SQL databases? Enforce diagnostic settings via Azure Policy so every database routes consistently to a shared Log Analytics workspace using resource-specific tables. Use SQL Insights for a fleet-wide health view, build tiered alerts (a small number of severity-0/1 metric alerts tied to action groups with paging, everything else visible only on a Workbook), and automate the boring, repeatable remediations via Logic Apps so humans are reserved for genuine judgment calls. Review Advisor and cost data monthly rather than reactively.
37. Walk through how you’d diagnose a “the database feels slow but no threshold alert has fired” complaint. Start with Resource Health to rule out a platform-level issue, then check all four resource dimensions in Metrics Explorer — CPU, log_write_percent, physical_data_read_percent, and sessions_percent/workers_percent — since a static CPU-only alert can miss log-write or connection-cap bottlenecks entirely. Cross-reference with DatabaseWaitStatistics and Query Store’s QueryStoreRuntimeStatistics via KQL to find which specific query’s plan or duration changed, rather than assuming it’s a resource-tier problem before you’ve confirmed it.
38. What’s the difference between FORCE LAST GOOD PLAN (Automatic Tuning) and manually creating a plan guide? When would you prefer one over the other? FORCE LAST GOOD PLAN is reactive and automated — it detects a regression via Query Store and reverts to a previously good plan for you, continuously monitoring whether the revert actually helped. A manual plan guide (or OPTION (USE PLAN ...), or sp_create_plan_guide) is a deliberate, static intervention you apply when you understand exactly why a specific plan is preferable and want that decision locked in regardless of what the optimizer would otherwise choose — useful for a known parameter-sniffing problem on a business-critical query where you don’t want to wait for automatic detection.
39. Explain how Hyperscale’s storage architecture differs from General Purpose/Business Critical, and what operational implications that has. Hyperscale separates compute from a distributed, log-structured storage layer with page servers and a log service, rather than a single attached storage volume. This means backups are near-instantaneous (they’re storage snapshots, not full data copies), storage can grow far beyond traditional limits without downtime, and you get fast database copy/restore operations — but you should understand that read-scale-out replicas in Hyperscale are asynchronous and serve a different purpose than Business Critical’s synchronous local replicas used for high availability.
40. How do you implement a zero-downtime (or near-zero-downtime) migration from on-prem SQL Server to Azure SQL Managed Instance? Use Azure Database Migration Service in online mode: DMS performs an initial full backup/restore or seeds via transactional replication-like continuous sync, then continuously applies transaction log changes to the target while the source stays live and serving traffic. You validate the target, then perform a brief cutover — pointing the application to the new endpoint — with downtime limited to the connection-switch window rather than the full data-copy duration.
41. What are the key differences in HA architecture between Business Critical and General Purpose tiers? Business Critical uses an Always On Availability Group-based architecture under the hood, with multiple synchronous replicas on local SSD storage, giving sub-10-second failover and enabling a free readable secondary (read-scale-out). General Purpose separates compute and storage (remote Azure Premium storage), so failover involves reattaching storage to a new compute node — typically a slightly longer failover window, and no built-in readable secondary without configuring Active Geo-Replication separately.
42. How would you handle a tempdb contention problem in Azure SQL Database, given you don’t have file-level access? Confirm the symptom via wait stats (PAGELATCH contention on tempdb system pages, or elevated tempdb_log_size/tempdb_data_size metrics), identify the driving query pattern (excessive use of temp tables, table variables, or sort/hash spills) via Query Store, and address it at the query/index level — since you can’t manually add tempdb files the way you would on-prem, the platform manages tempdb file configuration itself, and your lever is reducing the workload’s tempdb footprint, not the file layout.
43. Describe how you’d design Row-Level Security combined with Microsoft Entra ID for a true multi-tenant SaaS application on Azure SQL. Map each tenant to a claim or attribute available in the Entra ID token (or a session context value set via EXEC sp_set_session_context at connection time from the application layer using an authenticated tenant ID), then write an inline table-valued function checking that value against a TenantId column, applied as a FILTER predicate via CREATE SECURITY POLICY. This ensures tenant isolation is enforced at the database engine level, not just in application code — meaning even a bug in the application’s WHERE clause logic can’t leak cross-tenant data.
44. What’s your approach to capacity planning and right-sizing for a Business Critical database that’s been over-provisioned for six months? Pull sustained CPU/DTU/vCore utilization trends over the full period from Azure Monitor Metrics (not just a snapshot), check Query Store for whether query patterns have genuinely changed or whether the original sizing was simply conservative, cross-check Azure Advisor’s cost recommendations as a second opinion, and test a downsize during a low-risk window with rollback readiness — since scaling back up if needed is fast, but you want a deliberate validation step, not a blind trust in Advisor’s number alone.
45. How does Always Encrypted with secure enclaves differ from standard Always Encrypted, and what new capability does it unlock? Standard Always Encrypted severely limits what operations (pattern matching, range comparisons) can be performed on encrypted columns, since the engine never sees plaintext. Always Encrypted with secure enclaves allows richer operations — including in-place encryption changes and rich computations — by performing them inside a hardware- or software-based secure enclave on the server side that can decrypt data in an isolated, attested environment, without ever exposing plaintext to the broader engine, DBA, or OS.
46. Explain how you’d set up cross-region disaster recovery for a mission-critical Azure SQL Managed Instance, including RPO/RTO expectations. Use auto-failover groups at the Managed Instance level, pairing a primary instance with a secondary in a different region, with asynchronous data replication. Typical RPO is measured in seconds under normal conditions (data loss window depends on replication lag at the moment of failure), and RTO depends on your configured grace period before automatic failover triggers (default 1 hour, tunable) plus DNS propagation time for the failover group’s listener endpoint — document both numbers explicitly in your DR runbook rather than assuming “near zero” without measuring actual replication lag under load.
47. How would you use Azure Monitor and KQL to build a proactive early-warning system for performance regressions, before users complain? Route QueryStoreRuntimeStatistics to Log Analytics, then build a scheduled log alert (or a Logic App running on a timer) comparing each query’s rolling 7-day average duration/CPU against the prior 7-day baseline, flagging any query whose regression exceeds a defined threshold — effectively building your own trend-based anomaly detection layered on top of, or as an alternative to, Intelligent Insights’ built-in detection, giving you full control over sensitivity and thresholds specific to your workload.
48. What are the trade-offs of enabling full Automatic Tuning (all three actions) on a highly regulated, change-controlled production database? Automatic Tuning genuinely improves performance and reduces manual tuning overhead, and Azure validates each change’s impact before keeping it. But in a regulated environment with strict change-control requirements, an automatic CREATE INDEX or DROP INDEX action is technically a schema change happening without a formal change ticket or peer review — many regulated shops choose “recommend only” mode so a human approves and logs each change, accepting slightly slower response to regressions in exchange for full auditability and change-control compliance.
49. How do you approach cost optimization for a large Azure SQL estate without sacrificing performance headroom? Combine several levers: reserved capacity/Azure Hybrid Benefit for steady-state vCore workloads with predictable baseline usage, serverless compute for genuinely intermittent dev/test/reporting databases, elastic pools for many low-and-uncorrelated-usage databases instead of individually provisioned compute, right-sizing informed by real sustained-utilization data (not peak, not guesswork) from Azure Monitor, and Log Analytics ingestion/retention discipline so the monitoring layer itself doesn’t become a hidden cost center — then revisit all of it quarterly, since workload patterns and Azure pricing options both shift over time.
50. Describe a real (or realistic) incident you’ve handled involving Azure SQL, and walk through your root-cause process end to end. This is deliberately open-ended — interviewers ask it to see your investigation process, not a memorized answer. A strong structure to follow regardless of the specific incident: (1) confirm platform health first via Resource Health/Service Health to rule out an Azure-side issue, (2) check the four core resource dimensions in Metrics Explorer to localize the bottleneck category, (3) drill into Query Store and wait statistics via KQL to identify the specific query or pattern responsible, (4) apply the narrowest fix that addresses the root cause rather than the symptom (an index, a plan force, a scale action — in that order of preference), and (5) close the loop with a written postmortem and, where appropriate, a new alert or automated remediation so the same issue is caught faster next time. Walking an interviewer through this structure, even for a scenario you’re inventing on the spot, demonstrates real operational maturity.
Final Tip
If you’re prepping for an actual interview, don’t just read these top to bottom. Pick five questions from each tier, close this article, and try to answer them out loud from memory. The gap between “I recognize this answer” and “I can explain this to someone else” is exactly the gap an interviewer is testing for.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
