Memory hook: Data model, consistency, recovery and operating responsibility determine the storage choice.
Must remember
- Azure SQL Database provides managed database capabilities; SQL Managed Instance improves instance-level compatibility for supported SQL Server workloads; SQL Server on Azure VMs offers OS/instance control with more management. Choose by required features, migration compatibility, isolation, scale and cost.
- Azure Database for PostgreSQL supports managed PostgreSQL workloads; Cosmos DB supports distributed non-relational patterns with partitioning and configurable consistency. Blob/Files provide object/file interfaces, not relational query semantics. Analytics may combine Data Lake Storage, Data Factory, Synapse or Fabric according to requirements.
- Select storage tiers, redundancy and lifecycle based on access frequency, retrieval time, residency and retention. Private endpoints, data roles, encryption/key ownership and auditing are separate controls. A cheaper stored GB can create costly retrieval and data movement.
- RPO/RTO determine backup, replica and failover design. Geo-replication can be asynchronous; read scale does not automatically provide transparent write failover. Failover groups, availability features, Backup and Site Recovery have workload-specific behaviour and limitations.
- Plan application-consistent recovery, key/secret availability, network/DNS changes, capacity and dependencies. A multi-Region design needs write ownership/conflict handling and a failback plan. Test recovery using useful user transactions rather than VM power state.
- Use caching, partitioning, query/index tuning, elastic pools or scaling where the evidence supports them. Optimise complete cost, including licences, standby capacity, storage history and transfer. Protect against logical errors with recoverable history even when replicas are highly available.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Need SQL Server instance compatibility with managed operations | Evaluate SQL Managed Instance. |
| Need global key/document access with tunable consistency | Evaluate Cosmos DB with deliberate partition design. |
| Need to recover before a bad deployment corrupted data | A suitable point-in-time recovery/backup strategy. |
Traps
- HA replicas can replicate a bad write.
- A storage redundancy label is not an application recovery plan.
- The strongest consistency option may add latency/availability trade-offs.
Active recall
1. What distinguishes SQL on a VM from SQL Database?
More OS/instance control and more customer operational responsibility.
2. Why is a partition key an architecture decision?
It affects distribution, query locality, scale and hot-partition risk.
3. What should be available in the recovery Region besides data?
Compute capacity, identity, keys/secrets, network, DNS and application dependencies.
4. Why measure replication lag?
It affects potential data loss at failover and whether the RPO is met.
5. Why retain backups with multi-Region replicas?
Replicas do not necessarily preserve a clean earlier state after corruption or deletion.