Examkin

Question

Difficulty: HardDesign Relational Database HA and DR

A financial services company is planning to migrate its core transactional database to Azure. The database layer has the following requirements:
- The database application relies on third-party system agents that require direct access to the underlying operating system.
- The solution must achieve high availability (HA) within the primary region with a 99.99%99.99\% availability service level agreement (SLA), zero data loss (RPO=0RPO = 0), and automatic failover.
- For disaster recovery (DR), the data must be replicated to a secondary region, supporting manual failover with a Recovery Point Objective (RPORPO) of under 1010 seconds.
- The database transaction logs have extremely high write activity and require sub-millisecond write latency.

Which of the following database architectures should you recommend?

  1. Deploy SQL Server on Azure Virtual Machines. Configure an Always On availability group with synchronous-commit replicas across Availability Zones in the primary region and an asynchronous-commit replica in the secondary region. Store the transaction logs on Premium SSD v2 or Ultra Disk volumes.Answer
  2. B
    Deploy Azure SQL Managed Instance in the Business Critical service tier. Configure an auto-failover group between the primary and secondary regions, and route read-only reporting workloads to the secondary replica.
  3. C
    Deploy SQL Server on Azure Virtual Machines. Configure a Failover Cluster Instance (FCI) using Standard SSD managed disks and Azure Shared Disks across Availability Zones for high availability, and configure Azure Site Recovery to replicate the database VMs to the secondary region.
  4. D
    Deploy SQL Server on Azure Virtual Machines. Configure database mirroring to replicate data to the secondary region, and store both the database data and transaction logs on Standard HDD volumes to minimize storage costs.

Answer

Deploy SQL Server on Azure Virtual Machines. Configure an Always On availability group with synchronous-commit replicas across Availability Zones in the primary region and an asynchronous-commit replica in the secondary region. Store the transaction logs on Premium SSD v2 or Ultra Disk volumes.
Deploying SQL Server on Azure Virtual Machines satisfies the requirement for direct operating system access needed by third-party agents. Configuring a SQL Server Always On availability group with synchronous-commit replicas across Availability Zones in the primary region provides automatic failover and zero data loss (RPO=0RPO = 0), meeting the 99.99%99.99\% SLA. Adding an asynchronous-commit replica in the secondary region satisfies the disaster recovery requirement with an RPO of under 1010 seconds without impacting primary transaction throughput. Utilizing Premium SSD v2 or Ultra Disk volumes for the transaction logs ensures sub-millisecond write latency.

Step-by-Step Solution

1
Analyze hosting requirements.
SQL Server on Azure Virtual Machines (IaaS) must be selected because the application relies on third-party system agents that require direct access to the underlying operating system. Fully managed options like Azure SQL Database or Azure SQL Managed Instance (PaaS) must be ruled out.
Direct OS access is not available in PaaS relational database offerings in Azure.
2
Evaluate intra-region High Availability (HA) requirements.
Configure a SQL Server Always On availability group with synchronous-commit replicas deployed across Availability Zones in the primary region.
This guarantees zero data loss (RPO=0RPO = 0), provides automatic failover, and achieves a high SLA (99.99%99.99\%) by utilizing multiple availability zones.
3
Evaluate inter-region Disaster Recovery (DR) requirements.
Add an asynchronous-commit replica in the secondary Azure region to the availability group.
Asynchronous-commit replication is designed for cross-region disaster recovery, minimizing the performance impact on the primary database while keeping the replication lag (RPO) minimal, typically well under 1010 seconds.
4
Determine the appropriate storage tier for the database transaction logs.
Select Premium SSD v2 or Ultra Disk volumes for the transaction log files.
The transaction logs require sub-millisecond write latency under high-write workloads. Standard HDD or Standard SSD volumes cannot meet these performance demands.

Key Concept

Designing High Availability and Disaster Recovery for SQL Server on Azure VMs using Always On Availability Groups and high-performance storage.
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic