Examkin

Question

Difficulty: MediumDesign Relational Database HA and DR

A company plans to migrate an on-premises database to Azure. The database workload requires SQL Server Agent jobs and cross-database queries. The disaster recovery (DR) solution must support automatic failover to a secondary Azure region with a recovery time objective (RTO) of less than 1 hour. After a failover, the application must connect to the database in the secondary region using the same connection string without requiring manual configuration changes. Which database solution and disaster recovery feature should you recommend?

  1. Azure SQL Managed Instance configured with a failover groupAnswer
  2. B
    Azure SQL Database (single database) configured with active geo-replication
  3. C
    Azure SQL Managed Instance configured with active geo-replication
  4. D
    SQL Server on Azure Virtual Machines using Standard HDD disks for transaction logs and configured with Always On availability groups

Answer

Azure SQL Managed Instance configured with a failover group
Azure SQL Managed Instance provides near-complete compatibility with the SQL Server database engine, including support for SQL Server Agent and cross-database queries. When configured with a failover group, it provides automatic failover capabilities and a single read-write listener endpoint. This listener endpoint ensures the application can connect to the primary instance in either region using the same connection string without manual configuration updates after a failover.

Step-by-Step Solution

1
Identify compatibility requirements
The application requires SQL Server Agent and cross-database queries, which are instance-level features not supported by Azure SQL Database (single database). This limits the choice to Azure SQL Managed Instance or SQL Server on Azure VMs.
Ensuring the selected database engine supports all legacy features is the first step in design.
2
Evaluate disaster recovery and endpoint routing requirements
The solution requires automatic failover and a single connection string (listener endpoint) across regions. Azure SQL Managed Instance failover groups provide a read-write listener that automatically updates its DNS record during a failover to point to the new primary instance.
This satisfies the RTO and connection string requirements without manual configuration changes.
3
Verify storage and performance constraints
SQL Server on Azure VMs using Standard HDD for transaction logs is rejected due to performance limitations, making Azure SQL Managed Instance with a failover group the optimal choice.
Transaction log drives require high IOPS/throughput typical of Premium SSD or Ultra Disk, not Standard HDD.

Key Concept

Selecting the correct Azure SQL deployment option based on legacy feature requirements and configuring failover groups for automatic multi-region DR with a single listener endpoint.
Estimated Time:1m 30s
Rate this question

Topics

Design Relational Database HA and DR

More questions from this topic