Azure Databases — Azure SQL, Managed Instance, Cosmos DB, PostgreSQL and Migration
In short: Azure SQL Database is a fully managed single database (or elastic pool) — best for new cloud apps, with database-scoped features.
- Managed Instance is a fully managed SQL Server instance in your VNet with near-complete compatibility (SQL Agent, cross-database queries, CLR, linked servers) — best for lift-and-shift migrations.
- SQL Server on an Azure VM is IaaS with full control and 100 % compatibility but you manage patching, backups and HA.
In short: DTU bundles CPU, memory and I/O into one measure with fixed tiers (Basic, Standard, Premium) — simple but less flexible. vCore lets you choose cores, memory and storage independently, pick hardware generations and tiers (General Purpose, Business Critical, Hyperscale), use serverless compute, and save with Azure Hybrid Benefit and reserved capacity.
- Most new deployments use vCore.
In short: Enable a managed identity on the App Service, set an Entra admin on the SQL server, create a contained database user FROM EXTERNAL PROVIDER for the identity and grant roles, and use a connection string with Authentication=Active Directory Default (or Managed Identity).
- Locally the same code uses your developer login through DefaultAzureCredential.
In short: Active geo-replication replicates a single database to up to four readable secondaries in other regions, and failover is per database with connection strings pointing to specific servers.
- Failover groups manage a group of databases (or a whole managed instance) together and provide stable listener endpoints for read-write and read-only traffic that follow the primary after failover, so applications don't need connection-string changes.
- Failover can be automatic or manual.
In short: Backups are automatic: weekly full, differential every 12–24 hours and log backups every 5–10 minutes, stored in geo-redundant (or zone/locally redundant) storage.
- You can restore to any point in time within the retention period (1–35 days) as a new database, keep long-term retention backups up to 10 years, and geo-restore to another region if the primary region is unavailable.
In short: The property used to distribute items across logical and physical partitions; all items with the same value form a logical partition (max 20 GB).
- Choose a key with high cardinality and evenly spread reads/writes that is present in most queries (e.g. customerId, tenantId, deviceId) — so queries hit one partition and throughput isn't limited by a hot partition.
- It can't be changed later without migrating data.
In short: RUs are the normalised cost of operations (CPU, IO, memory): a 1 KB point read ≈ 1 RU, writes and queries cost more;
- you provision RU/s (manual/autoscale) or pay per RU in serverless.
- Consistency levels trade latency/availability for freshness: Strong, Bounded staleness, Session (default, read-your-writes), Consistent prefix and Eventual.
In short: A compute tier that automatically scales vCores between a minimum and maximum based on load and bills per second of compute used;
- in General Purpose it can auto-pause after an idle period, charging only for storage, and resumes on the next connection (with a short delay).
- Ideal for dev/test and intermittent workloads, not for latency-sensitive always-on production.
Private endpoints and disabled public access (or tight firewall rules), Entra-only authentication with managed identities, least-privilege database roles, TDE (on by default) and TLS, Always Encrypted for highly sensitive columns, Row-Level Security for tenant isolation, Dynamic Data Masking for non-privileged users, auditing to Log Analytics, Microsoft Defender for SQL, and secrets (if any) in Key Vault.
In short: A vCore service tier with a distributed architecture separating compute, page servers and log service, supporting databases up to 128 TB with near-constant-time backups and fast restores, rapid scale up/down, and up to four HA replicas plus named read replicas.
- Use it for large or fast-growing OLTP databases and mixed workloads needing read scale-out.
In short: Cloud databases have transient faults: failovers during maintenance or hardware failure, load balancing, throttling when resource limits are hit (errors such as 40613, 40501, 40197, 10928).
- A short retry with exponential backoff (EF Core
EnableRetryOnFailure, Polly, SqlClient configurable retry logic) makes these invisible to users; - make operations idempotent so retries are safe.
In short: A shared pool of compute (vCores or eDTUs) and storage used by many Azure SQL databases, each scaling within per-database min/max limits — cost-effective when databases have low average and uneven peak usage (SaaS database-per-tenant).
- Avoid it when databases peak at the same time or one database needs consistently high resources — that database belongs in its own single database or a separate pool.
In short: Azure SQL Managed Instance, because it supports SQL Agent and cross-database queries.
- Assess with the migration tools, provision MI in a VNet connected by VPN/ExpressRoute, use the online migration (Database Migration Service or the MI link/log replay) to keep the target in sync, test the application against MI, then cut over during a short window by stopping writes, completing the sync and switching connection strings.
In short: Check Query Store/Query Performance Insight for the top CPU queries and regressed plans;
- force the last good plan (or rely on automatic tuning) as an immediate fix;
- fix the query/indexes (missing index, non-SARGable predicate, parameter sniffing);
- temporarily scale up the tier if users are impacted;
- add caching or read replicas for heavy reads;
- set alerts on CPU and log IO for early warning.
In short: Put them in one or more elastic pools so they share a pool of vCores/eDTUs sized for combined peak, not each database's peak;
- monitor per-database usage and move noisy tenants to their own database or pool;
- use Elastic Jobs for schema updates across all databases and a catalog database to map tenants to databases.