DP-900 Practice Question: Identify considerations for relational data on Azure
Your company has an Azure SQL Database that stores customer orders. You notice that long-running reports are causing blocking on the transactional tables. Which approach would minimize impact on transaction processing while still allowing reporting?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a read-only replica in Azure SQL Database Hyperscale
Creating a read-only replica offloads reporting queries to a separate copy, avoiding blocking on the primary transactional tables. Option A is wrong because READ UNCOMMITTED reduces blocking but risks dirty reads and inconsistent data. Option B is wrong because increasing the service tier to Business Critical provides more resources but does not separate reporting load. Option C is wrong because sharding distributes data across databases but does not prevent blocking; reporting queries still run on the same shards as transactional queries.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use the READ UNCOMMITTED isolation level for reporting queries
Why it's wrong here
READ UNCOMMITTED is the least restrictive isolation level and eliminates shared locks, so reporting queries avoid blocking against updates. However, it permits dirty reads—the query can return rows from transactions that later roll back—and also allows non-repeatable reads and phantoms, leading to inconsistent reports. Even with no blocking, the reporting workload still runs on the primary replica, consuming CPU, I/O, and memory alongside OLTP traffic. Therefore, it only hides the symptom while risking data correctness, not a proper workload isolation solution.
- ✗
Increase the service tier to Business Critical
Why it's wrong here
Business Critical is a higher service tier with increased CPU, memory, and I/O capacity, and it includes in-memory OLTP and automated backups. But all queries, including the long-running reporting ones, execute on the same primary replica and must contend with the same locks and latches held by write transactions. Blocking happens at the row or page level due to exclusive locks, and more resources do not alter locking semantics or remove the contention between the reporting read and the transactional write. This simply scales up the server, which can delay rather than prevent blocking and leaves the OLTP workload vulnerable to report-induced resource starvation.
- ✗
Shard the database by customer region
Why it's wrong here
Sharding the database by customer region is a horizontal partitioning technique that distributes rows across multiple independent databases, which can reduce overall query load per shard. However, each shard still contains the same customer and related tables, and each shard's primary replica will run both OLTP and reporting workloads concurrently. A reporting query that scans a large table on one shard will still compete with write transactions on that shard's tables and can be blocked by lock conflicts. Additionally, cross-region reports now require query federation and are more complex, yet the fundamental read/write contention on each shard remains unresolved.
- ✓
Create a read-only replica in Azure SQL Database Hyperscale
Why this is correct
In Azure SQL Database Hyperscale, you can provision one or more readable secondary replicas, each with its own compute resource and a separate connection string. The primary replica continuously sends log records to these secondaries, which apply them asynchronously and serve read-only workloads such as reporting. Because reporting queries are directed to a secondary replica, they no longer acquire locks on the primary, eliminating the blocking caused by those queries. This read scale-out pattern isolates the reporting traffic from the transactional workload while keeping data nearly current, with only negligible redo lag.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Azure SQL Database
Azure SQL Database is a fully managed relational database-as-a-service (DBaaS) in Microsoft Azure, based on the SQL Server engine, that handles scaling, backups, patching, and high availability automatically.
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.