Examkin

Question

Difficulty: Very hardDesign Relational Database HA and DR

A multinational logistics enterprise is migrating an on-premises package tracking application to Azure. The application's database layer has the following requirements:
- Must support SQL Server Agent jobs for hourly scheduled maintenance and three-part name cross-database queries.
- Must tolerate a single Availability Zone failure in the primary region with a recovery time objective (RTO) of less than 30 seconds and a recovery point objective (RPO) of zero.
- Must support disaster recovery to a paired secondary region with a regional RTO of under 20 minutes and a regional RPO of less than 5 seconds, using a single connection string that automatically redirects application traffic during failover.
- Must minimize ongoing administrative effort for database engine patching and operating system maintenance.

Which database solution should you design to meet these requirements?

  1. A
    Azure SQL Managed Instance in the General Purpose tier configured with zone-redundant storage (ZRS) in the primary region, replicated to a secondary region using active geo-replication.
  2. B
    Azure SQL Database in the Business Critical tier configured with zone redundancy in the primary region, replicated to a secondary region using an Auto-Failover Group.
  3. Azure SQL Managed Instance in the Business Critical tier configured with zone redundancy in the primary region, replicated to a secondary region using an Auto-Failover Group.Answer
  4. D
    SQL Server on Azure Virtual Machines (VMs) configured in an Always On Availability Group across Availability Zones in the primary region, using Standard SSD storage for the transaction log drives to reduce costs, with asynchronous replication to a secondary region.

Answer

Azure SQL Managed Instance in the Business Critical tier configured with zone redundancy in the primary region, replicated to a secondary region using an Auto-Failover Group.
The correct solution uses Azure SQL Managed Instance in the Business Critical tier with zone redundancy. SQL Managed Instance provides the required compatibility features (SQL Server Agent and cross-database queries) while minimizing administrative effort as a fully managed PaaS. Enabling zone redundancy in the Business Critical tier places replicas across zones, ensuring synchronous replication (RPO of zero) and fast failovers (RTO under 30 seconds). Replicating to a secondary region using an Auto-Failover Group satisfies the RPO and RTO disaster recovery requirements, and provides a single endpoint listener to automatically route application traffic.

Step-by-Step Solution

1
Analyze compatibility requirements.
SQL Server Agent and three-part name cross-database queries are required, which eliminates Azure SQL Database.
Azure SQL Database does not support these native SQL Server features.
2
Evaluate administrative overhead constraints.
Eliminate SQL Server on Azure VMs.
Running SQL Server on VMs requires manual patching, backups, and OS management, violating the constraint to minimize administrative effort.
3
Evaluate local high availability requirements.
Select Azure SQL Managed Instance Business Critical tier with zone redundancy enabled.
Business Critical tier uses fast Always On Availability Groups under the hood to meet RPO of zero and RTO under 30 seconds. General Purpose tier has a longer failover time (1-2 minutes) because it requires bootstrapping a new instance.
4
Evaluate disaster recovery and redirection requirements.
Implement an Auto-Failover Group to the paired secondary region.
Auto-Failover Groups support asynchronous replication (RPO under 5 seconds) and provide a single read-write listener endpoint for automatic traffic redirection during regional failover.

Key Concept

Designing high availability and disaster recovery for relational databases in Azure based on compatibility, performance, RTO/RPO, and administrative overhead.
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic