8. Interview Questions

A fully managed, regional relational database service running the SQL Server engine, sold as a database rather than a server or a VM. It removes the operational tail of running a relational database: provisioning and patching, backup and point-in-time restore, high availability, and failover become properties of the service tier rather than projects you staff.

The trade is access to the instance. Anything that lives at server scope in SQL Server — SQL Agent, cross-database queries, linked servers, Service Broker, CLR — is absent or reshaped. That trade is the entire reason Azure SQL Managed Instance exists as a separate product.

A database (Microsoft.Sql/servers/databases) sits inside a logical server (Microsoft.Sql/servers), which sits in a resource group, in a subscription, in a tenant.

The point to land is the logical server: it is not a machine. It has no compute and no cost of its own. It owns the globally unique DNS name, the administrator identity, the firewall and VNet rules, the auditing configuration, and the private endpoint attachment. Compute belongs to each database individually — the opposite of RDS, where databases share an instance's resources. Sharing compute across databases in Azure is an explicit opt-in called an elastic pool.

Inside the database: full ACID, SQL Server isolation levels, and read committed snapshot isolation on by default — which differs from a default on-premises install and changes blocking behaviour for anyone migrating.

Local high availability is synchronous: a committed transaction is durable across the local replica set before the commit returns. Zone redundancy extends that across availability zones in the region, still synchronously, still zero data loss.

Cross-region geo-replication is asynchronous. A geo-secondary lags. An unplanned forced failover therefore has a non-zero RPO — you can lose recently committed transactions. A planned failover drains the log first and doesn't. Anyone claiming zero data loss on regional failover is describing the planned case only.

Backup storage redundancy (Local / Zone / Geo / GeoZone) is a separate axis and is effectively fixed at creation ⚠️ verify current mutability against current Azure docs.

Default to Azure SQL Database for anything new, and for modernisation where the application talks to a single database. It is cheaper at the low end, provisions in minutes, has a serverless tier that can pause to zero compute, and supports Hyperscale.

Choose Managed Instance when the application depends on instance-scope features: SQL Agent jobs, cross-database queries and transactions, linked servers, Service Broker, CLR, msdb. Also choose it when the requirement is a VNet-native private IP rather than a private endpoint in front of a public service — some compliance teams treat those differently.

The one-line test: if the application only ever says USE MyDatabase, it is Azure SQL Database.

And the honest way to decide is not opinion — run the Data Migration Assistant and let it enumerate the actual blockers.

Compute (vCores × hours provisioned, or vCore-seconds serverless), allocated storage per GB-month, and backup storage beyond the included allowance. Long-term retention backups bill separately. A geo-secondary bills roughly a full second database's compute.

When idle: a provisioned database bills every hour regardless of query volume; storage and backup storage bill regardless; a geo-secondary bills regardless. The only thing that stops billing compute is a serverless database that has actually auto-paused — and it still bills storage.

The two follow-ups worth volunteering: serverless that never pauses (because something pings it) is more expensive than provisioned, and long-term retention backups survive deleting the database and keep billing until you delete them explicitly.

DNS resolves <server>.database.windows.net — via the privatelink CNAME chain to a private IP if a private endpoint exists. The connection lands on a regional gateway on TCP 1433, which terminates TLS and evaluates firewall and VNet rules first (before authentication, which is why a firewall rejection looks nothing like a login failure).

Then the connection policy decides the data path: under Redirect, the gateway hands back the address of the node hosting the database and the client reconnects directly on a port in 11000–11999 — one extra round trip, then no gateway in the path. Under Proxy, everything keeps flowing through the gateway on 1433. Redirect is faster; Proxy needs fewer firewall holes. This is the usual explanation for "works from my laptop, fails from the corporate network".

Authentication follows: a SQL login validated in master, or an Entra token validated against Entra ID and mapped to a contained database user. Then the ordinary SQL Server engine — parse, bind, optimise, execute — with Query Store recording the plan, and storage access shaped entirely by the service tier.

They are three different architectures, not three speeds.

• General Purpose — one compute replica; data files on remote Azure Premium Storage. Cheapest, higher I/O latency, failover in tens of seconds because a new node must start and re-attach the files.
• Business Critical — a four-node Always On availability group with local NVMe. Much lower I/O latency, failover in seconds, and one secondary readable for free via ApplicationIntent=ReadOnly. You are paying for four copies of the compute.
• Hyperscale — the engine decomposed into compute nodes with an RBPEX cache, a log service as the durability boundary, and page servers. Scales to very large sizes, and backup/restore is near-constant-time regardless of size because it is snapshot-based at the page-server layer.

Moving between General Purpose and Business Critical is an online scale operation with a brief connection drop at the failover moment. Moving into Hyperscale is straightforward; moving out of it has historically been a migration rather than a scale operation ⚠️ verify current reverse-migration support. Treat Hyperscale as a one-way door until proven otherwise.

The senior answer adds the cost framing: teams often pick Business Critical "for resilience" when zone-redundant General Purpose delivers most of the availability at a fraction of the price. Business Critical is for latency and for the free read replica.

Two sides, and doing only one is the common failure:

1. Azure side — the application gets a managed identity, and the connection string carries Authentication=Active Directory Managed Identity with no password.
2. Database side — CREATE USER [id-app] FROM EXTERNAL PROVIDER; then the narrowest role that works. db_datareader/db_datawriter at minimum, and GRANT EXECUTE ON SCHEMA::app (procedures only, no direct table access) for anything mature. Never db_owner for a runtime identity, and never db_ddladmin — the migration identity is a different identity, so a SQL injection bug can't become a DROP TABLE.

Around that: set the server's Entra admin to a group governed by PIM, enable Entra-only authentication so SQL logins stop working entirely, set publicNetworkAccess = Disabled with a private endpoint on the sqlServer sub-resource, and enforce TLS 1.2 with TrustServerCertificate=False.

Worth naming the prerequisite that trips people: the server's identity may need Entra Directory Readers for FROM EXTERNAL PROVIDER to resolve principals.

Control plane is ARM at management.azure.com, governed by Azure RBAC (SQL DB Contributor, SQL Server Contributor, Contributor, Owner). It creates the server and database, changes tier, configures firewall, geo-replication, auditing, and retention.

Data plane is TDS at <server>.database.windows.net:1433, governed by SQL permissions — database roles, GRANT/DENY, and the server's SQL or Entra admin.

The classic mistake: assuming subscription Owner lets you read a table. It does not, and no ARM role does — there is no Azure SQL Data Reader built-in role, because SQL's granularity (table, column, row, procedure) is far finer than ARM's model.

The senior addition, and the point most interviewers are actually fishing for: the control plane is still a privilege-escalation path into the data plane. An Owner can set themselves as the Entra admin on the logical server, or reset the SQL administrator password, and then read everything. So control-plane RBAC on a SQL server is a data-security control even though it grants no data permission. Enabling Entra-only authentication removes the password-reset half of that path; restricting who can write to the server resource removes the rest.

Terraform for infrastructure: a parameterised module (main.tf/variables.tf/outputs.tf) with the SKU, zone redundancy, backup redundancy and retention driven by an environment variable; remote state in an Azure Storage backend using native blob leases for locking (no separate lock table, unlike AWS); plan on pull request and apply on merge behind an environment approval gate; and workload identity federation / OIDC against an Entra app registration rather than any client secret.

The part that separates a real answer: Terraform does not manage the schema or the data. That is a second, separate pipeline — DACPAC/SqlPackage, EF Core migration scripts, or Flyway — and it is forward-only, expand-then-contract. To rename a column you add the new one, backfill, deploy code that writes both and reads the new, and drop the old in a later release. Down-migrations are a fiction once data exists.

Environment differences belong in .tfvars and separate state, with separate subscriptions for production — in Azure the subscription is the natural blast-radius, quota and RBAC boundary — and Azure Policy enforcing the rules that matter (deny public network access, deny non-Entra-only auth, require diagnostic settings) so they are constraints rather than conventions.

If the database has no public endpoint, the schema stage must run on a runner inside the VNet. Opening the firewall for the pipeline instead is how public endpoints come back.

For the database: changing the name, the collation, or the parent server_id forces replacement — which for a database means delete and recreate empty. Changing the server's name replaces the server and everything under it.

By contrast, SKU changes, zone redundancy, retention policies, firewall rules and diagnostic settings are in-place updates (with a brief connection drop at the failover moment for a scale operation).

The mitigations worth naming: lifecycle { prevent_destroy = true } on production databases, a CanNotDelete resource lock on the resource group, and reading the plan's # forces replacement lines rather than skimming to the summary. And the ARM-specific version of the same footgun: az deployment group create --mode Complete deletes every resource in the resource group that the template doesn't declare — a data-loss event triggered by a flag.

Structure the answer as a funnel, and resist jumping to "scale it up".

Is it the database or the caller? Application Insights end-to-end latency versus database duration. "The database is slow" is usually one query in one endpoint.

Which resource is actually saturated? sys.dm_db_resource_stats gives 20-second granularity over the last hour: avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, max_worker_percent, max_session_percent. CPU flat while things slow down points at IO, log write, worker exhaustion, or blocking — not compute. On General Purpose, avg_data_io_percent is the tier's characteristic bottleneck because data files are on remote storage.

Has a plan changed? This is where Query Store earns its keep — it retains plans and their runtime statistics, so you can show a query regressed at a specific time with a specific new plan, and force the old one. A week-long degradation with flat CPU is very often a plan regression after statistics updated or data grew past a threshold.

Is it blocking? sys.dm_exec_requests with blocking_session_id, plus the Blocks and Deadlocks diagnostic categories.

Only then, capacity. If it is genuinely resource-bound: scale up (accepting the brief drop), consider moving read traffic to a replica with ApplicationIntent=ReadOnly — free on Business Critical and routinely unused — or fix the query and the index.

Bad answer: "scale to Business Critical". It costs several times more and fixes nothing if the problem is a missing index or a regressed plan.

Database-per-tenant on elastic pools. Per-tenant databases give you clean isolation, per-tenant restore, per-tenant geo-placement for residency, and a trivial "delete this customer" story. Buying compute per database for 500 mostly-idle databases is unaffordable, which is exactly what pools fix: one pooled allocation with per-database min/max caps so one noisy tenant can't starve the others.

Shard tenants across several pools rather than one giant one, so a pool is a blast-radius boundary and you can move a heavy tenant into its own database without re-architecting.

Where it breaks: pools work because peaks are staggered. If every tenant runs month-end on the same day, you must size for the aggregate peak and have gained a shared failure domain for nothing. Check the correlation before committing.

The rest of the design: a catalog database mapping tenant → server/database; Elastic Jobs for schema migrations across all 500 (this is also the answer to "where did SQL Agent go"); a single logical server per pool group with Entra-only auth; and the honest alternative — a shared database with a TenantId column and row-level security, which is far cheaper at very small tenant sizes and far worse for isolation, noisy neighbours, and per-tenant restore. Say which you would pick and why; the wrong answer is not knowing the other one exists.

First: which pipeline failed? They have different rollback stories, and conflating them is how a bad night becomes a bad quarter.

Infrastructure failed — re-apply the previous commit. Terraform is convergent, so a half-applied change reconciles. Check the plan for forces replacement before applying anything under pressure. If a CanNotDelete lock is blocking, that is working as intended; don't remove it at 3 a.m. without understanding why the apply wants to delete something.

Schema failed — there is no down-migration. Roll forward with a corrective migration, or if data is damaged, point-in-time restore. Note that PITR restores to a new database, so the actual recovery is: restore to a new name, rename the damaged one aside, rename the restored one into place, reconnect the app. That rename dance is the step nobody has rehearsed, which is why it belongs in a quarterly drill with a stopwatch — the measured time is your real RTO.

Blast radius questions to answer out loud: is this resource group shared with anything else? Is the deployment mode incremental (safe) or complete (deletes everything not in the template)? Does a geo-secondary or failover group exist, and did the failed change propagate to it? Did the migration run against production data before failing, meaning a restore loses everything since?

Afterwards: the postmortem action is almost always BlockOnPossibleDataLoss=true on the schema publish, a script-and-review step before publish, and prevent_destroy on the database.

Detect. A scheduled nightly terraform plan in CI that fails the build on a non-empty plan is the single most effective control — it turns drift from a discovery into an alert. Azure Policy compliance state catches drift Terraform doesn't manage (a hand-added firewall rule, public access re-enabled). Neither tells you who, which is what the activity log is for:

az monitor activity-log list --resource-group rg-sql-orders-prod \
  --start-time 2026-07-28T00:00:00Z \
  --query "[?contains(resourceId,'databases')].{time:eventTimestamp, who:caller, what:operationName.value}" -o table

Decide, deliberately. Two legitimate outcomes, and the mistake is picking neither:

• The emergency scale should stay → update the sku_name in code, raise a PR referencing the incident, apply. Reality becomes the spec.
• It should not stay → apply the existing code to revert, and schedule it for a low-traffic window because scaling drops connections.

Either way, say which in the pull request. Silently tolerating drift trains people to ignore the plan, and then the one plan that mattered gets ignored too.

Prevent the recurrence properly. Don't respond by removing portal access — someone scaling a database during an incident is correct behaviour. Respond by making the drift visible within hours and reconciled within a day, and by using PIM so the elevated access that permitted it was time-bound and logged in the first place.