70-465 Question #31: Real Exam Question with Answer & Explanation

Question

Your company has offices in Seattle and Montreal. The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. Server1 is located in the Seattle office. Server2 is located in the Montreal office. The latency of the WAN link between the Montreal office and the Seattle office is more than 200 ms. You plan to implement an AlwaysOn availability group on both servers. You need to recommend a failover type for the availability group. What should you recommend?

Options

• ASynchronous manual failover
• BSynchronous automatic failover
• CAsynchronous automatic failover
• DAsynchronous manual failover

Explanation

To implement an AlwaysOn availability group across two offices separated by a high-latency WAN link (over 200 ms), the recommended failover type must prioritize primary replica performance while acknowledging the network constraints.

Common mistakes.

• A. Synchronous-commit mode would severely degrade primary replica performance due to the requirement that transactions are committed on both the primary and secondary before the primary acknowledges the commit to the client, which is impractical over a 200 ms latency link.
• B. Synchronous-commit mode is unsuitable for high-latency links as it significantly impacts performance, and while automatic failover typically requires synchronous-commit, the high latency would lead to poor performance and potential issues with reliable failover detection.
• C. While asynchronous-commit mode is appropriate for high latency, automatic failover with asynchronous-commit is generally not recommended because it accepts potential data loss and high network latency can lead to unreliable health monitoring, potentially causing spurious failovers or split-brain scenarios.

Concept tested. SQL Server AlwaysOn Availability Group commit and failover modes, latency considerations

Reference. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-alwayson-availability-groups?view=sql-server-ver16

No community discussion yet for this question.