AZ-305 Design data storage solutions: 340 practice questions

12 of the 340 Design data storage solutions questions in the Certsqill AZ-305 bank, shown in full below. Each one carries an explanation for every option, not just the correct one — the wrong answers are where the marks go.

Preparing for AZ-305? Take the free 5-min readiness check →

1. Business Critical tier with a built-in readable secondary: Which service tier satisfies all requirements?

1. General Purpose tier deployed with a zone-redundant configuration

   General Purpose tier uses remote storage (Azure Premium Storage) for HA — failover takes 20-30 seconds (not sub-second). It does not support in-memory OLTP and does not include readable secondary replicas.
2. Business Critical tier with a built-in readable secondary ✓

   Business Critical tier provides: 99.99% SLA, Always On availability group-based HA with sub-second automatic failover (no data loss), in-memory OLTP support, and a built-in readable secondary replica at no extra cost — satisfying all four requirements.
3. General Purpose tier with active geo-replication to another region

   Active geo-replication provides cross-region DR (RTO minutes, RPO seconds) but does not provide sub-second local failover. It also does not add in-memory OLTP support or eliminate the 20-30 second failover time within the primary region.
4. Hyperscale tier configured with a single named replica

   Hyperscale supports readable replicas (named replicas) and has high performance, but its SLA depends on replica count, and in-memory OLTP support is not available in Hyperscale. Business Critical is the correct tier for in-memory OLTP.

Business Critical is the only tier combining sub-second Always On failover, in-memory OLTP, built-in readable secondary, and 99.99% SLA.

2. Azure SQL Managed Instance as the fully managed PaaS: Which Azure SQL option best supports this migration with

1. Azure SQL Managed Instance as the fully managed PaaS target ✓

   Azure SQL Managed Instance provides near 100% SQL Server compatibility including: SQL Server Agent, cross-database queries, CLR assemblies, and linked servers (including to Oracle via OLEDB). It is a fully managed PaaS service, eliminating OS and SQL Server patch management.
2. Azure SQL Database Elastic Pool with elastic cross-database query

   Elastic Pool with elastic query can support some cross-database queries, but it does not support SQL Server Agent, CLR assemblies, or linked servers to Oracle. This is not a near-complete SQL Server compatibility solution.
3. SQL Server installed on an Azure Virtual Machine (IaaS)

   SQL Server on VM supports all SQL Server features including Agent, CLR, and linked servers — but it is IaaS (not PaaS), requiring OS patching, storage management, and VM maintenance. The requirement is for a fully managed PaaS service.
4. Azure SQL Database deployed as a single standalone database

   Azure SQL Database does not support SQL Server Agent, CLR assemblies (limited support only), cross-database queries within the same server, or linked servers. These features require SQL Managed Instance.

Azure SQL Managed Instance provides near 100% SQL Server compatibility (Agent, CLR, linked servers, cross-database queries) as a fully managed PaaS — the correct lift-and-shift target for feature-rich SQL Server workloads.

3. Product catalog: Which consistency level combination is correct?

1. Product catalog: Strong. Shopping cart: Eventual. Order history: Strong (fully synchronous across every write region).

   Eventual consistency for the shopping cart allows out-of-order reads — cart items could appear to disappear, violating monotonic reads. Strong consistency for all containers negates the multi-region write performance benefit and has significant write latency in global deployments.
2. Product catalog: Strong. Shopping cart: Session. Order history: Bounded Staleness (per-session monotonic reads only).

   Session consistency provides monotonic reads within the same session (client connection), not across sessions/clients. If a customer uses multiple devices or sessions, a different shopping cart session may not see monotonic reads. Consistent Prefix is more appropriate for global monotonic read guarantees across all clients.
3. Product catalog: Bounded Staleness. Shopping cart: Eventual. Order history: Session (which provides no defined staleness lag bound).

   Bounded Staleness allows stale reads — inconsistent with 'always latest version' for product catalog. Eventual allows out-of-order reads — items could disappear from the shopping cart, violating the requirement. Session consistency doesn't provide the lag-bound guarantee for order history.
4. Product catalog: Strong. Shopping cart: Consistent Prefix. Order history: Bounded Staleness (MaxLagTime=5s, MaxLagCount=100000). ✓

   Strong consistency ensures reads always return the latest committed write — correct for product catalog. Consistent Prefix guarantees reads never see out-of-order writes (no disappearing items) with eventual consistency — correct for shopping cart. Bounded Staleness provides the exact SLA-bound lag guarantee (5 seconds or 100K ops) — correct for order history.

Strong for product catalog (always latest), Consistent Prefix for shopping cart (no disappearing items across clients), Bounded Staleness for order history (defined lag bound) maps exactly to the three requirement profiles.

4. customerId: Which partition key is optimal?

1. customerId ✓

   customerId is the optimal partition key: access pattern 1 (all orders for a customer) becomes a single-partition query — efficient and cheap. Access pattern 2 (single order by orderId) requires a cross-partition query, but this is a point read that is still fast. customerId has sufficient cardinality (10,000 active customers) to avoid hot partitions.
2. status

   status has extremely low cardinality (3 values: pending/shipped/delivered) — this creates hot partitions as thousands of orders share the same status partition. Low-cardinality partition keys are a fundamental anti-pattern in Cosmos DB.
3. orderId

   orderId as partition key makes each order its own partition — access pattern 1 (all orders for a customer) requires a cross-partition fan-out query across all customer's orders, which is expensive. While access pattern 2 (single order lookup) becomes a point read, the customer's orders query is the primary access pattern and should drive partition key selection.
4. orderDate (truncated to day)

   orderDate truncated to day means all orders on the same day land in the same partition — on a day with 50,000 orders, one partition handles all of that day's writes, creating a hot write partition. Order history queries by date also require cross-partition scans.

customerId as partition key optimizes the primary access pattern (all customer orders = single-partition query) while providing sufficient cardinality to avoid hot partitions across 10,000 active customers.

5. Last-Write-Wins using the default _ts system property as: Which conflict resolution policy minimizes applicati

1. Use the Cosmos DB change feed to detect conflicts and apply application-level merge logic

   Change feed conflict detection is a valid pattern for auditing but doesn't resolve conflicts automatically — it requires application-level logic to handle and reapply corrections, adding significant complexity compared to LWW.
2. Last-Write-Wins (LWW) using the default _ts (timestamp) system property as the conflict resolution path ✓

   LWW with _ts automatically resolves conflicts by keeping the write with the highest timestamp value — no application code changes required. Cosmos DB handles conflict resolution automatically. This is the simplest approach when the business rule is 'latest write wins.'
3. Custom conflict resolution procedure with a JavaScript stored procedure that merges both document versions

   Custom conflict resolution procedures are powerful but require writing and deploying JavaScript stored procedures — significant application code effort. LWW requires zero application code changes and satisfies 'minimize code changes.'
4. Disable multi-region writes and use a single write region with read replicas in both regions

   Single write region eliminates conflict scenarios but also eliminates low-latency writes for one region — defeating the purpose of multi-region write. Users in the secondary region would have high write latency routing to the primary region.

Last-Write-Wins conflict resolution with the _ts timestamp property is the zero-application-code approach for automatic conflict resolution in multi-region write Cosmos DB deployments.

6. Gremlin API, MongoDB API, Cassandra API: Which Cosmos DB API matches each application?

1. (A) NoSQL API, (B) MongoDB API, (C) Table API

   NoSQL API (formerly Core SQL) is a document API — graph traversal is not natively efficient without graph-specific primitives. Table API supports key-value access similar to Azure Table Storage but lacks wide-column time-series query optimization with clustering keys.
2. (A) NoSQL API with graph extension, (B) MongoDB API, (C) Cassandra API

   There is no 'graph extension' for the NoSQL API — Gremlin API is the dedicated graph API. Using NoSQL API for graph traversal requires custom application-level graph traversal logic.
3. (A) Gremlin API, (B) MongoDB API, (C) Cassandra API ✓

   Gremlin API supports property graph model with graph traversal queries (Gremlin query language) — ideal for friends-of-friends and relationship traversal. MongoDB API provides wire-protocol compatibility with MongoDB — existing MongoDB applications work with minimal code changes. Cassandra API supports wide-column store model with CQL — ideal for time-series data queried by partition key (deviceId) and clustering key (timestamp).
4. (A) Gremlin API, (B) NoSQL API, (C) MongoDB API

   Using NoSQL API for a MongoDB migration requires significant query language changes (SQL-like syntax vs MongoDB operators). MongoDB API provides direct wire-protocol compatibility. MongoDB API for IoT time-series doesn't provide the partition/clustering key query optimization of Cassandra.

Gremlin API for graph traversal, MongoDB API for MongoDB compatibility, Cassandra API for wide-column time-series — each Cosmos DB API is purpose-built for a specific data model.

7. Hot tier, Cool tier, Archive tier: Which blob access tier assignment minimizes storage cost?

1. (A) Cool tier, (B) Cool tier, (C) Archive tier

   Cool tier for product images accessed thousands of times per day would result in extremely high access transaction and data retrieval costs — Hot tier is cost-optimal for high-frequency access.
2. (A) Hot tier, (B) Archive tier, (C) Archive tier

   Archive tier for financial reports accessed 2-3 times per year incurs high retrieval costs and hours-long rehydration time — Cool tier provides a better balance for data accessed a few times annually. Archive is only appropriate when access is extremely rare or unexpected.
3. (A) Hot tier, (B) Cool tier, (C) Cold tier

   Cold tier (GA 2023) is positioned between Cool and Archive — it has lower storage cost than Cool but higher than Archive, with access costs between Cool and Archive. For 7-year regulatory backups never expected to be accessed, Archive tier provides the lowest storage cost.
4. (A) Hot tier, (B) Cool tier, (C) Archive tier ✓

   Hot tier for frequently accessed data (product images) — lowest access cost, highest storage cost. Cool tier for infrequently accessed data with at least 30-day minimum retention (financial reports) — lower storage cost, higher access cost. Archive tier for rarely/never accessed data with at least 180-day minimum (backups) — lowest storage cost (~$0.002/GB/month), high retrieval cost and rehydration time.

Hot for daily-access data, Cool for infrequent access (2-3x/year), Archive for compliance backups never expected to be accessed — matching tier to access frequency minimizes total storage cost.

8. Azure Blob Storage Lifecycle Management policy with rules: Which Azure Blob Storage feature handles this?

1. Azure Blob Storage Lifecycle Management policy with rules to transition to Cool after 30 days and delete after 365 days ✓

   Blob Lifecycle Management policies define rules with conditions (last modified or last accessed time) and actions (tier transition or delete). A rule can: move to Cool tier after 30 days of last modification, then delete blobs after 365 days — completely automated without manual intervention.
2. Azure Event Grid with Blob Created events triggering an Azure Function that schedules tier transitions and deletions on a timer

   Event Grid + Functions can implement custom lifecycle logic, but this is significantly more complex than the built-in Lifecycle Management feature that handles exactly this pattern natively without custom code.
3. Azure Data Factory pipeline with daily scheduled runs that check blob age and move blobs between storage tiers

   ADF is a data integration service for ETL/ELT pipelines — using it for blob tier management adds unnecessary complexity when Lifecycle Management provides this natively.
4. Azure Storage Explorer paired with a scheduled script that manually re-tiers older blobs and deletes them past retention

   Manual scripted management via Storage Explorer requires ongoing operational effort and scheduling infrastructure — the opposite of 'automated without manual intervention.' Lifecycle Management provides native automation.

Azure Blob Lifecycle Management policies automate tier transitions and deletion based on time-based rules — the purpose-built feature for automated storage cost optimization.

9. Configure immutable blob storage with a locked time-based: Which storage configuration achieves this?

1. Enable Azure Blob versioning together with soft delete configured to a 7-year retention period so that every prior version of each trade confirmation is preserved for the full compliance window

   Blob versioning retains previous versions when blobs are modified, and soft delete protects against accidental deletion — but both can be disabled or overridden by storage account administrators. They do not provide the immutability guarantee required by SEC 17a-4(f).
2. Configure immutable blob storage with a locked time-based retention policy (7-year retention) on the container or storage account, verified by a third-party assessment for SEC 17a-4(f) ✓

   Azure immutable blob storage with a locked (not unlocked) time-based retention policy prevents modification and deletion of blobs for the retention period — even by storage account owners and administrators. Microsoft has obtained Cohasset Associates assessment for SEC Rule 17a-4(f) compliance for immutable storage. The policy must be locked (not just configured) to be enforceable.
3. Encrypt every blob with customer-managed keys (CMK) held in Azure Key Vault and apply an automatic key rotation policy throughout the entire 7-year retention period

   CMK encryption protects data confidentiality but does not prevent modification or deletion of blobs. Encryption is orthogonal to immutability — they address different requirements.
4. Apply a CanNotDelete resource lock to both the storage account and the container so administrators cannot remove the trade confirmation records during the 7-year retention window

   CanNotDelete resource lock prevents deletion of the storage account but does not prevent deletion or modification of individual blobs within the account. Users with Owner role can remove the lock. This does not meet WORM or SEC 17a-4(f) requirements.

Locked time-based immutability policies on Azure Blob Storage provide verifiable WORM compliance — preventing modification/deletion even by admins — with SEC Rule 17a-4(f) third-party certification.

10. Azure Data Lake Storage Gen2: Which Azure storage service should they use?

1. Standard Azure Blob Storage with a flat namespace, relying on Azure AD RBAC for fine-grained access control

   Standard Azure Blob Storage with flat namespace does not support atomic directory rename (rename requires copy+delete, not atomic) or POSIX ACLs (only RBAC/SAS at container level). This makes it unsuitable for Spark ETL pipelines that rely on atomic renames for transaction commits.
2. Azure NetApp Files with POSIX ACLs served over the NFS v4.1 protocol to the analytics cluster

   Azure NetApp Files provides enterprise NFS with POSIX ACLs but is a high-performance, high-cost service designed for latency-sensitive workloads. It is not the native Azure analytics storage layer and lacks direct ABFS integration with Synapse and Databricks.
3. Azure Data Lake Storage Gen2 (ADLS Gen2) — Azure Blob Storage with Hierarchical Namespace (HNS) enabled ✓

   ADLS Gen2 combines Azure Blob Storage's cost-effective object storage with a Hierarchical Namespace that enables: atomic directory renames (critical for Spark commit protocols), POSIX-compliant ACLs at file/directory level, and native integration with Synapse Analytics and Databricks via ABFS (Azure Blob File System) driver.
4. Azure Files premium tier using the NFS 3.0 protocol with POSIX-style directory permissions

   Azure Files provides file share semantics (SMB/NFS) and POSIX-like permissions via NFS, but it is not optimized for big data analytics workloads at petabyte scale. It lacks native ABFS driver integration with Synapse/Databricks and is not cost-effective for large analytics datasets.

ADLS Gen2 (Blob Storage with Hierarchical Namespace) provides atomic rename, POSIX ACLs, and native ABFS integration with Synapse and Databricks — the purpose-built analytics storage layer.

11. Azure NetApp Files with Ultra performance tier: Which storage option meets all requirements?

1. Azure Files Premium tier over the NFS 3.0 protocol

   Azure Files Premium NFS 3.0 provides low latency (typically 1-3ms) but does not achieve sub-millisecond latency or the 4 GB/s throughput of NetApp Files Ultra. Additionally, it supports NFS 3.0 only — not NFS v4.1 as required.
2. Azure Ultra Disk shared disk across multiple VMs

   Azure Ultra Disk provides sub-millisecond latency and high throughput, but shared disk (shared managed disk) has limited node support and does not provide an NFS file system interface — it is block storage, not NFS. Oracle on NFS requires a network file system protocol, not block storage.
3. Azure Blob Storage using the NFSv3 protocol

   Azure Blob Storage NFSv3 support is designed for data lake scenarios (analytics workloads), not low-latency database storage. It does not provide sub-millisecond latency or NFS v4.1 support.
4. Azure NetApp Files with Ultra performance tier ✓

   Azure NetApp Files Ultra tier provides sub-millisecond latency (< 1ms), throughput up to 4,500 MiB/s per volume, and full NFS v4.1 support with NFSv4.1 ACLs — meeting all three requirements. It is purpose-built for latency-sensitive, high-throughput enterprise workloads like Oracle databases.

Azure NetApp Files Ultra tier provides sub-millisecond latency, 4+ GB/s throughput, and NFS v4.1 support — purpose-built for latency-sensitive enterprise workloads like Oracle databases.

12. Deploy Azure File Sync agent on each branch file server: Which solution achieves this?

1. Deploy Azure File Sync agent on each branch file server, register with a Storage Sync Service, enable cloud tiering to tier cold files to Azure Files automatically ✓

   Azure File Sync: deploys an agent on Windows Server at each branch, syncs with Azure Files as the cloud endpoint (master copy). Cloud tiering: files not accessed for a configured period are tiered to Azure Files (only a stub/pointer remains on-premises). Hot files remain local for low-latency access. VPN provides the connectivity for sync and tier recall.
2. Use Azure Data Box to seed all branch files into Azure Files, then configure DFS Replication between the branch servers to keep each location synchronized going forward

   Azure Data Box is for large one-time data transfer, not ongoing sync. DFS Replication replicates between on-premises servers but does not consolidate to Azure Files as master copy or provide cloud tiering to Azure.
3. Deploy Azure Virtual Desktop with FSLogix profile containers stored in Azure Files so that every branch user's profile roams from the central share

   Azure Virtual Desktop with FSLogix addresses profile storage for virtual desktops — it does not provide branch office file server consolidation, local file caching, or cloud tiering for general file shares.
4. Mount the Azure Files SMB share directly on each branch server over the site-to-site VPN and decommission the local branch file servers

   Direct SMB mount to Azure Files over VPN provides no local caching — all file access traverses the WAN, resulting in high latency for 50-100 users at each branch. This is the opposite of 'keep frequently accessed files local.'

Azure File Sync with cloud tiering caches hot files locally at each branch, syncs to Azure Files as master copy, and automatically tiers cold files to Azure — the purpose-built branch office file consolidation solution.

Other AZ-305 domains

• Design infrastructure solutions — 572 questions →
• Design identity, governance, and monitoring solutions — 470 questions →
• Design business continuity solutions — 270 questions →
• All 1652 AZ-305 questions →