Microsoft AZ-305: Data Storage and Database Solutions — Study Guide

By Laurynas Brazys · 11 August 2026 · #microsoft #az-305 #certification #exam-prep #study-guide

Part of the Microsoft Azure Solutions Architect Expert AZ-305 — Study Guide. Practice with verified answers in the Microsoft exam hub, or take timed practice tests on ExamRoll.io.

Overview

Designing data storage on Azure requires balancing consistency, latency, availability, operational complexity, and cost across multiple database and storage options. The platform spans fully managed relational databases, globally distributed NoSQL, object storage with data lifecycle governance, high-throughput analytical data lakes, and in-memory caches. Your architecture should start with workload characteristics—transactional vs analytical, global reach vs locality, schema rigidity, read/write patterns, size and velocity—and select the service and configuration that match those constraints while meeting security, resilience, and governance requirements.

Relational Data Services on Azure

Azure SQL Database offers two purchasing models. The DTU model combines CPU, memory, and I/O into a blended unit across Basic, Standard, and Premium tiers; it is simple but opaque for capacity planning. The vCore model separates compute, memory, and storage, with hardware choices, predictable scaling, and cost levers such as Azure Hybrid Benefit and reserved capacity. Under vCore, the main service tiers are General Purpose (decoupled compute and remote storage, balanced cost), Business Critical (local SSD with Always On replicas for low-latency I/O and fast failover), and Hyperscale (log-structured architecture with page servers and distributed storage for multi-terabyte scale and fast snapshot-based operations). Serverless for General Purpose automatically scales compute between a configured min and max and can auto-pause when idle; you pay per compute-second and storage. This is well-suited for intermittent or development workloads but incurs cold-start and cache warm-up on resume.

Elastic pools let multiple databases share a compute budget and I/O headroom, smoothing peaks and reducing cost for many small, variable-load databases. Pools exist in both DTU and vCore models; correct sizing demands understanding aggregate concurrency and per-database burst limits to avoid noisy-neighbor effects.

Azure SQL resiliency patterns include active geo-replication and auto-failover groups. Active geo-replication asynchronously maintains up to four readable secondary databases in any Azure region, with failover initiated per database. Auto-failover groups manage multiple databases as a unit across paired logical servers, provide read/write and read-only listener endpoints, and handle planned or unplanned failover with configurable grace periods—ideal for multi-tenant SaaS. Zone redundancy is available in Business Critical (and Hyperscale) tiers to span Availability Zones within a region, improving intra-region fault tolerance.

Azure SQL Managed Instance (MI) targets near-100% SQL Server compatibility (SQL Agent, cross-database queries, Linked Servers, CLR, Service Broker). It runs inside your virtual network on a dedicated, delegated subnet (Microsoft.Sql/managedInstances). Instances use private IPs only; configure network security groups and route tables to permit management traffic to Azure control planes and data traffic to your applications. Integrate with Azure Private DNS (or custom DNS) so clients resolve the managed instance’s private FQDN. For hybrid connectivity and migration, ensure line-of-sight via site-to-site VPN or ExpressRoute. Migration paths include native backup/restore to Azure Blob Storage (WITH COPY_ONLY, WITH MOVE), online or offline migrations using Azure Database Migration Service (DMS), and transactional replication or log shipping where appropriate. MI’s Business Critical tier adds low-latency storage and high availability; General Purpose offers cost-effective storage with remote disks.

Azure Database for PostgreSQL and MySQL Flexible Server provide fine-grained control over maintenance windows, stop/start for cost savings, and network isolation via VNet integration. High availability options include same-zone synchronous standby for fastest failover and zone-redundant HA to withstand zonal failures (synchronous replication with automatic failover). Read replicas (in-region and, for many versions, cross-region) offload read workloads and support near-real-time analytics; they are asynchronous and not suitable for strictly consistent reads. Select compute and storage tiers based on IOPS/latency targets, and plan for connection failover handling in client libraries.

Nonrelational and Globally Distributed Stores

Azure Cosmos DB is a fully managed, multi-model, globally distributed database with turnkey global replication and single-digit millisecond reads and writes at the 99th percentile. Choose the API based on ecosystem fit and data model: Core (SQL) API for document and SQL-like queries with rich SDK support; MongoDB API for Mongo wire-protocol compatibility; Cassandra API for wide-column workloads; Gremlin API for graph traversal; and Table API for key/attribute scenarios. Throughput is provisioned in request units (RUs), using fixed or autoscale modes; design partitions and indexing to minimize RU consumption.

Partitioning is fundamental. Select a high-cardinality partition key that evenly distributes storage and traffic, avoids hot partitions, and aligns with your access patterns (e.g., tenantId or userId for multi-tenant writes, or a synthetic composite key to balance reads). Logical partitions are constrained in size and throughput; model to keep hot working sets distributed. You cannot change a container’s partition key after creation; migrations require new containers and data movement.

Cosmos consistency spans five tunable levels: Strong (linearizable, highest RU/latency), Bounded Staleness (predictable lag or version window), Session (client-centric reads-your-writes, popular default), Consistent Prefix (no out-of-order reads), and Eventual (maximum availability and performance with potential anomalies). Choose per-account defaults and override per-request when needed. Multi-region writes enable true multi-master for low-latency global writes and higher availability; handle conflict resolution via Last-Writer-Wins (on a designated property), custom policies, or application logic with stored procedures and conflict feed.

When choosing the right data store, match requirements to capabilities. Strict relational integrity, complex joins, and transactional guarantees favor Azure SQL Database or Managed Instance. Massive global scale, flexible schema, and low-latency geo-access favor Cosmos DB. Graph problems (social, recommendation, network topology) map to Cosmos DB Gremlin API or graph features in Azure SQL when relational collocation is beneficial. High-ingest time-series telemetry, ad hoc exploration, and near-real-time analytics align with Azure Data Explorer. Unstructured blobs, media, and large binary payloads belong in Azure Blob Storage or ADLS Gen2 with metadata in a complementary database.

Object and Analytical Storage

Azure Blob Storage is the foundation for unstructured data. Access tiers align storage cost with access patterns: Hot for frequent access, Cool for infrequent access with minimum 30-day retention, and Archive for long-term cold storage with minimum 180-day retention and hours-long rehydration. Premium block blob accounts on SSD deliver low-latency, high-transaction workloads such as ingestion pipelines. Lifecycle management policies automate transitions and deletions based on rules—last modified time, blob index tags, or prefixes—reducing cost without manual intervention.

Object replication for block blobs asynchronously mirrors objects and their versions between storage accounts (in the same or different regions). It requires blob versioning on source and destination and is policy-driven per container pair, supporting compliance and multi-region distribution while keeping independence from account-level redundancy choices. Immutability (WORM) is enforceable at container or blob scope via time-based retention and legal holds, with options like allowProtectedAppendWrites for append-only logs. Version-level immutability protects past states against tampering and ransomware.

Azure Data Lake Storage Gen2 adds a hierarchical namespace to Blob storage, providing true directories, atomic renames, and optimized file operations. Fine-grained POSIX-like ACLs control access at directory and file levels, with Access and Default ACLs, and are evaluated alongside Azure RBAC. Authenticate with Azure AD and OAuth2 for least privilege and auditability. Analytics integration is native: Azure Synapse Analytics and Azure Databricks access ADLS Gen2 through the ABFS driver with scalable throughput, while services like Azure Data Factory, Azure Purview, and Azure Machine Learning integrate for orchestration, governance, and model training. Design folder structures and ACL inheritance to isolate domains and support multi-team governance, and leverage features like change feed and soft delete for lineage and recovery.

Caching and Performance Acceleration

Azure Cache for Redis provides sub-millisecond data access, pub/sub, and distributed locking. Tiers map to availability and scale needs. Basic is single-node for dev/test. Standard adds a two-node primary/replica with automatic failover. Premium introduces clustering across shards, persistence (RDB snapshots and AOF), VNet support, and geo-replication in an active–passive topology. Enterprise and Enterprise Flash (Redis Enterprise) add Active-Active geo replication using CRDTs for multi-region writes, larger memory footprints, multi-threaded performance, and modules support; Flash augments DRAM with NVMe for massive caches at lower cost. Choose eviction policy to match key TTL and workload: allkeys-lru/allkeys-random when not all keys have TTLs; volatile-lru/volatile-ttl when only expiring keys should evict; and noeviction when write failures are acceptable over eviction. Persistence reduces data loss on failover at the cost of I/O and latency overhead; enable it only when required and tune snapshot intervals.

Integrate Redis as an aside cache for database query results, session state, and rate-limit counters. Ensure idempotent population, apply suitable TTLs, and implement circuit breakers. For clustered caches, shard keys deterministically; for Enterprise Active-Active, test conflict resolution semantics.

Security, Access, and Resiliency Patterns

Azure Storage access delegation uses SAS tokens and policies. Service SAS grants scoped access to a specific resource (container, blob, file share, queue, or table). Account SAS spans multiple services in the account and is powerful; protect it carefully. User delegation SAS (Blob service only) derives from Azure AD and a user delegation key, enabling per-user access control without account keys—ideal for multi-tenant applications and short-lived grants. Stored access policies (on containers, shares, queues, and tables) bind SAS tokens to a server-side policy so you can revoke or shorten access without rotating account keys; SAS without a stored access policy can only be revoked by expiring the token or rotating keys.

For encryption, Azure Storage uses service-side encryption by default. Customer-managed keys (CMK) stored in Azure Key Vault or Managed HSM provide centralized key lifecycle control and auditability. Encryption scopes allow different CMKs within the same storage account by container or prefix, supporting per-tenant keying. For per-blob client control, customer-provided keys (CPK) can be supplied on requests. Combine CMK at the account or scope level with CPK as needed for regulatory isolation.

Azure SQL security and resilience build on the platform tiers and replication features already described. Use auto-failover groups for coordinated cross-region failover and captive listener endpoints, and enable zone redundancy where available to withstand zonal failures. For sensitive data, apply dynamic data masking to obfuscate PII in query results for non-privileged users, and consider Always Encrypted with secure enclaves for client-side protection of columns when administrators must be prevented from viewing plaintext. Monitor RPO/RTO targets against your tier’s replication behavior and test failover routinely.

When planning end-to-end architectures, unify identity (Azure AD for SQL, storage, and analytics), apply least privilege with RBAC and ACLs, use Private Link or VNet integration to keep data off the public internet, and implement lifecycle, immutability, and replication policies to meet retention and DR objectives.

Practical Problem Scenario

Contoso Retail is launching a global e-commerce platform with volatile daytime traffic, strict PII controls, product media at petabyte scale, and near-real-time personalization. They require low-latency reads worldwide, minimal downtime, and governed data analytics.

Place the transactional catalog and order databases on Azure SQL Database using the vCore model:

Tier selection: Business Critical for orders (low-latency storage and fast failover) and General Purpose serverless for the catalog (traffic bursts with idle windows).
Why: vCore provides predictable sizing and Hybrid Benefit; Business Critical meets latency/HA for orders; serverless minimizes compute cost during low activity.

Configure an auto-failover group across paired regions for both databases and enable zone redundancy:

Why: A unified read/write listener simplifies application failover; zone redundancy protects against zonal outages; cross-region replicas meet DR and offer read scale for reporting.

Store product images and videos in Azure Blob Storage (general-purpose v2) with lifecycle management and object replication:

Policy: Hot for active items, Cool after 30 days, Archive after 180 days; replicate to a secondary region via object replication.
Why: Minimizes storage cost over time while providing asynchronous regional distribution independent of account redundancy; meets retention with immutability for legal assets (time-based WORM).

Build the customer profile and shopping cart service on Azure Cosmos DB (Core API) with multi-region writes and Session consistency:

Design: Partition by userId to distribute writes; enable autoscale RUs.
Why: Multi-master provides low-latency writes for a global audience and high availability; Session consistency ensures read-your-writes per user with strong UX guarantees and efficient RU usage.

Introduce Azure Cache for Redis Enterprise for session state, product detail caching, and rate limiting:

Mode: Clustering with Active-Active geo for multi-region writes.
Why: Sub-millisecond access and CRDT-based conflict resolution keep sessions and counters consistent across regions without a single write master.

Land clickstream and operational logs in Azure Data Lake Storage Gen2 with hierarchical namespaces and POSIX ACLs:

Integration: Stream ingestion writes to curated folders; Databricks and Synapse read via ABFS; enable change feed and soft delete.
Why: Fine-grained directory/file ACLs support multi-team governance; hierarchical namespace optimizes file operations; native analytics integration shortens time to insight.

Use Azure Database for PostgreSQL Flexible Server for the recommendation microservice:

HA: Zone-redundant synchronous standby; provision read replicas for feature experimentation.
Why: Rich Postgres extension ecosystem and JSONB capabilities fit the service; managed HA keeps RTO low; replicas offload reads.

Secure access with user delegation SAS for temporary media upload and CMK with encryption scopes per tenant:

Why: Eliminates exposure of account keys and enables per-tenant cryptographic isolation and auditability.

Migrate legacy order data from on-premises SQL Server to Azure SQL Managed Instance for archival processing and agent-driven tasks:

Steps: Assess with Data Migration Assistant; perform online migration via DMS; connect over ExpressRoute.
Why: MI preserves SQL Agent jobs and cross-database operations, easing modernization while keeping archival workloads close to cloud data.

This design meets global performance via Cosmos DB multi-region writes and Redis Enterprise, enforces governance with ADLS Gen2 ACLs and Storage immutability, delivers transactional integrity and fast failover with Azure SQL tiers and auto-failover groups, and optimizes cost through serverless compute and lifecycle policies.

← Identity · All domains · Compute and Application Architecture →

Practice these questions → · Timed practice on ExamRoll.io →