SQL Server Always On Availability Group (2022)
This guide explains 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
2. Enable Always On Availability Groups
3. Prepare Databases
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)
8. Add Database to Availability Group (Primary Node)
9. Verify
0. Environment
| Item | Hostname | Public IP | SQL Replication IP |
|---|---|---|---|
| DC / Witness Server | DC01 | 192.168.2.20 | NA |
| DB Node 1 | NTICBPSQLSGV01 | 192.168.2.21 | 10.10.10.11 |
| DB Node 2 | NTICBPSQLSGV02 | 192.168.2.22 | 10.10.10.12 |
| DB Node 3 | NTICBPSQLSGV03 | 192.168.2.23 | 10.10.10.13 |
| DB Node 4 | NTICBPSQLSGV04 | 192.168.2.24 | 10.10.10.14 |
| WSFC Cluster Name | NTICBPSQLSGC05 | 192.168.2.25 | NA |
| AG Listener Name | NTICBPSQLSGL05 | 192.168.2.26 | NA |
| Availability Group Name | NTICBPSQLSGA05 | NA | NA |
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)
2. Enable Always On Availability Groups
Run on each SQL Server node:
3. Prepare Databases (On PRIMARY )
Set recovery model to FULL
CREATE DATABASE TEST;
ALTER DATABASE TEST SET RECOVERY FULL;
GO
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)
Run on NTICBPSQLSGV01
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, NTICBPSQLSGV04
USE master;
GO
ALTER AVAILABILITY GROUP [NTICBPSQLSGA05] JOIN;
GO
7. Grant CREATE ANY DATABASE
Run on all nodes – NTICBPSQLSGV01, NTICBPSQLSGV02, NTICBPSQLSGV03, NTICBPSQLSGV04
USE master;
GO
ALTER AVAILABILITY GROUP [NTICBPSQLSGA05]
GRANT CREATE ANY DATABASE;
GO
8. Add Database to Availability Group
Run on Primary Node (NTICBPSQLSGV01)
USE master;
GO
ALTER AVAILABILITY GROUP [NTICBPSQLSGA05]
ADD DATABASE [TEST];
GO
9. Verify
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/
