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

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

Always On Availability Groups (AG) is SQL Server’s premier high-availability and disaster recovery solution, enabling automatic failover with near-zero data loss and readable secondary replicas that offload reporting workloads from the primary. On Windows Server 2025, the underpinning technology — Windows Server Failover Clustering (WSFC) — benefits from improved cluster health policies, enhanced direct migration for Hyper-V virtual machines, and better integration with Storage Spaces Direct. SQL Server 2022’s Always On also introduces cross-subnet multi-subnet listener improvements and contained availability groups that simplify database portability. This guide walks you through building a two-node Always On Availability Group from scratch: installing clustering prerequisites, creating the WSFC cluster, enabling Always On in SQL Server, configuring replicas, creating the AG with PowerShell and T-SQL, setting up a listener, and validating failover behavior.

Prerequisites

• Two or more Windows Server 2025 servers (physical or virtual) joined to the same Active Directory domain.
• SQL Server 2022 Enterprise or Developer edition installed identically on each node (same instance name, same collation, same features).
• All nodes must be able to communicate on port 5022 (database mirroring endpoint) and port 1433 (SQL client connections).
• A domain account or gMSA for SQL Server services — the same account on all nodes simplifies endpoint permissions.
• Shared witness resource — a File Share Witness on a third server, or a Cloud Witness using Azure Blob Storage.
• The SqlServer and FailoverClusters PowerShell modules available on all nodes.

Step 1 — Install Failover Clustering Features

Run the following on all nodes that will participate in the cluster:

# Install Failover Clustering and management tools on each node
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -Restart:$false

# Install RSAT for Failover Clustering (admin tools on management station)
Install-WindowsFeature -Name RSAT-Clustering, RSAT-Clustering-Mgmt, RSAT-Clustering-PowerShell

# Verify the feature is installed
Get-WindowsFeature -Name Failover-Clustering | Select-Object Name, Installed, InstallState

Step 2 — Validate Cluster Configuration

Always run the cluster validation wizard before creating the cluster. It identifies hardware, networking, or Active Directory issues that will cause cluster creation to fail.

# Run cluster validation (produces a detailed HTML report)
$nodes = @("SQL2025-N1", "SQL2025-N2")
Test-Cluster -Node $nodes -Include "Storage", "Network", "System Configuration" `
    -ReportName "C:ClusterValidationPreBuildValidation"

# View the report path
Write-Host "Review the validation report before proceeding"
Invoke-Item "C:ClusterValidationPreBuildValidation.htm"

Resolve any errors (not warnings) before continuing. Common issues include missing network redundancy and mismatched firmware versions.

Step 3 — Create the Windows Server Failover Cluster

# Create the cluster — NoStorage because SQL AG uses its own log shipping, not shared disks
New-Cluster -Name "SQLCLUSTER01" `
    -Node "SQL2025-N1", "SQL2025-N2" `
    -StaticAddress "192.168.10.50" `
    -NoStorage

# Verify cluster is online
Get-Cluster -Name "SQLCLUSTER01" | Select-Object Name, SharedVolumesRoot
Get-ClusterNode -Cluster "SQLCLUSTER01" | Select-Object Name, State

Step 4 — Configure Quorum

For a two-node cluster a witness is mandatory — without it losing one node takes the cluster offline. Use a File Share Witness or Cloud Witness:

# Option A: File Share Witness (on a third server — \fileserverSQLCLUSTER01-Witness)
Set-ClusterQuorum -Cluster "SQLCLUSTER01" `
    -FileShareWitness "\WIT2025SQLCLUSTER01-Witness"

# Option B: Cloud Witness (Azure Storage Account)
Set-ClusterQuorum -Cluster "SQLCLUSTER01" `
    -CloudWitness `
    -AccountName "sqlag2025witness" `
    -AccountKey "base64keystringfromazureportal=="

# Verify quorum configuration
Get-ClusterQuorum -Cluster "SQLCLUSTER01"

Step 5 — Enable Always On Availability Groups in SQL Server

Always On must be enabled on each SQL Server instance via SQL Server Configuration Manager or PowerShell:

# Import the SQL Server module
Import-Module SqlServer

# Enable Always On on the primary node
Enable-SqlAlwaysOn -ServerInstance "SQL2025-N1" -Force
# Enable on the secondary node
Enable-SqlAlwaysOn -ServerInstance "SQL2025-N2" -Force

# Verify Always On is enabled
Invoke-Sqlcmd -ServerInstance "SQL2025-N1" `
    -Query "SELECT is_hadr_enabled FROM sys.configurations WHERE name = 'hadr enabled'" `
    -TrustServerCertificate

SQL Server must be restarted after enabling Always On. The -Force parameter does this automatically.

Step 6 — Create the Database Mirroring Endpoint

Each replica communicates through a mirroring endpoint. Create it on all nodes:

-- Run on SQL2025-N1 (primary)
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO

-- Grant CONNECT permission to the SQL Server service account
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CORPsvc-sqlengine];
GO

-- Run identically on SQL2025-N2 (secondary)
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CORPsvc-sqlengine];
GO

Add a firewall rule for the endpoint port on all nodes:

New-NetFirewallRule -DisplayName "SQL AG Hadr Endpoint" `
    -Direction Inbound -Protocol TCP -LocalPort 5022 `
    -Action Allow -Profile Domain

Step 7 — Prepare the Database for the Availability Group

The database must be in Full recovery model and have at least one full backup taken before joining an AG:

-- On SQL2025-N1 (primary)
USE [master];
GO

-- Set recovery model to Full
ALTER DATABASE [SalesDB] SET RECOVERY FULL;
GO

-- Take a full backup
BACKUP DATABASE [SalesDB]
    TO DISK = 'G:SQLBackupSalesDB_FullForAG.bak'
    WITH FORMAT, INIT, NAME = 'SalesDB-Full for AG', COMPRESSION;
GO

-- Take a transaction log backup
BACKUP LOG [SalesDB]
    TO DISK = 'G:SQLBackupSalesDB_LogForAG.trn'
    WITH FORMAT, INIT, NAME = 'SalesDB-Log for AG';
GO

Restore the backups on SQL2025-N2 with NORECOVERY to prepare it as a secondary:

-- On SQL2025-N2 (secondary) — restore with NORECOVERY
RESTORE DATABASE [SalesDB]
    FROM DISK = '\SQL2025-N1SQLBackupSalesDB_FullForAG.bak'
    WITH NORECOVERY, REPLACE,
    MOVE 'SalesDB' TO 'D:SQLDataSalesDB.mdf',
    MOVE 'SalesDB_log' TO 'E:SQLLogSalesDB_log.ldf';
GO

RESTORE LOG [SalesDB]
    FROM DISK = '\SQL2025-N1SQLBackupSalesDB_LogForAG.trn'
    WITH NORECOVERY;
GO

Step 8 — Create the Availability Group with PowerShell

Import-Module SqlServer

# Define the primary replica
$primaryReplica = New-SqlAvailabilityReplica `
    -Name "SQL2025-N1" `
    -EndpointURL "TCP://SQL2025-N1.corp.local:5022" `
    -AvailabilityMode SynchronousCommit `
    -FailoverMode Automatic `
    -ConnectionModeInSecondaryRole AllowAllConnections `
    -AsTemplate -Version 16

# Define the secondary replica
$secondaryReplica = New-SqlAvailabilityReplica `
    -Name "SQL2025-N2" `
    -EndpointURL "TCP://SQL2025-N2.corp.local:5022" `
    -AvailabilityMode SynchronousCommit `
    -FailoverMode Automatic `
    -ConnectionModeInSecondaryRole AllowReadIntentConnectionsOnly `
    -AsTemplate -Version 16

# Create the Availability Group
New-SqlAvailabilityGroup `
    -InputObject "SQL2025-N1" `
    -Name "AG_Sales" `
    -AvailabilityReplica @($primaryReplica, $secondaryReplica) `
    -Database "SalesDB" `
    -BasicAvailabilityGroup:$false `
    -DatabaseHealthTrigger:$true

# Join the secondary to the AG
Join-SqlAvailabilityGroup -Path "SQLSERVER:SQLSQL2025-N2DEFAULT" -Name "AG_Sales"

# Add the database to the secondary
Add-SqlAvailabilityDatabase -Path "SQLSERVER:SQLSQL2025-N2DEFAULTAvailabilityGroupsAG_Sales" `
    -Database "SalesDB"

Step 9 — Create an Availability Group Listener

The AG listener is a virtual network name (VNN) that clients connect to, regardless of which replica is currently primary:

# Create listener with a static IP
Add-SqlAvailabilityGroupListenerStaticIP `
    -Path "SQLSERVER:SQLSQL2025-N1DEFAULTAvailabilityGroupsAG_Sales" `
    -ListenerName "AG_Sales_Listener" `
    -Port 1433 `
    -StaticIP @("192.168.10.55/255.255.255.0")

-- Verify listener from T-SQL
SELECT dns_name, port, ip_configuration_string_from_cluster
FROM sys.availability_group_listeners
WHERE group_id = (SELECT group_id FROM sys.availability_groups WHERE name = 'AG_Sales');

Step 10 — Test Failover

# Check current primary
Get-SqlAvailabilityGroup -ServerInstance "SQL2025-N1" -Name "AG_Sales" |
    Select-Object -ExpandProperty AvailabilityReplicas |
    Select-Object Name, Role, AvailabilityMode, FailoverMode

# Perform a planned manual failover to the secondary
Switch-SqlAvailabilityGroup `
    -Path "SQLSERVER:SQLSQL2025-N2DEFAULTAvailabilityGroupsAG_Sales"

# Confirm the new primary
Get-SqlAvailabilityGroup -ServerInstance "SQL2025-N2" -Name "AG_Sales" |
    Select-Object -ExpandProperty AvailabilityReplicas |
    Select-Object Name, Role

Monitor the AG health dashboard in SSMS: right-click the AG in Object Explorer and choose Show Dashboard. Synchronization state should read Synchronized and failover readiness No data loss.

Conclusion

A properly configured Always On Availability Group on Windows Server 2025 with SQL Server 2022 provides automatic failover within seconds, protecting your SalesDB from instance-level failures with synchronous commit and near-zero RPO. The secondary replica, configured with AllowReadIntentConnectionsOnly, can serve read-scale workloads by pointing application connection strings to the listener with ApplicationIntent=ReadOnly. You can also redirect secondary replica backups to reduce I/O load on the primary — configure this via the AG backup preference in SSMS or via ALTER AVAILABILITY GROUP ... SET (BACKUP_PRIORITY = 60) on the secondary replica. With the cluster validated, the endpoint secured with AES encryption, and quorum properly configured, your AG environment is ready for production traffic.