What is Managed Databases — Azure SQL & Cosmos DB in Microsoft Azure?

4 / 24

Microsoft Azure

Managed Databases — Azure SQL & Cosmos DB

Choose managed relational (Azure SQL) or globally-distributed NoSQL (Cosmos DB).

Two very different databases, and the interview is about choosing.

Azure SQL is a managed branch of a familiar bank that still follows all the classic rules (SQL, transactions). Cosmos DB is a global courier network with depots in every city: data is everywhere at once with millisecond pickup, and you dial exactly how synchronised the depots must be versus how fast they respond.

Key Concepts

    Azure SQL   managed SQL Server. Relational, joins, transactions.
    Cosmos DB   globally distributed NoSQL, millisecond reads.

Azure SQL comes in three shapes.

    Single Database   one database, its own resources
    Elastic Pool      many databases sharing a budget -- for SaaS
                      tenants with uneven, uncorrelated load
    Managed Instance  near-full SQL Server surface, for lift-and-shift
                      (SQL Agent, cross-database queries, CLR)

Elastic Pools are the SaaS answer. A hundred tenant databases that each peak at different times cost far less sharing a pool than provisioned individually.

Purchasing models.

    vCore   choose cores and memory. Supports Hybrid Benefit.
    DTU     a blended unit. Simpler, older, harder to reason about.

Serverless vCore auto-pauses when idle, which suits development and intermittent workloads.

High availability versus disaster recovery is a distinction worth stating:

    zone-redundant    survives a datacentre loss, same region
    failover group    automatic failover to ANOTHER region, with
                      a listener endpoint so connection strings
                      do not change
    active geo-replication  readable secondaries, manual failover

Cosmos DB is a different model. You design around a partition key, pay in Request Units, and there are no joins. Its distinguishing features are turnkey multi-region replication including multi-region writes, and five tunable consistency levels from strong to eventual — session being the default and usually what you want.

Choosing between them. Varied or unknown queries, relational integrity, reporting: Azure SQL. Few known access patterns that must stay fast at global scale: Cosmos DB.

Connection handling differs. Azure SQL has connection limits a serverless app can exhaust, and retry logic for transient faults is mandatory. Cosmos DB is an HTTP API with no pool.

What the interviewer is probing.1. "When is an Elastic Pool the right choice?" Probing: the SaaS pattern. Stalls: "To save money generally." Moves up: many tenant databases with uneven, uncorrelated load — they share a budget rather than each being provisioned for its own peak.

2. "What does a failover group give you over zone redundancy?" Probing: HA versus DR. Stalls: "Both are failover." Moves up: zone redundancy survives a datacentre loss in the region; a failover group fails over to another region with a listener endpoint, so connection strings do not change.

3. "What are Request Units?" Probing: the Cosmos cost model. Stalls: "A performance setting." Moves up: the normalised cost of every operation; exceed the provisioned rate and you get 429s, which the SDK retries, so throttling shows up as latency rather than errors.

4. "Which Cosmos consistency level is the default and why?" Probing: the five levels. Stalls: "Strong." Moves up: session — read your own writes, which is what most applications actually need, at far lower RU cost than strong.