Examkin

Question

Difficulty: HardDesign Relational Database HA and DR

A healthcare provider is designing the disaster recovery architecture for its patient management application. The database layer has the following requirements:
- Must support SQL Server Agent jobs to schedule nightly data synchronization.
- Must support cross-database queries between the patient records database and the billing database.
- Must support automatic failover to a secondary Azure region, utilizing a single connection endpoint that automatically redirects traffic without requiring application reconfiguration.
- Must ensure that all databases are isolated from the public internet, using private network paths for all communication.

Which database deployment and high availability/disaster recovery (HA/DR) configuration should you recommend?

  1. A
    Deploy Azure SQL Database single databases in a Business Critical tier in both regions, configure active geo-replication, and configure the application to connect using the primary database's private endpoint.
  2. B
    Deploy Azure SQL Database Elastic Pools in both regions, configure active geo-replication, and use Azure Traffic Manager to route traffic to the active database endpoint.
  3. Configure Azure SQL Managed Instances in a failover group across two regions, establish global virtual network peering between the virtual networks hosting the instances, and configure the application to connect using the failover group's read-write listener.Answer
  4. D
    Deploy SQL Server on Azure Virtual Machines in both regions, configure Always On availability groups, and host the database transaction log files on Standard HDD virtual disks to minimize costs.

Answer

Configure Azure SQL Managed Instances in a failover group across two regions, establish global virtual network peering between the virtual networks hosting the instances, and configure the application to connect using the failover group's read-write listener.
The correct solution uses Azure SQL Managed Instance to support SQL Server Agent and cross-database queries. Failover groups provide automatic failover and a single read-write listener endpoint, and global virtual network peering ensures private network communication between the regions without exposing endpoints to the public internet.

Step-by-Step Solution

1
Analyze database compatibility requirements.
SQL Server Agent and cross-database queries require Azure SQL Managed Instance or SQL Server on Azure VMs, ruling out Azure SQL Database single databases and Elastic Pools.
Azure SQL Database single databases and Elastic Pools do not natively support SQL Server Agent or cross-database queries.
2
Evaluate high availability and disaster recovery requirements.
Failover groups are required to support automatic failover with a single connection listener endpoint.
Active geo-replication only supports manual failover and does not provide a single connection listener endpoint.
3
Check network security and storage performance requirements.
Virtual network peering must be used for private network paths, and high-performance disks (Premium SSD or Ultra Disk) are required for database transaction logs to prevent write bottlenecks.
Standard HDD is unsuitable for transaction logs due to IOPS and throughput limitations, and the solution must avoid public internet exposure.

Key Concept

Selecting the appropriate Azure SQL relational database deployment option and configuring failover groups with network isolation to satisfy enterprise compatibility, performance, and recovery constraints.
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic