SQL Server Always On Availability Groups Setup Guide | DBA

I've set up Always On Availability Groups more times than I can count — on two-node dev clusters, eight-node production environments, stretched across data centers three time zones apart. The documentation makes it look clean. Reality is messier. This guide covers what actually works.

Before we get into the T-SQL, a word on scope: this is Enterprise Edition, Windows Server Failover Cluster, synchronous commit with automatic failover. If you're on Standard Edition, or you're trying to do this without a cluster (Clusterless AGs), the rules change significantly — I cover those scenarios in a separate deep dive.

What Always On Actually Gives You

Always On Availability Groups are not the same as mirroring with a new name. AGs replicate at the database level using a log shipping stream over a dedicated endpoint. The primary replica ships hardened log blocks to secondaries; synchronous secondaries acknowledge before the transaction commits (that's your zero data loss path). Asynchronous secondaries get the data a few seconds later and are fine for DR or readable replicas in geographically distant sites.

What you get that you didn't have with mirroring:

Multiple secondaries (up to 8 replicas total in Enterprise)
Readable secondaries for reporting offload
A listener — a virtual network name and IP that clients connect to regardless of which node is primary
Automatic failover when combined with a synchronous secondary and quorum
AG-level backups so you can take log backups from a secondary

What you do not get: shared storage, automatic login/job synchronization, automatic failover of non-AG databases. More on that later.

Prerequisites: Get These Right Before You Touch SQL Server

Free · 2 Minutes

How healthy is your database, really?

Get your free database health score — spot risks before they become incidents.

Get my health score

The most common reason an AG implementation fails before it starts is skipped prerequisites. Read this section carefully.

Windows Server Failover Clustering

Always On requires WSFC even if you're not going to use automatic failover. The cluster doesn't have to own any disk resources — it can be a pure software cluster — but it must exist and both (or all) nodes must be members.

Cluster requirements:

All nodes in the same Active Directory domain
Windows Server 2016 or later (2019/2022 strongly preferred — they fixed a lot of quorum edge cases)
A cluster network dedicated to cluster heartbeat traffic, separate from your client network
DNS resolution working for all node names and the cluster name object (CNO)

One thing that trips people up: the cluster name object needs permissions to create computer objects in AD for the AG listener. Either pre-stage the listener's computer object in AD and grant the CNO control over it, or give the CNO permissions to create computer objects in the OU. Skipping this causes listener creation to fail with a cryptic AD permissions error that looks like a SQL problem but isn't.

Quorum Configuration

Quorum is the mechanism that prevents split-brain — a situation where two nodes both think they're primary and start writing conflicting data. You need to understand it before you turn on automatic failover.

For two-node clusters: use Node and File Share Majority. Point the file share witness at something stable — a file share on a domain controller, a DFS share, anything that isn't on one of the two cluster nodes. Cloud Witness (Azure Blob Storage) works well here and has zero infrastructure cost.

For three or more nodes with an odd count: Node Majority works without a witness. For even node counts, add a witness.

The rule that matters: your cluster needs more than half of its votes to remain operational. If you lose quorum, the cluster stops — which means all AGs fail over or go offline, depending on your configuration. Plan for the failure scenarios, not just the happy path.

SQL Server Service Account

Each SQL Server instance needs a domain service account (not a local account, not NETWORK SERVICE in a clustered context). The accounts on both replicas need:

The ability to connect to each other's database mirroring endpoint
This is handled via GRANT CONNECT ON ENDPOINT — we'll do this explicitly

If you use the same service account on all replicas (common), you grant the account on the endpoint. If you use different accounts per node, grant each account explicitly.

Identical SQL Server Versions

All replicas must run the same SQL Server version and edition. Same CU level is strongly recommended — Microsoft supports mixed patch levels for rolling upgrades but it's a transient state, not a permanent one. I've seen replication stream incompatibilities when two nodes are more than one CU apart during normal operation. Stay tight on patching.

Step 1: Enable the Always On Feature

This is done at the SQL Server instance level via SQL Server Configuration Manager, not T-SQL. You have to do this on every replica.

Open SQL Server Configuration Manager → SQL Server Services → right-click your SQL Server service → Properties → AlwaysOn High Availability tab → check "Enable AlwaysOn Availability Groups" → OK.

Restart the SQL Server service. Not the whole server — just the service. Then verify:

SELECT SERVERPROPERTY('IsHadrEnabled');
-- Returns 1 if enabled, 0 if not

If it returns 0, the service restart didn't take or you enabled it on the wrong instance. Don't proceed until this returns 1 on every replica.

Step 2: Create the Database Mirroring Endpoint

The endpoint is the TCP channel that replicas use to ship log blocks to each other. Create it on every instance.

-- Run on EACH replica
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
    );
GO

Port 5022 is the convention — use it unless you have a conflict. AES encryption is required by default in modern SQL Server versions; don't downgrade this.

Now grant the service accounts permission to connect:

-- If all replicas use the same domain account:
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [DOMAIN\SQLSvcAccount];
GO

-- If different accounts per replica, run the appropriate GRANT on each instance
-- Grant the remote account, not the local one

Verify the endpoint is running:

SELECT name, state_desc, role_desc 
FROM sys.database_mirroring_endpoints;
-- Should show: STARTED, WITNESS or PARTNER depending on role

Step 3: Prepare the Databases

Three requirements for a database to join an AG:

Recovery model must be FULL
At least one full backup must exist
No active database snapshots

-- Set recovery model
ALTER DATABASE [YourDatabase] SET RECOVERY FULL;
GO

-- Take a full backup (you'll need this for initialization)
BACKUP DATABASE [YourDatabase] 
TO DISK = N'\\backup-share\YourDatabase_full.bak'
WITH COMPRESSION, STATS = 10;
GO

-- Take a log backup immediately after
BACKUP LOG [YourDatabase]
TO DISK = N'\\backup-share\YourDatabase_log.bak'
WITH COMPRESSION;
GO

The backup must be accessible from the secondary replica for initial seeding — use a network share both nodes can read, or use automatic seeding (SQL Server 2016+) which ships the data directly over the mirroring endpoint.

Step 4: Create the Availability Group

This is the main event. I'm showing T-SQL here rather than the wizard because the wizard hides what it's doing and you need to understand the parameters when things go wrong at 3am.

-- Run on the PRIMARY replica
CREATE AVAILABILITY GROUP [AG_Production]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000,
    DB_FAILOVER = ON,
    DTC_SUPPORT = NONE,
    REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0
)
FOR DATABASE [YourDatabase]
REPLICA ON 
    N'PRIMARY-NODE' WITH (
        ENDPOINT_URL = N'TCP://primary-node.domain.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    ),
    N'SECONDARY-NODE' WITH (
        ENDPOINT_URL = N'TCP://secondary-node.domain.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
    );
GO

Parameter breakdown — the ones that actually matter:

AVAILABILITY_MODE = SYNCHRONOUS_COMMIT: Zero data loss. The primary waits for the secondary to harden the log before committing. Use this for replicas you care about. Use ASYNCHRONOUS_COMMIT for DR replicas that are geographically distant — latency from a synchronous commit across 1,000 miles will kill your transaction throughput.
FAILOVER_MODE = AUTOMATIC: The cluster will failover to this replica automatically if the primary becomes unavailable. Only valid with synchronous commit. Only set this on replicas you trust to be current.
SEEDING_MODE = AUTOMATIC: SQL Server seeds the secondary directly over the endpoint. No manual backup/restore required. Requires the secondary instance to have GRANT CREATE ANY DATABASE permission.
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0: The primary doesn't require any secondary to be in sync before committing. Setting this to 1 means a transaction can't commit if the secondary falls behind — use this only when you absolutely cannot tolerate any data loss at the cost of potential primary blocking.
FAILURE_CONDITION_LEVEL = 3: Triggers failover on SQL Server service down, critical errors, or resource pressure (OS/memory). Level 4 adds any moderately-severe SQL error. I use 3 in production to avoid flapping on transient conditions.

Step 5: Join the Secondary Replica

The AG was created on the primary. Now join it on the secondary:

-- Run on SECONDARY replica
ALTER AVAILABILITY GROUP [AG_Production] JOIN;
GO

-- If using SEEDING_MODE = AUTOMATIC, grant create database permission
ALTER AVAILABILITY GROUP [AG_Production] GRANT CREATE ANY DATABASE;
GO

With automatic seeding, SQL Server will now start copying the database to the secondary. Watch progress:

-- On the secondary, check seeding progress
SELECT ag.name AS ag_name,
       d.name AS 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
FROM sys.dm_hadr_database_replica_states dbs
JOIN sys.availability_groups ag ON dbs.group_id = ag.group_id
JOIN sys.databases d ON dbs.database_id = d.database_id
WHERE dbs.is_local = 1;

SYNCHRONIZING → SYNCHRONIZED is the progression you want. If it stalls at INITIALIZING, check the SQL Server error log on both nodes — seeding failures show up there with detail.

Step 6: Create the Availability Group Listener

The listener is the virtual network name your applications connect to. Without it, your app has to know which node is currently primary and reconnect when failover happens. The listener makes failover transparent to clients.

-- Run on PRIMARY replica
ALTER AVAILABILITY GROUP [AG_Production]
ADD LISTENER N'AG-Listener' (
    WITH IP (
        (N'192.168.1.100', N'255.255.255.0')
    ),
    PORT = 1433
);
GO

Use a dedicated IP address — not the IP of either node. This IP needs to be:

In the same subnet as your nodes (or properly routed if multi-subnet)
Pre-authorized in DNS or reserved in DHCP — don't let something else grab it
Accessible from your application servers on port 1433

For multi-subnet deployments (nodes in different subnets, common in stretched clusters or Azure), add both subnet IPs and set the client connection string to MultiSubnetFailover=True. This makes the client attempt connections to all IPs in parallel rather than serially, which cuts reconnect time dramatically.

Verify listener creation:

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_Production'
);

Step 7: Verify the AG is Healthy

Don't trust the SSMS dashboard alone. Run the DMVs:

-- Overall AG health
SELECT ag.name,
       ar.replica_server_name,
       ar.availability_mode_desc,
       ar.failover_mode_desc,
       ars.role_desc,
       ars.operational_state_desc,
       ars.recovery_health_desc,
       ars.synchronization_health_desc,
       ars.connected_state_desc
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;

-- Database-level synchronization detail
SELECT ag.name AS ag_name,
       ar.replica_server_name,
       d.name AS database_name,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.log_send_queue_size,
       drs.redo_queue_size,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON drs.group_id = ag.group_id
JOIN sys.databases d ON drs.database_id = d.database_id
ORDER BY ag.name, ar.replica_server_name;

What healthy looks like: SYNCHRONIZED state, HEALTHY synchronization health, connected_state = CONNECTED, zero or near-zero log_send_queue_size and redo_queue_size.

What unhealthy looks like and why:

SYNCHRONIZING (not SYNCHRONIZED): Secondary is catching up. Normal after a restart or brief disconnection. If it stays SYNCHRONIZING for more than a few minutes with active redo_queue growth, you have a performance problem on the secondary.
NOT SYNCHRONIZED: Replication stream is broken. Check the error log on both replicas. Usually an endpoint connectivity issue or the secondary was offline and missed log that's no longer available.
DISCONNECTED: The primary can't reach the secondary. Firewall, network, or the secondary instance is down.

Testing Failover

Test before production traffic hits it. Do a manual failover first — it's controlled and reversible:

-- Run on the SECONDARY you want to promote to primary
ALTER AVAILABILITY GROUP [AG_Production] FAILOVER;
GO

This is a manual failover — the old primary becomes a secondary. The AG stays healthy, no data is lost (synchronous mode), and the listener IP moves to the new primary. Your applications reconnect via the listener and they're back in business.

To test automatic failover, you have to actually bring down the primary. The clean way: stop the SQL Server service on the primary node. The cluster should detect the failure within the health check timeout (30 seconds in our config) and the secondary promotes automatically. The rough way: pull a network cable or power-cycle the node. Do the clean test first.

After automatic failover, verify:

The old secondary is now showing ROLE = PRIMARY in the DMVs
The listener DNS name resolves to the new primary's IP
Application connectivity is restored (test with a query, not just a ping)
The old primary, when it comes back up, joins as a secondary and starts synchronizing

Common Gotchas That Will Cost You Time

Logins and Jobs Don't Replicate

This is the one that bites the most people. SQL Server Agent jobs and SQL logins exist at the instance level, not the database level. When you failover, your application databases are now accessible on the secondary — but the logins and jobs that went with those databases aren't there unless you synchronized them manually.

The fix: scripting jobs and logins to the secondary is a manual process. Some shops use a scheduled T-SQL job on the primary that scripts logins and recreates them on the secondary. Others use PowerShell. The important thing is that you don't discover this gap during an actual outage.

TEMPDB is Not Replicated

Temp tables, table variables, anything in tempdb — it doesn't follow the failover. This rarely matters for applications (they recreate temp objects per session), but it matters for long-running processes that were mid-execution when failover happened. Those processes fail and must be restarted.

Linked Server Connections to the Old Primary

If any process connects to the primary by server name (instead of listener name), it breaks on failover. Audit your linked servers, SSIS connection managers, SSRS data sources, and ETL jobs. Anything that uses a hardcoded server name instead of the listener will fail. The listener exists specifically to prevent this — use it.

Redo Queue Growth During Heavy Workloads

The secondary applies log in a single thread (the redo thread). During heavy bulk inserts or index rebuilds on the primary, the redo queue on the secondary can grow significantly. This isn't immediately dangerous but it means your secondary is falling behind — if you failover during this window, clients have to wait for the secondary to apply the remaining log before it's current.

Monitor redo_queue_size during maintenance windows. If it's growing faster than redo_rate can consume it, you'll need to throttle your maintenance operations or accept a temporary gap in RPO.

Certificate-Based Authentication

If your nodes are in different domains (workgroup clusters, cross-domain DR) or if you're connecting to Azure SQL Managed Instance as a replica, you can't use Windows authentication for the endpoint. You need certificate-based authentication instead. This is more complex to set up but works cross-domain.

Database Owner = SA

Some databases have a non-SA owner, which can cause issues with certain AG operations. Before adding a database to an AG, set the owner to SA:

ALTER AUTHORIZATION ON DATABASE::[YourDatabase] TO sa;

Not always required, but it eliminates an entire class of weird permission errors.

Connection String Requirements

Your application connection string needs to reference the listener, not a specific node. Additionally:

Server=AG-Listener,1433;Database=YourDatabase;
Integrated Security=True;
ApplicationIntent=ReadWrite;
MultiSubnetFailover=True;

ApplicationIntent=ReadWrite: Routes to the primary. For read workloads you want to offload, use ApplicationIntent=ReadOnly — this routes to a readable secondary if one is configured.
MultiSubnetFailover=True: Enables parallel IP connection attempts. Even in single-subnet deployments, this setting reduces failover reconnect time. Set it everywhere.
Connect Timeout: Default is 15 seconds. During failover, the listener IP moves to the new primary within the cluster failover time (typically 20-45 seconds from failure detection). Your application will see timeouts during this window. Size your retry logic accordingly — 2-3 retries with 5-second backoff is a reasonable starting point.

Monitoring in Production

Set up alerts before you go live. The minimum viable monitoring set:

-- Check synchronization health — alert if anything isn't HEALTHY
SELECT ar.replica_server_name, 
       drs.synchronization_health_desc,
       drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.synchronization_health_desc != 'HEALTHY'
   OR drs.redo_queue_size > 51200; -- Alert if redo queue > 50MB

-- Check AG failover readiness
SELECT ag.name,
       COUNT(CASE WHEN ars.role_desc = 'PRIMARY' THEN 1 END) AS primary_count,
       COUNT(CASE WHEN ars.synchronization_health_desc = 'HEALTHY' 
                   AND ar.availability_mode_desc = 'SYNCHRONOUS_COMMIT' THEN 1 END) AS healthy_sync_secondaries
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
GROUP BY ag.name
HAVING COUNT(CASE WHEN ars.role_desc = 'PRIMARY' THEN 1 END) != 1;

Wire these into SQL Server Agent alerts or your monitoring platform. An AG with no synchronous secondary in HEALTHY state is an AG with no automatic failover. You want to know about that immediately, not when you're paging through dashboards at 2am trying to figure out why a failover didn't happen.

For deeper coverage of operational monitoring — the DMVs that tell you about wait stats on the AG threads, identifying replication lag by time rather than just queue size, and Extended Events sessions for AG health — see the companion post on SQL Server performance monitoring with DMVs and Extended Events.

When to Call in Help

Always On AGs are complex infrastructure. The initial setup, if done carefully, usually goes smoothly. The problems tend to show up in edge cases: stretched clusters across data centers, listener behavior in multi-subnet topologies, AG failures during Windows patching cycles, or debugging synchronization stalls under load.

If you're deploying AGs for a production environment and you don't have hands-on experience with WSFC behavior under failure conditions — not just "I've read about it" experience but actual "I've watched the cluster failover and debugged why it didn't" experience — consider bringing in a DBA who's done this. The downtime risk from a misconfigured quorum or a listener that doesn't float correctly is real.

That's what I do. If you're planning an AG deployment and want a second set of eyes on the design, or if you're in the middle of an AG problem right now, get in touch.