DP-900 Describe core data concepts Practice Question
Your organization is migrating on-premises SQL Server databases to Azure. The databases include a mission-critical OLTP system that requires high availability with automatic failover and a reporting database that is used for read-only queries. You need to choose the appropriate Azure SQL deployment options for each workload. The OLTP system must have a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds. The reporting database should be cost-effective and can tolerate up to 5 minutes of data loss. What should you recommend?
⚠ Common exam trap
Watch out — candidates often confuse the high availability features of Azure SQL Database single database (active geo-replication) with the stricter RPO/RTO guarantees of Managed Instance failover groups, or they assume that SQL Server on Azure VMs with Always On Availability Groups is the only option for such requirements, overlooking the managed service benefits.
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
✓
Use Azure SQL Database Managed Instance with a failover group for OLTP, and use a read-only replica of the Managed Instance for reporting.
Azure SQL Database Managed Instance supports failover groups that provide automatic failover across regions with an RPO of less than 5 seconds and an RTO of less than 30 seconds, meeting the OLTP requirements. The read-only replica of the Managed Instance can be used for reporting queries without impacting the primary OLTP workload, and it is cost-effective as it does not require a separate database instance.
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 SQL Server on Azure Virtual Machines with Always On Availability Groups for both workloads.
Why it's wrong here
SQL Server on Azure VMs with Always On Availability Groups places the burden of managing VMs, patching, backups, and failover orchestration on you. While readable secondaries can serve reporting and the listener provides a single connection point, failover is not automatic and requires manual intervention or custom automation, potentially exceeding the required RPO/RTO. This IaaS approach is operationally heavy compared to a fully managed PaaS failover group.
- ✗
Use Azure SQL Database Hyperscale for OLTP and Azure SQL Database serverless for reporting.
Why it's wrong here
Azure SQL Database Hyperscale is built for massive scalability but uses asynchronous page-server replication, so its RPO is typically at least 5 minutes, which fails the low-RPO requirement for OLTP. Serverless is a compute-pricing option, not an HA/DR feature, and it does not provide a failover group or a read-only replica for reporting. Combining these two does not deliver a managed, low-latency failover mechanism for the transactional workload.
- ✓
Use Azure SQL Database Managed Instance with a failover group for OLTP, and use a read-only replica of the Managed Instance for reporting.
Why this is correct
Azure SQL Database Managed Instance with a failover group automatically replicates to a secondary instance, providing a low RPO (usually within 5–10 seconds) and automated failover that preserves the same connection string. The failover group's secondary can also serve as a read-only replica, offloading reporting workloads without interfering with OLTP transactions. This PaaS solution minimizes operational overhead while meeting both the high-availability and reporting needs, making it the correct choice.
- ✗
Use Azure SQL Database single database with active geo-replication for both workloads.
Why it's wrong here
Active geo-replication for Azure SQL Database is asynchronous, so the secondary can lag the primary, meaning RPO can be higher and RTO depends on manual or custom failover logic. Using the same geo-secondary for both reporting and DR risks contention and complicates meeting a strict low-RPO target. Without a failover group, there is no automatic failover or built-in listener, so this is less managed and less reliable than a Managed Instance failover group.
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
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. 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.