Examkin

Question

Difficulty: MediumDesign Relational Database HA and DR

You are designing a high availability and disaster recovery (HA/DR) solution for an on-premises database that is migrating to Azure. The database relies on SQL Server Agent jobs and performs cross-database queries. The disaster recovery design must support automatic failover to a secondary region, a Recovery Time Objective (RTO) of 1 hour, and a single connection endpoint that redirects application traffic without requiring connection string modifications during a failover. Which solution should you recommend?

  1. A
    Azure SQL Database single database configured with an auto-failover group
  2. B
    Azure SQL Managed Instance configured with active geo-replication and manual failover
  3. Azure SQL Managed Instance configured with a failover groupAnswer
  4. D
    SQL Server on Azure Virtual Machines configured with an Always On availability group using Standard HDD storage for database transaction logs

Answer

Azure SQL Managed Instance configured with a failover group
The correct answer is Azure SQL Managed Instance configured with a failover group. Azure SQL Managed Instance supports features required for legacy SQL Server migrations, including SQL Server Agent and cross-database queries. Configuring it with a failover group enables automatic failover to a secondary region and provides a read-write listener endpoint, allowing the application to connect using a single connection string that automatically redirects traffic during failover.

Step-by-Step Solution

1
Analyze the database compatibility and feature requirements.
The database requires SQL Server Agent and cross-database queries, which rules out Azure SQL Database single database and points to Azure SQL Managed Instance or SQL Server on Azure VMs.
Azure SQL Database single database does not support SQL Server Agent or cross-database queries.
2
Evaluate the HA/DR requirements.
The solution must support automatic failover to a secondary region with a single listener endpoint.
This eliminates configurations that require manual failover and application connection string changes.
3
Verify storage and performance requirements.
SQL Server on Azure VMs with Standard HDD is eliminated due to inadequate performance for database transaction logs.
Standard HDD is unsuitable for database transaction logs, which require low latency and high IOPS provided by Premium SSD or Ultra Disk.

Key Concept

Designing relational database HA and DR using failover groups and choosing the correct Azure SQL service tier based on application compatibility.
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic