Building an Availability Group

October 16, 2026

Tested on SQL Server 2022 (two test instances), October 2026: the endpoints, CREATE and JOIN, automatic seeding, the readable secondary and a manual failover and failback all worked. Windows-authenticated endpoints, the listener and automatic failover have since been tested on a Windows Server failover cluster.

This post covers building an Availability Group (AG) from scratch on Windows Server Failover Clustering with automatic seeding (SQL Server 2016 onwards). Server names, IP addresses and the service account below are placeholders.

Before you start

The Windows failover cluster is built and passes validation, with a quorum witness.
Every replica runs the same SQL Server version. Full AGs need Enterprise; Standard only supports Basic AGs with one database.
The Always On feature is enabled on each replica in SQL Server Configuration Manager, or with PowerShell: Enable-SqlAlwaysOn -ServerInstance ‘NODE1’ -Force. This restarts the SQL Server service.
Each database is in FULL recovery and has had a full backup.
Data and log file paths are identical on every replica, which automatic seeding needs.

Step 1: Endpoints (run on every replica)

CREATE ENDPOINT [Hadr_endpoint]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (ROLE = ALL,
                            AUTHENTICATION = WINDOWS NEGOTIATE,
                            ENCRYPTION = REQUIRED ALGORITHM AES);
GO
-- The SQL Server service account of the other replicas
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\sqlservice];
GO
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE = START;

If all replicas run under the same domain account, that account is already the owner of its own endpoint and only needs a login on the other servers.

Step 2: Create the AG (on the primary)

CREATE AVAILABILITY GROUP [AG1]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
      DB_FAILOVER = ON,
      HEALTH_CHECK_TIMEOUT = 30000)
FOR DATABASE [YourDB]
REPLICA ON
    N'NODE1' WITH (ENDPOINT_URL = N'TCP://node1.domain.local:5022',
                   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                   FAILOVER_MODE = AUTOMATIC,
                   SEEDING_MODE = AUTOMATIC,
                   SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
    N'NODE2' WITH (ENDPOINT_URL = N'TCP://node2.domain.local:5022',
                   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                   FAILOVER_MODE = AUTOMATIC,
                   SEEDING_MODE = AUTOMATIC,
                   SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Step 3: Join each secondary (on each secondary)

ALTER AVAILABILITY GROUP [AG1] JOIN;
ALTER AVAILABILITY GROUP [AG1] GRANT CREATE ANY DATABASE;

The GRANT lets automatic seeding create the database on this replica.

Step 4: Add a listener (on the primary)

ALTER AVAILABILITY GROUP [AG1]
ADD LISTENER N'AG1-LSN' (WITH IP ((N'10.0.0.50', N'255.255.255.0')), PORT = 1433);

For a multi-subnet AG, list one IP address per subnet, and set MultiSubnetFailover=True in application connection strings.

Step 5: Watch the seeding

SELECT ag.name AS ag_name, a.start_time, a.completion_time,
       a.current_state, a.failure_state_desc, a.number_of_attempts
FROM sys.dm_hadr_automatic_seeding a
JOIN sys.availability_groups ag ON ag.group_id = a.ag_id
ORDER BY a.start_time DESC;

When seeding has finished, check the Availability Group’s health. For a very large database, seed manually instead: restore the full and log backups on the secondary WITH NORECOVERY, then run ALTER DATABASE [YourDB] SET HADR AVAILABILITY GROUP = [AG1]; on the secondary.

After the build

Copy logins with their SIDs to every replica, plus Agent jobs, operators and linked servers.
Make backup jobs AG-aware: sys.fn_hadr_backup_is_preferred_replica(N’YourDB’) returns 1 on the replica that should take the backup.
Test a manual failover and failback before go-live, and check the application reconnects through the listener.
A readable secondary only accepts connections that ask for read-only access: add ApplicationIntent=ReadOnly to the connection string (in sqlcmd, -K ReadOnly).

From my SQL Server DBA handbook.

Setup

Clive Roberts