Examkin

Question

Difficulty: HardDesign Relational Database HA and DR

You are designing a disaster recovery (DR) solution for a critical financial application migrating to Azure. The application database has the following requirements:
- Employs native cross-database queries and relies heavily on SQL Server Agent jobs.
- Requires database replication between East US (primary) and West US (secondary).
- The write workload must connect via a single endpoint that remains unchanged during a failover.
- Reporting applications must run read-only queries against the secondary region using a dedicated endpoint.
- All database traffic must remain within private virtual networks without traversing the public internet.

Which database configuration should you recommend to meet these requirements?

  1. Deploy a primary Azure SQL Managed Instance in East US and a secondary Azure SQL Managed Instance in West US. Configure an Auto-Failover Group between the instances and connect the application using the failover group's read-write and read-only listener endpoints.Answer
  2. B
    Deploy Azure SQL Database instances in East US and West US configured in an Elastic Pool. Enable active geo-replication and redirect the client application using a custom Azure Traffic Manager profile to route connections to the active primary.
  3. C
    Deploy a primary Azure SQL Managed Instance in East US and a secondary Azure SQL Managed Instance in West US. Configure Active Geo-Replication between the instances, and configure the application connection strings to point to the primary IP address, updating them via an Azure Function during failover.
  4. D
    Deploy SQL Server on Azure Virtual Machines in East US and West US. Configure an Always On Availability Group with SQL Server transaction logs hosted on Standard HDD managed disks to optimize costs.

Answer

Deploy a primary Azure SQL Managed Instance in East US and a secondary Azure SQL Managed Instance in West US. Configure an Auto-Failover Group between the instances and connect the application using the failover group's read-write and read-only listener endpoints.
The correct solution involves deploying Azure SQL Managed Instance in both regions and linking them via an Auto-Failover Group. SQL Managed Instance provides the necessary SQL Server features (SQL Server Agent and cross-database queries) required for legacy application migration. The Auto-Failover Group supplies a read-write listener endpoint and a read-only listener endpoint, satisfying the requirement for an unchanged client connection string for writes and a dedicated endpoint for reporting workloads. Because SQL Managed Instances are deployed directly within Azure Virtual Networks, the entire configuration maintains private network isolation.

Step-by-Step Solution

1
Evaluate application requirements for database features.
The requirement for native cross-database queries and SQL Server Agent jobs rules out Azure SQL Database (Single Database or Elastic Pool) and points to Azure SQL Managed Instance or SQL Server on Azure VMs.
Azure SQL Database does not support these features natively.
2
Evaluate endpoint requirements for HA/DR.
The requirement for a single unchanged endpoint for writes and a dedicated endpoint for reads points to Auto-Failover Groups, which provide read-write and read-only listener endpoints.
Active geo-replication or SQL VM configurations without additional routing components (like distributed network names or load balancers) do not natively offer these endpoints without connection string changes.
3
Assess storage performance constraints.
Standard HDD is rejected for hosting transaction logs.
Database transaction logs require low-latency and high-IOPS storage (Premium SSD or Ultra Disk) to function correctly under production workloads.

Key Concept

Selecting the correct Azure relational database service and configuring high availability/disaster recovery with Auto-Failover Groups to support legacy SQL Server features and read-scale listeners over private networks.
Estimated Time:3m 0s
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic