Azure / Intermediate: managed services and governance / Lesson 8

Managed database choices and data contracts

Compare managed relational and distributed-data options from transactions, compatibility, access patterns, and recovery. Keep engine management distinct from application schema and query correctness.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Distinguish service families and compatibility requirements.
Model connection and partition pressure.
Define backup, high availability, and restore evidence separately.

Start with the data operation, not the logo

Azure SQL Database, Azure SQL Managed Instance, Azure Database for PostgreSQL flexible server, and Azure Cosmos DB represent different service and compatibility choices. They are not interchangeable storage labels. Identify transaction scope, query language, relational constraints, supported extensions or features, latency requirements, and migration assumptions. A managed engine can reduce infrastructure patching responsibilities while leaving schema design, permissions, query plans, connection behavior, and recovery validation with the application team. Check current regional support, engine lifecycle, and the chosen service option before planning a move from a self-managed database. Do not assume every SQL Server or PostgreSQL feature has identical support in every managed offering.

A decision model should expose what cannot be compromised

The synthetic enrollment workload needs a transaction covering seat allocation and enrollment identity, plus predictable lookup by course and learner. A document-oriented activity feed has a different partition and query contract. Record consistency and uniqueness expectations explicitly before selecting a distributed-data API. Cosmos DB partition design influences distribution, query efficiency, and transaction boundaries under the selected API; a hot key can concentrate load. The JSON below records requirements only, not a deployed database or a guarantee that a service automatically enforces every rule. Access policy and regional recovery remain separate work.

json A schema and partition decision record

{
  "model": "database-requirements-only",
  "enrollment": {
    "atomic_operation": [
      "allocate-seat",
      "create-enrollment"
    ],
    "unique_key": [
      "learner-id",
      "course-id"
    ]
  },
  "activity_feed": {
    "partition_question": "which key spreads expected writes?",
    "query_question": "which reads cross partitions?"
  }
}

Expected output

transaction and partition requirements must drive service evaluation

The model neither selects an account API nor creates constraints. Real service and engine behavior must be verified later.

Application scale multiplies connection demand

Eight application instances each opening a pool of twenty connections can request 160 concurrent connections, even when no individual instance seems large. In the invented model, the available budget is 120 and ten are reserved for other work, leaving thirteen per instance after integer division. This is a local planning calculation, not an Azure service limit or a universal pool recommendation. Real connection pressure also depends on request concurrency, transaction duration, retries, idle behavior, and failover. Pool limits should protect the database rather than merely maximize each client’s local throughput. Verify the complete workload and chosen service limits.

python Offline connection-budget arithmetic

instances = 8
pool_per_instance = 20
budget = 120
reserved = 10
print(instances * pool_per_instance)
print((budget - reserved) // instances)

Expected output

160
13

The numbers are synthetic. No database, connection pool, quota, or Azure endpoint is opened or measured.

High availability, backup, and migration are different promises

A high-availability configuration handles specified failures; backups preserve recoverable data under retention and restore rules; migration moves a workload with compatibility and cutover concerns. None automatically proves the others. Define recovery time and data-loss objectives, test restore consistency, and plan credentials, identities, networking, and application configuration at the destination. A read replica may lag and may not support every write or failover workflow. Engine maintenance can affect availability according to the service contract. Do not promise zero downtime or zero loss from the phrase managed database; specify the exact option, failure, and tested procedure.

Protect queries, identities, and observable outcomes

Parameterized queries protect value handling but do not authorize a tenant or prevent an expensive unbounded query. Use narrow identities, scoped data access, encrypted transport, controlled network exposure, and safe diagnostic practices. Monitor query count, duration, waits, failures, and connection pressure without logging private payloads. The course executes no SQL against Azure, runs no migration, and creates no database account. Its models establish requirements and arithmetic only. A later authorized evaluation should use representative synthetic data on the selected service and verify correct results, denied access, recovery, and acceptable resource behavior before accepting the design.

Common errors and fixes

Managed database is treated as schema and access correctness

Why it happens: Platform maintenance is confused with application responsibility.
How to fix it: Define and test schema, permissions, queries, and recovery separately.
Prevent it next time: Keep the service responsibility boundary in the design record.

Every app instance chooses a large pool independently

Why it happens: Aggregate connection demand is omitted.
How to fix it: Budget connections across all clients and reserve required headroom.
Prevent it next time: Test scaling and retry behavior against documented limits.

A replica is assumed to satisfy every recovery objective

Why it happens: Replication, retention, and tested restore are conflated.
How to fix it: Specify lag, failover, backup, and restoration requirements explicitly.
Prevent it next time: Verify the exact selected service option and failure case.

Check your understanding

1. Why can an application scale-out overload a database connection budget?

Show worked solution

Each new instance can add its own pool, multiplying aggregate connections. Count every client, reserve operational headroom, and inspect transaction duration and retry storms. The example’s thirteen-per-instance result is specific to its invented budget and must not be copied as an Azure limit or tuning prescription.

2. What should be decided before choosing a partition key for a distributed activity feed?