How to Set Up SQL Server Always On Availability Groups on Windows Server 2022

SQL Server Always On Availability Groups (AG) provide high availability and disaster recovery by maintaining one or more synchronous or asynchronous secondary replicas of a group of databases. Reads, backups, and even DBCC checks can be offloaded to secondaries. This guide walks through the complete process of setting up an Availability Group on Windows Server 2022 with SQL Server Enterprise edition, from building the underlying Windows Server Failover Cluster to testing a failover.

Prerequisites

Always On Availability Groups have strict prerequisites that must all be satisfied before configuration begins.

Edition: SQL Server Enterprise edition is required for up to eight secondary replicas and all AG features. SQL Server Standard edition supports Basic Availability Groups with one secondary replica and significant limitations. Developer edition includes all Enterprise features for testing.

Windows Server Failover Cluster (WSFC): Every node participating in an AG must be a member of the same WSFC. The WSFC provides the health monitoring and failover coordination infrastructure that AGs depend on.

Domain membership: All cluster nodes must be joined to the same Active Directory domain. WSFC requires Kerberos authentication between nodes, which requires domain membership. Workgroup clusters (introduced in Server 2016) are technically possible but require additional configuration and are not covered here.

Identical SQL Server versions and editions: All replicas must run the same SQL Server edition. The version on all replicas should be the same or the secondary should be at a higher version than the primary during rolling upgrades only.

Database configuration: All databases in the AG must use the Full recovery model and have at least one full backup taken. Simple recovery model databases cannot participate in an AG.

Step 1: Creating the Windows Server Failover Cluster

Install the Failover Clustering feature on all nodes that will participate in the AG. Run the following on each node:

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

Before creating the cluster, run the validation wizard to confirm that all nodes meet the hardware and software requirements:

Test-Cluster -Node "SQL-NODE1", "SQL-NODE2", "SQL-NODE3" -Include "Storage Spaces Direct", "Inventory", "Network", "System Configuration"

Review the validation report. Warnings about shared storage are acceptable for an AG cluster (AGs do not use shared storage). Errors in the Network or System Configuration categories must be resolved before proceeding.

Create the cluster. For an AG cluster that does not use any clustered resources beyond what the AG needs, you do not need a shared disk:

New-Cluster -Name "WSFC-SQL" `
    -Node "SQL-NODE1", "SQL-NODE2", "SQL-NODE3" `
    -StaticAddress "10.0.1.50" `
    -NoStorage

After the cluster is created, configure the cluster quorum. For a two-node cluster, a file share witness or cloud witness is required to avoid split-brain scenarios. For three or more nodes, the default node majority quorum is usually sufficient.

# Configure a file share witness for a two-node cluster
Set-ClusterQuorum -Cluster "WSFC-SQL" -FileShareWitness "\fileserverWSFC-Witness"

# Configure Azure cloud witness (recommended for cloud-connected environments)
Set-ClusterQuorum -Cluster "WSFC-SQL" -CloudWitness `
    -AccountName "mystorageaccount" `
    -AccessKey "BASE64ACCESSKEY=="

Step 2: Enabling Always On on Each SQL Server Instance

Always On must be enabled on each SQL Server instance that will participate. This can be done via SQL Server Configuration Manager (right-click the SQL Server service, select Properties, go to the Always On Availability Groups tab) or via PowerShell using the SqlServer module:

# Import SQL Server PowerShell module
Import-Module SqlServer

# Enable Always On on each node (requires SQL Server service restart)
Enable-SqlAlwaysOn -ServerInstance "SQL-NODE1" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL-NODE2" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL-NODE3" -Force

The -Force flag automatically restarts the SQL Server service. After running this command on all nodes, verify that Always On is enabled:

-- Run on each instance
SELECT SERVERPROPERTY('IsHadrEnabled') AS AlwaysOnEnabled;

A return value of 1 confirms Always On is enabled.

Step 3: Preparing Databases for the Availability Group

All databases to be added to the AG must be in Full recovery model and must have a recent full backup. On the primary replica (SQL-NODE1):

-- Set the database to Full recovery model
ALTER DATABASE AppDatabase SET RECOVERY FULL;

-- Take a full backup to a shared network location accessible by all replicas
BACKUP DATABASE AppDatabase
TO DISK = '\fileserverSQLBackupsAppDatabase_AG.bak'
WITH INIT, COMPRESSION, STATS = 10;

-- Take a transaction log backup (required before joining secondary)
BACKUP LOG AppDatabase
TO DISK = '\fileserverSQLBackupsAppDatabase_AG.trn'
WITH INIT, STATS = 10;

Restore the database on each secondary in NORECOVERY mode. Do not bring the database online:

-- Run on SQL-NODE2 and SQL-NODE3
RESTORE DATABASE AppDatabase
FROM DISK = '\fileserverSQLBackupsAppDatabase_AG.bak'
WITH NORECOVERY, STATS = 10;

RESTORE LOG AppDatabase
FROM DISK = '\fileserverSQLBackupsAppDatabase_AG.trn'
WITH NORECOVERY;

Step 4: Creating the Availability Group

Create the AG on the primary replica using T-SQL. This example creates a three-replica AG with one synchronous commit secondary for high availability and one asynchronous commit secondary for disaster recovery:

-- Run on the primary replica (SQL-NODE1)
CREATE AVAILABILITY GROUP [AG_AppDatabase]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,
    DTC_SUPPORT = NONE,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000
)
FOR DATABASE [AppDatabase]
REPLICA ON 
    N'SQL-NODE1' WITH (
        ENDPOINT_URL = N'TCP://SQL-NODE1.domain.com:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        SEEDING_MODE = MANUAL,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
    ),
    N'SQL-NODE2' WITH (
        ENDPOINT_URL = N'TCP://SQL-NODE2.domain.com:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        SEEDING_MODE = MANUAL,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    ),
    N'SQL-NODE3' WITH (
        ENDPOINT_URL = N'TCP://SQL-NODE3.domain.com:5022',
        FAILOVER_MODE = MANUAL,
        AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
        SEEDING_MODE = MANUAL,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    );
GO

Before creating the AG, you need a mirroring endpoint on each instance. SQL Server may create this automatically, but if it does not, create it manually:

-- Run on each instance
CREATE ENDPOINT [Hadr_endpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES);

GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAINSQLServiceAccount];

Join each secondary to the AG:

-- Run on SQL-NODE2
ALTER AVAILABILITY GROUP [AG_AppDatabase] JOIN;
ALTER DATABASE AppDatabase SET HADR AVAILABILITY GROUP = [AG_AppDatabase];

-- Run on SQL-NODE3
ALTER AVAILABILITY GROUP [AG_AppDatabase] JOIN;
ALTER DATABASE AppDatabase SET HADR AVAILABILITY GROUP = [AG_AppDatabase];

Configuring the Availability Group Listener

The AG listener is a virtual network name and IP address that applications use to connect. The listener automatically routes connections to the current primary replica, so application connection strings do not need to change after a failover.

-- Run on the primary replica
ALTER AVAILABILITY GROUP [AG_AppDatabase]
ADD LISTENER 'AG-LISTENER' (
    WITH IP ((N'10.0.1.60', N'255.255.255.0')),
    PORT = 1433
);
GO

-- Verify the listener
SELECT dns_name, port, ip_configuration_string_from_cluster
FROM sys.availability_group_listeners;

After creating the listener, create a DNS record if it does not appear automatically in Active Directory DNS. The listener name must resolve from all client machines that will connect to SQL Server.

Automatic vs Manual Failover

Replicas configured with FAILOVER_MODE = AUTOMATIC and AVAILABILITY_MODE = SYNCHRONOUS_COMMIT will automatically fail over when WSFC health checks detect that the primary is unavailable and the secondary is synchronized. Automatic failover requires at least two synchronous replicas with automatic failover configured and the WSFC quorum intact.

To perform a planned manual failover (for maintenance, patching, or testing), use:

-- Run on the target secondary that will become the new primary
ALTER AVAILABILITY GROUP [AG_AppDatabase] FAILOVER;

-- Or use PowerShell
Switch-SqlAvailabilityGroup -Path "SQLSERVER:SqlSQL-NODE2DEFAULTAvailabilityGroupsAG_AppDatabase"

A forced failover (for disaster recovery when the primary is completely unavailable) may involve data loss and requires explicitly acknowledging that risk:

-- Forced failover with possible data loss
ALTER AVAILABILITY GROUP [AG_AppDatabase] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Readable Secondary Replicas

Secondary replicas configured with SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) can serve read-only queries. To direct applications to a readable secondary rather than the primary, set ApplicationIntent=ReadOnly in the connection string:

Server=AG-LISTENER,1433;Database=AppDatabase;Integrated Security=True;ApplicationIntent=ReadOnly;

Readable secondaries use row versioning to provide snapshot isolation, eliminating read/write conflicts with primary redo operations. Redo latency means reads on the secondary may see slightly older data than the primary.

Monitoring with the Always On Dashboard

In SSMS, right-click the availability group under Always On High Availability in Object Explorer and select Show Dashboard. The dashboard displays the synchronization state, estimated recovery time, log send queue size, redo queue size, and any policy health alerts for each replica and database.

Monitor AG health via T-SQL using the dynamic management views:

-- Check replica synchronization state
SELECT ag.name AS ag_name,
       ar.replica_server_name,
       ars.role_desc,
       ars.operational_state_desc,
       ars.connected_state_desc,
       ars.synchronization_health_desc,
       ars.last_connect_error_description
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

-- Check database synchronization state and lag
SELECT ag.name AS ag_name,
       dbs.database_name,
       dbs.synchronization_state_desc,
       dbs.synchronization_health_desc,
       dbs.log_send_queue_size,
       dbs.log_send_rate,
       dbs.redo_queue_size,
       dbs.redo_rate,
       dbs.last_commit_time
FROM sys.availability_groups ag
JOIN sys.dm_hadr_database_replica_states dbs ON ag.group_id = dbs.group_id;

With WSFC established, Always On enabled, an AG created with a listener, and monitoring in place, your SQL Server 2022 high availability environment on Windows Server 2022 is production-ready. Regularly test failover procedures, review AG health metrics, and keep all replicas at the same SQL Server patch level to maintain a reliable setup.

Links

Newsletter

Contact

How to Configure DNS over HTTPS on Windows Server 2022