Examkin

Question

Difficulty: MediumDesign Relational Database HA and DR

An organization is designing a high availability and disaster recovery (HA/DR) solution for a retail application database layer that is migrating to Azure. The database workload depends on SQL Server Agent for scheduled data-aggregation tasks and requires cross-database queries. The solution must meet the following requirements:
- Recover from a regional outage automatically with a Recovery Time Objective (RTO) of less than 1 hour.
- Maintain a single connection string for the application tier that does not require modification during a regional failover.
- Allow read-heavy reporting applications to query the secondary replica in the secondary region to reduce query impact on the primary database.

Which two options should you recommend to meet these requirements? (Select TWO.)

  1. Deploy the databases to Azure SQL Managed Instance.Answer
  2. Create a Failover Group between the primary and secondary Azure SQL Managed Instances, and configure the reporting application to use the read-only listener endpoint.Answer
  3. C
    Deploy the databases to Azure SQL Database Single Database within an Elastic Pool.
  4. D
    Configure Active Geo-Replication between Azure SQL Managed Instances, and configure the reporting application to connect directly to the secondary instance's fully qualified domain name (FQDN).
  5. E
    Deploy SQL Server on Azure Virtual Machines using Standard HDD disks for the database transaction logs, and configure an Always On Availability Group.

Answer

Deploy the databases to Azure SQL Managed Instance, and create a Failover Group between the primary and secondary Azure SQL Managed Instances, configuring the reporting application to use the read-only listener endpoint.
The correct design employs Azure SQL Managed Instance because the application relies on SQL Server Agent and cross-database queries, which are not natively supported by Azure SQL Database. To automate failover and prevent client-side configuration changes, a Failover Group must be used. The Failover Group includes a read-write listener that stays active during failover, and a read-only listener that routes reporting traffic to the secondary instance in the disaster recovery region.

Step-by-Step Solution

1
Analyze the application's database compatibility requirements.
Identify that SQL Server Agent and cross-database queries are required. This rules out Azure SQL Database Single Database and Elastic Pools, pointing to Azure SQL Managed Instance as the correct platform.
Choosing the correct deployment option ensures legacy application features work without rewrite overhead.
2
Evaluate the disaster recovery failover automation and endpoint requirements.
Determine that a Failover Group is required because it supports automatic failover and provides a DNS listener endpoint to avoid application connection string changes.
Active Geo-Replication only supports manual failover and lacks unified listener endpoints for failovers.
3
Address the read-scale offloading requirements for reporting workloads.
Point the reporting application to the read-only listener of the Failover Group to target the secondary replica in the failover region.
Using the read-only listener endpoint guarantees that reporting queries are routed to the secondary replica, reducing the workload on the primary instance.

Key Concept

Designing relational database high availability and disaster recovery in Azure requires aligning database engine compatibility with replication capabilities such as Failover Groups.
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic