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

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/