Relational Data: Azure SQL & PostgreSQL

Question 8. Compare Azure SQL Database, SQL Managed Instance, and SQL Server on a VM.

Answer

Azure SQL Database is fully managed PaaS for a single database or elastic pool — Microsoft handles patching, backups, and HA; best for new cloud apps. SQL Managed Instance is also PaaS but offers near-100% SQL Server compatibility (SQL Agent, cross-database queries, CLR, Service Broker) and native VNet deployment — ideal for lift-and-shift of existing SQL Server workloads. SQL Server on a VM is IaaS: full control and 100% compatibility, but you manage OS, patching, and HA yourself. Pick SQL Database for greenfield, Managed Instance to migrate an on-prem instance with minimal changes, and SQL on VM only when you need OS-level control or features neither PaaS option supports.

Analogy

SQL Database is a furnished studio (move in, live simply); Managed Instance is a fully furnished house that matches your old one (easy move-in for an existing household); SQL-on-VM is an empty house you furnish and maintain entirely.

Question 9. Explain the DTU vs vCore purchasing models for Azure SQL.

Answer

DTU (Database Transaction Unit) bundles compute, storage, and I/O into a single blended metric across fixed tiers (Basic/Standard/Premium) — simple, good for predictable small/medium workloads, but you can't scale compute and storage independently. vCore lets you independently choose vCores, memory, and storage, gives transparency into the hardware, supports Azure Hybrid Benefit (reuse SQL licenses to cut cost), and unlocks Hyperscale and Serverless options.

vCore is the recommended, more flexible model and is required for advanced tiers. Use DTU only for simple, steady workloads where its simplicity is the appeal.

Analogy

DTU is a fixed combo meal (one number, take it or leave it); vCore is ordering à la carte — pick exactly the CPU, RAM, and storage you want, and bring your own coupons (Hybrid Benefit).

Question 10. What are elastic pools and the vCore service tiers (General Purpose, Business Critical, Hyperscale)?

Answer

Elastic pools let many databases share a common pool of resources, smoothing cost when individual databases have spiky, uncorrelated load (great for SaaS multi-tenant — one DB per tenant). You pay for the pool, not each DB's peak. The vCore service tiers are: General Purpose (remote SSD storage, balanced cost — most workloads); Business Critical (local SSD + always-on replicas for lowest latency and fast failover, plus a free readable secondary); and Hyperscale (decoupled storage scaling to 100 TB, near-instant backups, and rapid read-scale-out).

Choose General Purpose by default, Business Critical for latency-sensitive/high-availability OLTP, and Hyperscale for very large databases or fast scaling needs.

Analogy

An elastic pool is a shared office printer plan — departments draw from one budget instead of each buying a printer for its busiest day. The tiers are economy / first-class / freight: balanced, low-latency premium, or massive capacity.

Question 11. How does high availability and disaster recovery work in Azure SQL — geo-replication vs failover groups?

Answer

In-region HA is built in (replicas behind the scenes). For cross-region DR you have two tools. Active geo-replication creates up to 4 readable secondaries in other regions with async replication; failover is manual per database. Auto-failover groups build on it to manage a group of databases together, provide automatic failover on a policy, and — crucially — expose stable read-write and read-only listener endpoints so your connection string doesn't change after a failover. Use failover groups for production DR because they automate failover and keep endpoints stable; use plain geo-replication when you want fine-grained, per-database, read-scale secondaries.

Example

Apps connect to the failover-group listener (e.g. myfog.database.windows.net) rather than a specific server, so a region failover redirects transparently without a redeploy.

Analogy

Geo-replication is keeping a manual backup copy in another city; a failover group is an automatic switchover with call-forwarding — if the main office goes down, calls (connections) reroute to the backup office on their own, same phone number.

Question 12. What is Azure Database for PostgreSQL Flexible Server, and why is it a common choice?

Answer

Flexible Server is the current managed PostgreSQL offering, giving you a managed Postgres with zone-redundant high availability, configurable maintenance windows, burstable (B-series) SKUs for cost-sensitive workloads, custom server parameters, and VNet integration / private access. It replaces the older Single Server model (which is being retired).

It's popular for cost-effective, open-source-stack apps: a small burstable instance (e.g. B1ms) runs dev/low-traffic workloads cheaply, and you scale up or enable HA when you go to production. .NET apps connect via Npgsql, ideally authenticating with a managed identity (Entra auth) rather than a password.

Analogy

It's a managed engine for the open-source car: you keep the PostgreSQL you like, and Azure handles the oil changes, backups, and standby engine (HA) — and you can start with a small economy motor and upgrade later.

Question 13. How do you make .NET data access resilient to transient SQL faults?

Answer

Cloud databases occasionally throw transient errors (failovers, throttling, brief network drops), so connections and commands must retry with backoff. EF Core has this built in via an execution strategy (EnableRetryOnFailure), which retries idempotent operations on known transient error numbers. For ADO.NET/Dapper, wrap calls in a Polly retry policy targeting transient SQL exceptions. Be careful with explicit transactions under retrying strategies — you must wrap the whole unit in

strategy.ExecuteAsync(...) so a retry re-runs the entire transaction, not a partial one. Always set

sensible command timeouts and use connection pooling.

Example

services.AddDbContext<AppDb>(o =>
 o.UseSqlServer(conn, sql =>
 sql.EnableRetryOnFailure(

maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10),

errorNumbersToAdd: null))); Analogy

It's the database equivalent of redialing a dropped call a few times before giving up — brief blips are expected in the cloud, so the client politely retries instead of failing instantly.

Question 14. What options exist for securing data in Azure SQL (auth, network, encryption)?

Answer

Authentication: prefer Microsoft Entra authentication with a managed identity over SQL logins, so there's no password to leak. Network: lock down with the server firewall, restrict to a VNet via Private Endpoint (disable public access), or use service endpoints. Encryption: data is encrypted at rest by default (Transparent Data Encryption) and in transit (TLS); for protecting sensitive columns even from DBAs, use Always Encrypted (client-side encryption) and Dynamic Data Masking to obscure values for unprivileged users.

Layer these — identity-based auth + private networking + TDE + column-level protection — and audit with SQL auditing and Microsoft Defender for SQL.

Analogy

Defense in depth: badge entry instead of a shared password (Entra/MI), a private driveway (Private Endpoint), a safe that's always locked (TDE), and tinted glass on specific drawers (Always Encrypted / masking).