Always On Availability Groups Requirement

Question

I want to ask Mssql Always On Availability Groups requirement for geo-redudancy.

Answer 1

Hi David,

For geo‑redundancy with SQL Server Always On Availability Groups, the requirements depend on whether you’re planning synchronous commit across regions or just asynchronous disaster recovery. At a minimum, you need Windows Server Failover Clustering configured across sites, Active Directory replication between domains if they’re separate, and a reliable low‑latency network if you expect synchronous commit. If you’re only targeting DR, asynchronous commit replicas in the secondary region are supported, but you’ll need to handle automatic failover manually since cross‑region synchronous quorum is rarely practical. Storage isn’t shared, so each site must maintain its own copies, and you’ll need to ensure DNS or listener redirection is properly configured for client failover.

Can you clarify whether your goal is active‑active high availability across regions, or simply a disaster recovery replica in a secondary site? That distinction changes the requirements significantly.Domic V.

Answer 2

For SQL Server Always On Availability Groups used for geo-redundancy, the key requirements and design considerations from the provided context are:

1. SQL Server instances and WSFC (for traditional AGs)
   • Each SQL Server instance that participates in an Always On availability group must have the Always On Availability Groups feature enabled.
   • For Windows-based availability groups (not read-scale only), each participating SQL Server instance must run on a node of a Windows Server Failover Cluster (WSFC).
   • Each availability replica in a given availability group must reside on a different node of the same WSFC cluster.
2. Enabling Always On and endpoints
   • Always On Availability Groups must be explicitly enabled on every SQL Server instance that will host a replica. This is done via SQL Server Configuration Manager (SQL Server service properties → Always On Availability Groups tab).
   • Each server instance requires a database mirroring endpoint for AG communication. If none exists, create one using CREATE ENDPOINT ... FOR DATABASE_MIRRORING or via the New Availability Group Wizard.
3. T‑SQL requirements for creating and managing AGs
   • Use CREATE AVAILABILITY GROUP on the instance that will host the initial primary replica. This instance must be on a WSFC node.
   • Use ALTER AVAILABILITY GROUP to manage the group and perform failovers.
4. Geo-redundancy / geo-read-scale design
   • For geographically separated deployments, use distributed availability groups to offload read workloads and achieve geographic read-scale. A single distributed availability group can have up to 17 readable secondary replicas, and multiple distributed AGs can be daisy-chained for additional scale.
   • A typical HA + DR design for geo-redundancy uses:
     • Two replicas in the primary data center with synchronous commit and vote privilege (for local high availability).
     • One or more replicas in a disaster recovery data center in another geo region with asynchronous commit and no vote privilege (for disaster recovery without impacting quorum).
   • The number of synchronous vs. asynchronous replicas and their placement across data centers should be chosen based on HA and DR requirements.
5. Underlying technology and behavior
   • Always On availability groups replicate the transaction log from the primary replica to secondary replicas. Secondaries are transactionally consistent; uncommitted transactions are not visible.
   • For Azure SQL offerings (Azure SQL Database and Azure SQL Managed Instance), high availability and geo-replication are implemented using technology similar to Always On availability groups, with multiple replicas and automatic failover managed by the platform.
6. Hardware and cluster validation
   • Nodes in the WSFC do not need identical hardware models, but they must run supported Windows and SQL Server versions and pass cluster validation. Hardware must meet performance and reliability requirements appropriate for the workload.

In summary, for geo-redundant Always On Availability Groups on Windows:

• Use a WSFC-based AG with at least one synchronous replica in the primary site and one asynchronous replica in a remote site.
• Ensure Always On is enabled on all instances, database mirroring endpoints exist, and the cluster passes validation.
• For large-scale geographic read workloads, consider distributed availability groups.

---

References:

• Getting Started with Always On Availability Groups
• Use read-scale with Always On availability groups
• Troubleshoot Always On Availability Groups Configuration (SQL Server)
• High Availability using SQL Server Always On Availability Groups - BizTalk Server
• High Availability and Disaster Recovery for Master Data Services
• Transact-SQL statements for Always On availability groups
• Active geo-replication
• Availability through local and zone redundancy - Azure SQL Managed Instance
• we have a 2-node SQL Server Failover Cluster Instance (FCI) on Dell R940 power edge model and we want to add new 3rd node for Always On / DR, but windows and SQL version is the same as existing servers but new hardware will be different - Microsoft Q&A