SQL Server Always On Availability Group setup

Symptom

Goal: set up automatic failover and a readable secondary replica for SQL Server with an Always On Availability Group (AG).

1. Set up a Windows Server Failover Cluster (WSFC)

Always On relies on an underlying WSFC. Install the Failover Clustering feature on all nodes and configure the network and witness. Create the cluster with PowerShell.

SQL Server

-- PowerShell (the Failover-Clustering feature must be installed on every node):
New-Cluster -Name sqlclu -Node node1,node2 -StaticAddress 10.0.0.50
-- Witness:
Set-ClusterQuorum -NodeAndFileShareMajority \\fs01\quorum

2. Enable Always On on each instance

Enable the Always On Availability Groups feature with SQL Server Configuration Manager or PowerShell. Then restart the SQL Server service. This binds cluster membership to the instance.

SQL Server

-- PowerShell:
Enable-SqlAlwaysOn -ServerInstance node1\SQL01 -Force
Enable-SqlAlwaysOn -ServerInstance node2\SQL01 -Force

3. Back up the database and restore it on the secondary WITH NORECOVERY

The database to be added to the AG must be in FULL recovery model. Take a full and a log backup on the primary; restore them on the secondary WITH NORECOVERY (so it can keep receiving redo). This is manual seeding; the alternative is automatic seeding.

SQL Server

-- On the primary:
BACKUP DATABASE app TO DISK='\\fs01\bak\app.bak';
BACKUP LOG app TO DISK='\\fs01\bak\app.trn';
-- On the secondary:
RESTORE DATABASE app FROM DISK='\\fs01\bak\app.bak' WITH NORECOVERY;
RESTORE LOG app FROM DISK='\\fs01\bak\app.trn' WITH NORECOVERY;

4. Create the endpoints and define the Availability Group

Create a HADR endpoint on port 5022 for inter-node redo traffic. Synchronous mode (SYNCHRONOUS_COMMIT) provides zero-data-loss automatic failover; asynchronous (ASYNCHRONOUS_COMMIT) is lagged but performant for remote DR.

SQL Server

CREATE ENDPOINT hadr_endpoint STATE=STARTED
  AS TCP (LISTENER_PORT=5022)
  FOR DATA_MIRRORING (ROLE=ALL, ENCRYPTION=REQUIRED ALGORITHM AES);

CREATE AVAILABILITY GROUP appag
  FOR DATABASE app
  REPLICA ON
    'NODE1\SQL01' WITH (ENDPOINT_URL='TCP://node1:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL)),
    'NODE2\SQL01' WITH (ENDPOINT_URL='TCP://node2:5022', AVAILABILITY_MODE=SYNCHRONOUS_COMMIT, FAILOVER_MODE=AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS=ALL));

5. Join the secondary to the AG and join the database

JOIN the AG on the secondary instance, then add the restored database to the AG. This step is needed if manual seeding was used; with automatic seeding it is handled on the primary via SET(SEEDING_MODE).

SQL Server

-- On the secondary:
ALTER AVAILABILITY GROUP appag JOIN;
ALTER DATABASE app SET HADR AVAILABILITY GROUP = appag;

6. Create the Availability Group Listener

The listener is the virtual name and IP that applications connect to; after a failover, connections are redirected automatically to the new primary. Use this name and MultiSubnetFailover=True in the application connection string.

SQL Server

ALTER AVAILABILITY GROUP appag
  ADD LISTENER 'appag-listener' (WITH IP ((N'10.0.0.60', N'255.255.255.0')), PORT=1433);

7. Test failover and verify synchronization state

When synchronization is 'HEALTHY', test a role change with a planned failover. For automatic failover, the mode must be synchronous and FAILOVER_MODE=AUTOMATIC. Monitor the state with sys.dm_hadr_availability_replica_states.

SQL Server

SELECT ag.name, ars.role_desc, ars.synchronization_health_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_groups ag ON ag.group_id=ars.group_id;
-- Planned failover (on the node that will become the new primary):
ALTER AVAILABILITY GROUP appag FAILOVER;