DP-900 Describe core data concepts Practice Question
A retail company uses Azure SQL Database to store transactional data. They need to ensure that reporting queries do not impact the performance of the transactional workload. Which solution should you recommend?
⚠ Common exam trap
A common mix-up: candidates confuse scaling up the database (Option B) with workload isolation, not realizing that scaling up only adds more resources but does not separate read and write operations, so reporting queries can still cause blocking or resource contention on the primary.
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
✓
Configure a read replica in Azure SQL Database
A read replica in Azure SQL Database allows reporting queries to be offloaded to a read-only copy of the database, isolating them from the primary transactional workload. This ensures that reporting activities do not consume resources (CPU, IO, memory) on the primary instance, preventing performance degradation for transactional operations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Configure a read replica in Azure SQL Database
Why this is correct
Configuring a read replica in Azure SQL Database creates a separate, readable secondary instance that handles reporting and analytical queries without consuming the primary's CPU, I/O, or locks. Azure SQL Database's active geo-replication or built-in read scale-out allows the replica to maintain a transactionally consistent (though potentially slightly delayed) copy of the data, enabling reporting workloads to run alongside OLTP without interference. This is the correct approach because it physically isolates the reporting load from the transactional database engine.
- ✗
Increase the DTU or vCore limit of the database
Why it's wrong here
Increasing the DTU or vCore allocation of the database raises the compute and storage performance ceiling, but both the transactional and reporting workloads still run against the same database engine, sharing the same buffer pool, transaction log, and locks. While more capacity may temporarily reduce resource contention, it does not prevent reporting queries from competing with OLTP for the same latches and I/O, and it becomes a costly, scale-by-proxy solution rather than an architectural separation of workloads.
- ✗
Add indexes to the reporting tables
Why it's wrong here
Adding indexes to reporting tables can accelerate specific queries via better seek operations or covering indexes, but those queries still execute on the primary database. Index maintenance itself imposes overhead on every insert, update, and delete, and reporting queries continue to take shared locks and consume the primary's resources, so the fundamental problem of workload interference remains unresolved.
- ✗
Partition the largest tables by date
Why it's wrong here
Partitioning the largest tables by date enables partition elimination and simplifies data lifecycle management, such as sliding-window archival, but it does not offload any query processing to a separate resource. Reporting queries still run on the primary database, consuming its CPU, I/O, and concurrency slots while also potentially causing metadata and lock contention with transactional workloads, so this option only addresses data manageability, not workload isolation.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
Key term
Transactional data
Transactional data is information that captures a specific event or exchange, such as a sale, a payment, or a system log entry, and is recorded in a database or log system.
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 →
Same concept, more angles
1 more way this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. An organization uses Azure SQL Database and needs to maintain a copy of the database for read-only reporting without affecting the production workload. Which feature should they use?
easy- A.Azure SQL Database read replica
- B.Automated backups
- ✓ C.Active geo-replication
- D.Failover groups
Why C: Active geo-replication (Option C) creates a readable secondary replica of an Azure SQL Database in a different Azure region. This secondary replica is continuously updated asynchronously from the primary and can be used for read-only query workloads, offloading reporting traffic without impacting the production database's performance or transaction throughput.
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.