Hotspot Question You plan to deploy three instances of SQL Server on Azure Virtual Machines that will each contain 20 databases. You need to recommend a solution that meets the following…

Question

Exhibit

Answer Area

• Type of availability group:Always On
  Always OnContainedDistributed
• Minimum Microsoft SQL Server version:2017
  201720192022

Explanation

This hotspot question tests knowledge of SQL Server high availability on Azure VMs and centralized management of logins, users, permissions, and SQL Agent jobs across multiple instances.

Approach. For high availability across three SQL Server instances on Azure VMs, you should recommend an Always On Availability Group (AG), which provides automatic failover and redundancy across instances. To minimize administrative effort for managing logins, users, permissions, and SQL Server Agent jobs across all instances, you should use a Distributed Management Objects (DMO) approach or, more specifically, leverage the 'Contained Databases' feature for users/permissions and use 'Always On AG' with synchronized logins via scripts - however, the key recommendation here is to use an Always On Availability Group listener for HA, and for centralized job/login management, use a Central Management Server (CMS) or configure logins at the AG replica level using scripts. The most exam-correct answer is: Always On Availability Groups for high availability, and Windows Authentication with Active Directory (AD) accounts to minimize per-instance user/login management, plus using SQL Server Agent jobs replicated via the AG or managed centrally. Specifically: HA = Always On Availability Groups; Minimize admin effort = Contained database users (so users travel with the database in AG failovers) for permissions, and use a shared/centralized approach like CMS for Agent jobs.

Concept tested. The question tests understanding of SQL Server Always On Availability Groups for high availability on Azure VMs, and the use of Contained Databases or Windows/Azure AD authentication to minimize the overhead of managing logins and permissions across multiple SQL Server instances. It also tests awareness of how SQL Server Agent jobs must be managed independently per instance (not replicated by AG), which informs the need for centralized job management strategies.

Reference. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview

No community discussion yet for this question.