Design data storage solutions

Relational data

Choosing the service

Rule of thumb: SQL Database unless a compatibility feature forces Managed Instance, and a VM only when PaaS can’t do it. Managed Instance has a Next-gen General Purpose tier and an instance pool for small instances. Azure Arc-enabled SQL Managed Instance runs on-premises or other clouds.

Service tiers and compute tiers

Purchasing models: vCore (choose cores, memory, storage separately; use Azure Hybrid Benefit and reserved capacity) is recommended. DTU is a bundled blend for simple workloads (Basic, Standard, Premium) and is not available for Managed Instance or Hyperscale.

• Elastic pools share compute among many databases with varied, non-coinciding peaks. They are the answer to “many databases, unpredictable usage per database, control cost”.
• Reserved capacity (1 or 3 year) and Azure Hybrid Benefit cut cost for steady workloads.
• Hyperscale and General Purpose are available in serverless.

Scalability

• Scale up (change tier or vCores, brief connection drop). Hyperscale scales in minutes regardless of size.
• Read scale-out: Business Critical and Premium include a free read-only replica reached by ApplicationIntent=ReadOnly. Hyperscale uses named replicas (separate compute, own scaling, different security) for isolated read workloads.
• Sharding with the elastic database tools and a shard map for horizontal partitioning when a single database ceiling isn’t enough.
• Geo-replicas can also serve reads in other regions.
• For open source: Flexible Server read replicas (up to 5 for PostgreSQL, 10 for MySQL), vertical scale and storage autoscale. Cosmos DB scales horizontally by partition.

Data protection

• TDE (transparent data encryption) is on by default with service-managed key. Use customer-managed key (BYOK) in Key Vault for control. Encrypts data at rest.
• Always Encrypted keeps keys on the client so even DBAs can’t read the columns (with secure enclaves for richer queries). Choose it when “even administrators must not see the data”.
• Dynamic data masking hides values from non-privileged users in query results only. It is not encryption. Row-level security filters rows by user. Data discovery and classification labels sensitive columns.
• Entra authentication (including managed identities) is preferred over SQL logins. Private endpoint and firewall rules limit network exposure.
• Auditing to storage, workspace or Event Hubs. Microsoft Defender for SQL adds vulnerability assessment and threat detection. SQL Ledger gives tamper-evident history.
• Backups and HA are covered in the business continuity notes.

Semi-structured data

Azure Cosmos DB

A globally distributed, multi-model NoSQL database with single-digit millisecond reads and writes, elastic throughput and 99.999% availability (multi-region).

• Consistency levels, strongest to weakest: Strong, Bounded staleness, Session, Consistent prefix, Eventual. Session is the default and the usual pick. Strong costs latency and can’t be used with multi-region writes. Eventual and consistent prefix have the lowest latency and cost.
• Throughput: RU/s (request units) provisioned manual, autoscale (scales 10-100% of max), or serverless (pay per request, small or spiky). Dedicated or shared at database level.
• Partitioning: choose a partition key with high cardinality and even distribution. A hot partition throttles requests (429). A logical partition holds up to 20 GB; a physical partition holds 50 GB and 10,000 RU/s.
• Multi-region writes for active-active; otherwise single-write region with read replicas and automatic failover.
• Change feed drives event-driven processing. Analytical store with Synapse Link / Fabric mirroring runs analytics without touching transactional RUs.
• Integrated cache, TTL and time-to-live for expiry.
• Cosmos vs SQL: choose Cosmos for schema-flexible documents, global low-latency distribution and massive horizontal scale. Choose SQL for relational integrity, joins and complex transactions.

Table storage is a cheap key-value store inside a storage account for simple structured NoSQL data, not globally distributed. Azure Managed Redis / Cache is for caching, not a system of record. Azure Data Explorer is for time series, telemetry and log analytics at scale.

Unstructured data

Choosing the store

• Files tiers: Premium (SSD, low latency, provisioned), Transaction optimized, Hot, Cool (HDD, pay as you go in the standard account). NFS needs premium.
• Disk selection: boot and light dev = Standard SSD, most production = Premium SSD v2 or Premium SSD, latency-sensitive databases with tuneable IOPS = Premium SSD v2 or Ultra. Ultra offers the highest IOPS, sub-ms latency, and dynamic performance change but has restrictions on zones, caching and snapshots.
• Storage account kinds: use general-purpose v2 (standard), premium block blobs, premium file shares, premium page blobs. Older v1 and BlobStorage accounts should be upgraded.

Balancing features, performance and cost

Blob access tiers (set at account default for hot/cool, per blob for all):

• Deleting or moving early incurs an early deletion charge. Archive blobs can’t be read until rehydrated (standard up to 15 hours, high priority under 1 hour for small blobs).
• Lifecycle management policies move blobs between tiers or delete them automatically by age or last access. This is the standard answer to “reduce cost of aging data automatically”. Last access time tracking enables tiering by access.
• Premium block blob accounts suit low-latency, high-transaction workloads (SSD).
• Reserved capacity discounts steady stored data. Smart tier options choose tiers automatically.
• Performance: spread across multiple storage accounts or partitions for very high request rates, use Azure CDN / Front Door for read-heavy public content, and Azure Blob Storage with NFS v3/SFTP where protocols require.

Protection and durability

Durability: LRS 11 nines, ZRS 12 nines, GRS/GZRS 16 nines (over a year). The secondary region is the paired region and replication is asynchronous (RPO usually under 15 minutes, not guaranteed).

Data protection features:

• Soft delete for blobs, containers and file shares (recover deleted data for a retention window). Versioning keeps prior versions on overwrite. Point-in-time restore for block blobs needs soft delete, change feed and versioning.
• Immutable storage (WORM): time-based retention and legal holds at container or version level for SEC 17a-4 style requirements.
• Encryption is always on (256-bit AES); options for CMK and customer-provided keys. Secure transfer required, minimum TLS version, disable shared key and anonymous access, use Entra authorization with RBAC, user delegation SAS over account-key SAS.
• Private endpoints and the storage firewall keep traffic private. Microsoft Defender for Storage scans malware and detects threats.
• Resource lock on the storage account prevents accidental deletion of the account.

Data integration

Choices: batch movement and orchestration use Data Factory; streaming uses Event Hubs plus Stream Analytics or Spark; code-heavy transformations and ML use Databricks. The self-hosted integration runtime is the answer for on-premises or private network sources.

Data analysis

Pick Fabric for a unified new platform, Synapse dedicated pool for an established MPP warehouse, serverless SQL for ad hoc queries over lake files without provisioning, and Databricks when the team is Spark and ML centred. Mirroring and Synapse Link give near real-time analytics from Cosmos DB or SQL without ETL.