What Is an Always On Availability Group?

SQL Server Always On AG (Availability Group) is a database-level HA solution that maintains multiple Secondary replicas and supports automatic and manual failover.

Key features

Up to 8 Secondary replicas (as of SQL Server 2022)
Choice of synchronous or asynchronous commit mode
Offload read-only queries to Secondary replicas
Runs on top of Windows Server Failover Cluster (WSFC)

Prerequisites

Item	Requirement
SQL Server edition	Enterprise or Developer (for testing)
OS	Windows Server 2019/2022
Domain	Active Directory domain required
WSFC	All nodes must be members of the same cluster
Network	High-speed private network between nodes recommended

Environment

Server	IP	Role
SQL1	192.168.1.10	Primary
SQL2	192.168.1.11	Secondary (synchronous)
SQL3	192.168.1.12	Secondary (asynchronous, DR)
AG Listener	192.168.1.50	Application connection VIP

Step 1 — Configure Windows Server Failover Cluster

PowerShell (all nodes)

POWERSHELL

#
Install-WindowsFeature Failover-Clustering -IncludeManagementTools

#
Test-Cluster -Node SQL1, SQL2, SQL3

# (SQL1)
New-Cluster -Name "SQLCluster" -Node SQL1,SQL2,SQL3 -StaticAddress 192.168.1.30

Step 2 — Enable SQL Server Always On

SQL Server Configuration Manager → SQL Server Services → Properties → enable on the Always On Availability Groups tab

Or PowerShell:

POWERSHELL

Enable-SqlAlwaysOn -ServerInstance "SQL1" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL2" -Force
Enable-SqlAlwaysOn -ServerInstance "SQL3" -Force

A service restart is required.

Step 3 — Create the mirroring endpoint (on each node)

SQL

-- SQL1, SQL2, SQL3 run
CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES);

-- CONNECT
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\sql_service];

Step 4 — Prepare the database (Primary)

SQL

-- FULL required
ALTER DATABASE [SalesDB] SET RECOVERY FULL;

-- backup (Secondary )
BACKUP DATABASE [SalesDB]
  TO DISK = '\\fileserver\backup\SalesDB.bak'
  WITH FORMAT, INIT;

BACKUP LOG [SalesDB]
  TO DISK = '\\fileserver\backup\SalesDB_log.bak';

Step 5 — Restore the backup on Secondary replicas

SQL

-- SQL2, SQL3 run (NORECOVERY required)
RESTORE DATABASE [SalesDB]
  FROM DISK = '\\fileserver\backup\SalesDB.bak'
  WITH NORECOVERY, MOVE 'SalesDB' TO 'C:\Data\SalesDB.mdf',
       MOVE 'SalesDB_log' TO 'C:\Data\SalesDB_log.ldf';

RESTORE LOG [SalesDB]
  FROM DISK = '\\fileserver\backup\SalesDB_log.bak'
  WITH NORECOVERY;

Step 6 — Create the availability group (Primary)

SQL

CREATE AVAILABILITY GROUP [AG_Sales]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000
)
FOR DATABASE [SalesDB]
REPLICA ON
  'SQL1' WITH (
    ENDPOINT_URL = 'TCP://SQL1.domain.local:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
  ),
  'SQL2' WITH (
    ENDPOINT_URL = 'TCP://SQL2.domain.local:5022',
    FAILOVER_MODE = AUTOMATIC,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
 SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY) --
  ),
  'SQL3' WITH (
    ENDPOINT_URL = 'TCP://SQL3.domain.local:5022',
    FAILOVER_MODE = MANUAL,
 AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, -- DR
    BACKUP_PRIORITY = 60,
    SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
  );

Step 7 — Join Secondary replicas

SQL

-- SQL2
ALTER AVAILABILITY GROUP [AG_Sales] JOIN;
ALTER DATABASE [SalesDB] SET HADR AVAILABILITY GROUP = [AG_Sales];

-- SQL3 run
ALTER AVAILABILITY GROUP [AG_Sales] JOIN;
ALTER DATABASE [SalesDB] SET HADR AVAILABILITY GROUP = [AG_Sales];

Step 8 — Create the AG Listener

SQL

-- Primary
ALTER AVAILABILITY GROUP [AG_Sales]
ADD LISTENER 'AG_Sales_Listener' (
    WITH IP ((N'192.168.1.50', N'255.255.255.0')),
    PORT = 1433
);

Applications connect to 192.168.1.50,1433 and continue using the same address after failover.

Health checks

SQL

--
SELECT ag.name, ars.role_desc, ard.synchronization_state_desc,
       ard.synchronization_health_desc, ard.log_send_queue_size,
       ard.redo_queue_size
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_database_replica_states ard ON ard.replica_id = ars.replica_id;

--
SELECT * FROM sys.dm_hadr_availability_group_states;

Manual failover

SQL

-- Primary set Secondary run
ALTER AVAILABILITY GROUP [AG_Sales] FAILOVER;

Read-only routing (read scale-out)

SQL

-- Primary URL settings
ALTER AVAILABILITY GROUP [AG_Sales]
MODIFY REPLICA ON 'SQL1' WITH (
    PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ('SQL2','SQL3'))
);

ALTER AVAILABILITY GROUP [AG_Sales]
MODIFY REPLICA ON 'SQL2' WITH (
    SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://SQL2.domain.local:1433')
);

Adding ApplicationIntent=ReadOnly to the connection string automatically routes connections to a Secondary replica.

Summary

Feature	Always On AG
Failover unit	Database group
Automatic failover	Synchronous replica (decided by WSFC)
Read offload	Secondary READ_ONLY allowed
DR configuration	Asynchronous replica
Unified connection	AG Listener VIP

FAQ

Q. Besides Always On, what other MSSQL high-availability options are there? A. The main options are: (1) Always On Availability Groups (AG) — database-level replication, automatic failover, and read load balancing; (2) Failover Cluster Instances (FCI) — instance-level redundancy on shared storage; (3) Log Shipping — for DR with acceptable lag; (4) Database Mirroring (legacy, deprecated). If you need both automatic failover and read scale-out, Always On AG is the standard choice.

Q. How many servers do I need for Always On redundancy? A. At least two (primary + secondary). Automatic failover also requires a File Share Witness for WSFC quorum, or a third node. Enterprise edition supports up to 8 replicas.

#mssql#sql-server#always-on#ha#failover#windows-server

Editorial note

AI tools may assist drafting. Check the evidence in each guide for whether commands were run and which environment was used. Confirm the current docs for your version before applying instructions, and email us about errors. email us

How we write and review →Editorial policy →

Questions & answers

Ask a question about this guide. We'll review and reply.

MSSQL Always On Availability Groups — SQL Server HA Redundancy Configuration