Setup (4-Node) SQL Server Always On Availability Group (2022)

How to configure a 4-node SQL Server 2022 Always On Availability Group using an existing Windows Server Failover Cluster (WSFC).

Table of Contents

1. Prerequisites (All nodes)
2. Enable Always On Availability Groups (All Nodes)
3. Set Recovery Model to Full & Take Full Backup of Database) – (Primary Node)
4. Create Availability Group (Primary Node)
5. Create HADR Endpoints (All Nodes)
6. Join Secondary Replicas (All secondary)
7. Grant CREATE ANY DATABASE (All Nodes) – if only (SEEDING_MODE = AUTOMATIC)
8. Add Database to Availability Group (Primary Node)
9. Verify
10. Health Monitoring

0. Environment

1. Prerequisites

• Windows Server Failover Cluster (WSFC) already configured (4-node cluster)
• SQL Server 2022 Enterprise Edition installed on all nodes
• Databases must use FULL recovery model
• Full backup taken before adding to AG
• All nodes in the same Active Directory domain
• Proper DNS resolution between nodes
• Dedicated domain service account MSAPICBSGSQL for SQL Server (same on all nodes)
• Same Directory Path on ALL SQL Nodes (Mandatory for AG)

Windows Server Failover Clustering (WSFC) is a Windows feature that groups multiple servers (nodes) into a cluster. It provides:

• A shared cluster database (stored in the quorum) that holds metadata about all cluster resources
• A heartbeat mechanism between nodes to detect failures
• Resource ownership — any clustered resource (like an AG) is “owned” by exactly one node at a time

2. Enable Always On Availability Groups

Each SQL Server instance on each node must have Always On Availability Groups enabled. This is done via SQL Server Configuration Manager.

It tells the SQL Server engine to register itself with the local WSFC service and activate the HADR (High Availability Disaster Recovery) subsystem.

Run on each SQL Server node:

3. Set Recovery Model to Full & Take Full Backup of Database) – (On PRIMARY )

• Each availability database must use the full recovery model , In Full recovery model transaction log records are never automatically truncated.
• Backup database at least once, so the LSN chain is established.
• Restored on secondary replicas with WITH NORECOVERY (leaving them in a restoring state, ready to receive log stream data)

3.1 Set recovery model to FULL

CREATE DATABASE TEST;

ALTER DATABASE TEST SET RECOVERY FULL;
GO

3.2 Take a full backup

BACKUP DATABASE [TEST] TO DISK = 'K:\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\TEST.bak'; 
GO

4. Create Availability Group (Primary Node)

When a DBA runs CREATE AVAILABILITY GROUP, several things happen simultaneously inside SQL Server and WSFC:

Inside SQL Server:

• A new AG object is created in system metadata (sys.availability_groups)
• Replica endpoints (database mirroring endpoints, typically on port 5022) are configured to handle the log stream communication between primary and secondaries.
• The primary replica begins tracking the synchronization state of each database for each secondary.

Inside WSFC:

• SQL Server calls Windows Cluster APIs to register the AG as a cluster resource
• This registration stores the AG name, its owning node, and its health-check configuration into the WSFC cluster database
• From WSFC’s perspective, the AG is now just another “resource” it needs to monitor and potentially fail over — it doesn’t understand SQL internals, it just knows “this resource is healthy or it isn’t”

The key insight here is the separation of concerns: SQL Server owns the data and synchronization logic; WSFC owns the failover orchestration. They communicate through a defined interface.

Two distinct resources get registered with WSFC:

AG Resource:

• Represents the Availability Group itself
• WSFC tracks which node currently owns it (i.e., which node is primary)
• Health is determined by querying SQL Server via the resource DLL

Listener Resource (Virtual Network Name + IP):

• A Virtual Network Name (VNN) like AG-Listener that clients use to connect
• A Virtual IP address that floats between nodes — it’s always active on whichever node is currently primary.
• WSFC manages bringing this IP online/offline during failovers.

These two resources are grouped together in a cluster resource group, meaning WSFC always moves them together. You’d never want the listener pointing to Node A while the AG primary is on Node B.

Run on NTICBPSQLSGV01 (Primary only)

USE master;
GO

CREATE AVAILABILITY GROUP NTICBPSQLSGA05
	WITH (
		AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
		FAILURE_CONDITION_LEVEL = 3,
		HEALTH_CHECK_TIMEOUT = 30000
		)
FOR
REPLICA ON
    'NTICBPSQLSGV01' WITH (
        ENDPOINT_URL = 'TCP://10.10.10.11:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL),
        SESSION_TIMEOUT = 10,
        SEEDING_MODE = AUTOMATIC
    ),
    'NTICBPSQLSGV02' WITH (
        ENDPOINT_URL = 'TCP://10.10.10.12:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL),
        SESSION_TIMEOUT = 10,
        SEEDING_MODE = AUTOMATIC
    ),
    'NTICBPSQLSGV03' WITH (
        ENDPOINT_URL = 'TCP://10.10.10.13:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL),
        SESSION_TIMEOUT = 10,
        SEEDING_MODE = AUTOMATIC
    ),
    'NTICBPSQLSGV04' WITH (
        ENDPOINT_URL = 'TCP://10.10.10.14:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 30,
        PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL),
        SESSION_TIMEOUT = 10,
        SEEDING_MODE = AUTOMATIC
    )
	
LISTENER 'NTICBPSQLSGL05' ( WITH IP ( ('192.168.2.26','255.255.255.0')), PORT = 1433);
GO

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'AlwaysOn_health')
BEGIN
    ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE = ON);
END

IF NOT EXISTS (SELECT * FROM sys.dm_xe_sessions WHERE name = 'AlwaysOn_health')
BEGIN
    ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE = START;
END
GO

5. Create HADR Endpoints (All Nodes)

On NTICBPSQLSGV01:

USE [master]
GO
CREATE ENDPOINT [Hadr_endpoint]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.11))
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE,ENCRYPTION = DISABLED)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

GO

USE [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQL\MSAPICBSGSQL$];
GO

ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint] TO [sa];
GO

Repeat on other nodes using their respective IP addresses.

On NTICBPSQLSGV02:

USE [master] 
GO
CREATE ENDPOINT [Hadr_endpoint]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.12))
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE,ENCRYPTION = DISABLED)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

GO

USE [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQL\MSAPICBSGSQL$];
GO

ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint] TO [sa];
GO

On NTICBPSQLSGV03:

USE [master] 
GO
CREATE ENDPOINT [Hadr_endpoint]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.13))
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE,ENCRYPTION = DISABLED)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END

GO

USE [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQL\MSAPICBSGSQL$];
GO

ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint] TO [sa];
GO

On NTICBPSQLSGV04:

USE [master] 
GO
CREATE ENDPOINT [Hadr_endpoint]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.10.10.14))
	FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE,ENCRYPTION = DISABLED)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
	ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO

USE [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [SQL\MSAPICBSGSQL$];
GO

ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint] TO [sa];
GO

6. Join Secondary Replicas

Run on NTICBPSQLSGV02, NTICBPSQLSGV03 and NTICBPSQLSGV04

USE master;
GO
ALTER AVAILABILITY GROUP [NTICBPSQLSGA05] JOIN;
GO

On NTICBPSQLSGV02

 

On NTICBPSQLSGV03 

 

On NTICBPSQLSGV04

7. Grant CREATE ANY DATABASE (Only if (SEEDING_MODE = AUTOMATIC), else not required, skip this step)

• This permission is crucial for automatic seeding (introduced in SQL Server 2016).
• With automatic seeding, SQL Server creates the database on secondary replicas by streaming data from the primary.
• To do this, the AG itself must have the right to create databases on those replicas — hence the grant required.

Run on all nodes – NTICBPSQLSGV01, NTICBPSQLSGV02, NTICBPSQLSGV03 and  NTICBPSQLSGV04

USE master;
GO
ALTER AVAILABILITY GROUP [NTICBPSQLSGA05]
GRANT CREATE ANY DATABASE;
GO

On NTICBPSQLSGV01:



On NTICBPSQLSGV02:
  

On NTICBPSQLSGV03:

 

On NTICBPSQLSGV04:

8. Add Database to Availability Group

Run on Primary Node (NTICBPSQLSGV01) 

USE master;
GO
ALTER AVAILABILITY GROUP [NTICBPSQLSGA05]
ADD DATABASE [TEST];
GO

9. Verify

10. Health Monitoring

This is where WSFC and SQL Server interact continuously at runtime.

hadrres.dll — The Resource DLL: This is a critical component. WSFC doesn’t know how to talk to SQL Server natively. Instead, SQL Server ships a DLL (hadrres.dll) that WSFC loads into the cluster service process. This DLL acts as an interpreter — it knows how to connect to SQL Server, run health queries, and translate the results into signals WSFC understands (healthy / degraded / failed).

The health check loop works like this:

1. WSFC’s cluster service calls into hadrres.dll on a regular interval
2. hadrres.dll opens a connection to the local SQL Server instance using the NT AUTHORITY\SYSTEM is the Local System account, Windows Server Failover Clustering service (ClusSvc.exe) runs under this identity by default. This means when WSFC (via hadrres.dll) connects to SQL Server, it authenticates as NT AUTHORITY\SYSTEM using Windows Authentication, NT AUTHORITY\SYSTEM login has these privileges by default CONNECT SQL, VIEW SERVER STATE, ALTER ANY AVAILABILITY GROUP.
3. It executes sp_server_diagnostics which returns a structured XML output covering:
   • System health — CPU, memory, I/O pressure
   • Resource health — memory availability
   • Query processing — scheduler health, worker threads
   • IO subsystem — disk response times
   • Events — recent critical errors
4. The DLL evaluates the results against thresholds and reports back to WSFC

If the health check fails enough times (based on configured HealthCheckTimeout and FailureConditionLevel), WSFC declares the resource failed and initiates failover.

11. Tips

• Transaction log management becomes mandatory — Must schedule regular transaction log backups (on the primary or allowed secondaries) to prevent the log from growing indefinitely and filling the disk.
• No data loss in sync mode — this is only possible because the full log chain is always available.
• Backups still needed — Availability Groups are not a replacement for backups. AGs protect against instance/hardware failure, but not user errors, corruption, or accidental deletes — continue taking full + log backups.

12. Conclusion

• SQL Server owns data consistency, synchronization, and log streaming
• WSFC owns node health, resource ownership, and failover orchestration (which means the automated configuration, coordination, and management of complex computer systems)
• NT AUTHORITY\SYSTEM + scoped grants is the narrow, secure bridge between the two
• The listener is the stable endpoint that hides all the complexity from applications

Each piece does exactly one job, and they communicate through well-defined, minimal interfaces. That’s what makes Always On both robust and secure.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/