Azure SQL Service Tiers and Architecture
Azure SQL product family: Azure SQL Database (fully managed PaaS — single databases or elastic pools, maximum PaaS abstraction), Azure SQL Managed Instance (near-100% SQL Server compatibility — VNet-integrated, supports SQL Agent, CLR, linked servers, cross-database queries, most on-premises migrations), SQL Server on Azure VMs (IaaS — full control, same as on-premises SQL Server, pay for OS and SQL licence separately or use Azure Hybrid Benefit to reuse existing licences). Purchasing models: DTU (Database Transaction Unit — bundled compute, memory, IO — simple but less flexible: Basic, Standard, Premium tiers), vCore (choose cores and memory independently — compute, memory, and storage billed separately, supports Azure Hybrid Benefit). Service tiers within vCore: General Purpose (Fsv2 or provisioned remote storage — most workloads), Business Critical (local SSD storage, built-in read replica, highest IOPS — latency-sensitive workloads), Hyperscale (scales to 100 TB, rapid backup/restore via snapshots — large databases). Serverless compute tier: auto-pauses when inactive (billing stops), auto-resumes on connection — for development and infrequently used databases.
Performance Tuning and Query Optimisation
DP-300 performance tuning: Query Store — built into Azure SQL and SQL Server 2016+ — captures query execution plans and runtime statistics over time, identifies plan regression (query suddenly uses a bad plan after statistics update), force good plans, compare performance before/after changes. Intelligent Query Processing (IQP): batch mode on rowstore, adaptive joins, interleaved execution for multi-statement TVFs — all automatic in compatibility level 150+. Index management: missing index recommendations in Query Store and sys.dm_db_missing_index_details, index fragmentation (ALTER INDEX REBUILD for high fragmentation, REORGANIZE for low fragmentation — REBUILD is offline by default on Standard tier but online in Business Critical and Hyperscale), covering indexes (include non-key columns to avoid key lookups), filtered indexes (WHERE clause — partial index for queries with common filters). Statistics: SQL Server query optimiser uses statistics to estimate row counts — stale statistics cause bad plans, update automatically (threshold-based) or manually with UPDATE STATISTICS. Azure SQL Database Advisor: automatic tuning recommendations — create index, drop index, force plan, parameterise queries — apply automatically or review first.
High Availability, Backup, and Security
Azure SQL HA and backup. Azure SQL Database built-in HA: General Purpose uses remote storage with automatic failover (99.99% SLA), Business Critical uses Always On availability group replicas across nodes (built-in read replica, faster failover — 99.995% SLA). Active geo-replication: create up to four readable secondary databases in different regions — asynchronous replication, manual failover. Auto-failover groups: automatic failover with listener endpoint (application always connects to the same DNS name — no connection string change needed). Backup: automated backups (full weekly, differential daily, log every 5-12 minutes), point-in-time restore within retention period (7-35 days), long-term retention (LTR) — store backups in Azure Blob Storage for up to 10 years. Security: Azure SQL firewall rules (IP-based allowlist at server or database level), private endpoints (no public access — traffic stays in Azure), Transparent Data Encryption (TDE — always on, default service-managed keys or CMK via Azure Key Vault), Always Encrypted (column-level encryption — SQL Server never sees plaintext, encrypted in client app — protects from DBAs and cloud insiders), Row-Level Security (RLS — filter rows based on current user context — for multi-tenant databases), Dynamic Data Masking (obfuscate sensitive columns for non-privileged users without changing stored data).