SQL Server Always On Availability Group (Windows, WSFC)

Builds an Always On Availability Group on Windows servers with SQL Server 2022 installed: Windows Server Failover Cluster (WSFC) and quorum witness (file share / cloud witness), enabling Always On, mirroring endpoints and permissions, an AG with automatic seeding, listener, synchronous/asynchronous replicas, readable secondary, failover test and monitoring. The result is an AG whose applications connect to the listener name and whose failover has been tested.

First install every node with SQL Server installation on Windows using the same version and paths. For day-to-day operation and failures of an existing AG (lag, forced failover, adding databases) see AG operations; for an AG with Pacemaker on Linux see Always On AG (Linux).

Topology: 2–9 Windows nodes (WSFC) + witness

Before you start

• SQL Server 2022 is installed on all nodes with the same version and CU, the same instance name (…) and the same data/log drive letters and paths (SQL Server 2022 installation on Windows in the Install menu). Edition: Enterprise (Standard supports only Basic AG: 2 replicas, 1 database).
• All nodes run the same Windows Server version, are in the same domain (…), patched and in the same subnet. Each node has a static IP; DNS records are correct.
• The account running the setup is a local administrator on all nodes and sysadmin in SQL Server. It is allowed to create the … and … computer objects in AD (or the objects have been pre-staged and the CNO granted Full Control).
• Two unused IPs are reserved: cluster …, listener … (no DNS record yet).
• The witness share … is on a server separate from the nodes; share and NTFS permissions: Full Control for …\…$ (can also be granted after the cluster is created).
• The Azure Storage account … (Standard, LRS/ZRS) is ready and the nodes can reach .blob.core.windows.net over 443. You have the access key.
• …… nodes (odd number): quorum majority is achieved without a witness; a witness is still recommended.…… nodes (even number): without a witness the cluster can stop when one node is lost. Choose a witness.…
• Ports open between the nodes: SQL …/TCP, endpoint …/TCP, cluster 3343/TCP-UDP, RPC 135/TCP + dynamic RPC, SMB 445/TCP.
• The SqlServer PowerShell module is installed on every node (for Enable-SqlAlwaysOn).
• The databases to add to the AG (………, ……) exist on node 1 and are user databases (not system databases); no database with the same name exists on the secondary nodes.
• For the service account … the SPN MSSQLSvc/<node>.…:… is registered for each node (endpoint and listener connections use Kerberos; also add MSSQLSvc/….…:… for the listener).
• The maintenance window is approved; there are current backups of the databases and snapshots of the nodes.

Phases of the guide

• Windows Server Failover Cluster — Install the Failover Clustering feature · Validate the cluster (Test-Cluster) · Create the cluster · Configure the quorum witness (file share) · Configure the quorum witness (cloud witness) · Check the cluster state
• Enable Always On in SQL Server — Enable Always On · Create logins for the service accounts · Create the mirroring endpoint · Open the SQL and endpoint ports in the firewall · Test access to the endpoints
• Availability Group — Switch the databases to FULL recovery and take a full backup · Create the AG (primary) · Join the secondary to the AG · Monitor automatic seeding · Create the listener · Configure read-only routing (readable secondary)
• Verification and failover test — Check AG health · Test the connection through the listener · Planned failover test: make … the primary · Fail back: make … the primary again · Check from the cluster view
• After installation and monitoring — Synchronize logins, jobs and other server objects · Define the backup strategy · Monitor the AG with DMV queries · Define Agent alerts for role changes