DB-유

Always On Availability Groups: Prerequisites and Setup

· mssql, always on, availability groups, high availability

I've watched several Availability Group setups fail, and every time the wizard wasn't the problem. The prerequisites were. So this post spends most of its time there.

AGs replaced database mirroring for keeping a synchronised copy on another server with automatic failover. Unlike a failover cluster instance, each replica has its own storage, several databases fail over together, and secondaries can serve read-only queries and take backups. Enterprise on 2016 and 2017 gets up to eight secondaries; Standard gets a Basic Availability Group with one database and one non-readable secondary, still a solid mirroring replacement.

What has to be true first

  • Two or more Windows Server 2012 R2 or 2016 machines in the same domain, with SQL Server 2016 or 2017 of the same edition and patch level as stand-alone instances, ideally the same collation and instance name.
  • The Failover Clustering feature installed on each node.
  • A domain account for the SQL Server service, the same on all replicas (or separate accounts that each have CONNECT on the others' endpoints).
  • Databases in FULL recovery model with at least one full backup taken.
  • Identical drive letters and paths for data and log files on all replicas. Not strictly required, but the wizard's automatic seeding and add-file operations assume it.
  • Port 5022 (the mirroring endpoint) open between the nodes, and 1433 open for clients.
  • A spare IP address per subnet for the listener, and a computer object name for it.

Build the Windows Server Failover Cluster

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Test-Cluster -Node SQLNODE1, SQLNODE2
New-Cluster -Name SQLCLUSTER01 -Node SQLNODE1, SQLNODE2 -StaticAddress 10.0.1.50 -NoStorage

-NoStorage matters: availability groups do not use shared disks, and the cluster wizard otherwise grabs any LUN visible to both nodes. Then configure quorum. With two nodes you need a witness, and I use a file share witness because it's the least fuss:

Set-ClusterQuorum -NodeAndFileShareMajority \\fileserver\SQLCLUSTER01_Witness

On Windows Server 2016 a cloud witness in Azure storage is an alternative when no third site exists. Never leave a two-node cluster with node majority only; losing one node takes the whole cluster offline.

Enable the feature on each instance

In SQL Server Configuration Manager, open the properties of the Database Engine service, AlwaysOn High Availability tab, tick Enable AlwaysOn Availability Groups, and restart the service. In PowerShell:

Enable-SqlAlwaysOn -ServerInstance SQLNODE1 -Force
Enable-SqlAlwaysOn -ServerInstance SQLNODE2 -Force

Confirm on each instance:

SELECT SERVERPROPERTY('IsHadrEnabled') AS hadr_enabled, SERVERPROPERTY('IsClustered') AS is_fci;

hadr_enabled should be 1; is_fci should be 0 for stand-alone instances.

Endpoints and permissions

The wizard does this, but I do it by hand. It shows what's happening and dodges the most common permission error:

-- on every 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);

CREATE LOGIN [CONTOSO\svc_sql] FROM WINDOWS;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [CONTOSO\svc_sql];

If each instance runs under a different service account, grant CONNECT to every other replica's account.

The availability group itself

Take a full and a log backup of each database that will join. Then, on the intended primary:

CREATE AVAILABILITY GROUP [AG_Sales]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON)
FOR DATABASE [Sales], [Inventory]
REPLICA ON
    N'SQLNODE1' WITH (ENDPOINT_URL = N'TCP://sqlnode1.contoso.local:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
    N'SQLNODE2' WITH (ENDPOINT_URL = N'TCP://sqlnode2.contoso.local:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

SEEDING_MODE = AUTOMATIC (new in 2016) streams the databases to the secondary over the endpoint, so you skip the backup-copy-restore dance. For very large databases, manual seeding with a restore WITH NORECOVERY on the secondary is still faster and more controllable. DB_FAILOVER = ON (2016+) makes a database going offline on the primary trigger failover, which the earlier versions did not.

On the secondary:

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

For a Basic Availability Group on Standard edition add WITH (BASIC) and limit it to one database and two replicas; readable secondaries are not permitted.

The listener

Applications connect to the listener, not to a node, so failover is transparent:

ALTER AVAILABILITY GROUP [AG_Sales]
ADD LISTENER N'AG-SALES' (WITH IP ((N'10.0.1.60', N'255.255.255.0')), PORT = 1433);

The cluster name object (SQLCLUSTER01$) needs Create Computer objects permission in the OU for this to succeed. Clients should add MultiSubnetFailover=True to the connection string, even in a single subnet, so reconnects after failover are fast.

Prove it with a failover

The Always On dashboard in SSMS (right-click the availability group → Show Dashboard) should show both replicas green and synchronised. In T-SQL:

SELECT ar.replica_server_name, drs.database_id, drs.synchronization_state_desc, drs.synchronization_health_desc,
       drs.log_send_queue_size, drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id;

Then perform a planned failover from the secondary and back:

-- run on SQLNODE2
ALTER AVAILABILITY GROUP [AG_Sales] FAILOVER;

Connect through AG-SALES before and after; SELECT @@SERVERNAME should change.

Errors you'll probably see

  • "Msg 35250: The connection to the primary replica is not active. The command cannot be processed." on JOIN. Almost always the endpoint: port 5022 blocked, the service account lacking CONNECT, or the FQDN in ENDPOINT_URL not resolving. Test with Test-NetConnection sqlnode1 -Port 5022 and check the ERRORLOG for "Database Mirroring login attempt failed".
  • "Msg 41158: Failed to join local availability group replica to availability group" together with "The cluster ... could not be accessed": the SQL Server service account needs to be able to read the cluster; add it to the local Administrators group on each node or grant the cluster permissions explicitly.
  • Listener creation fails with "The WSFC cluster could not bring the Network Name resource with DNS name 'AG-SALES' online" (error 19471). Pre-stage the computer object in AD and give the cluster name object Full Control on it, or grant Create Computer objects on the OU.
  • Automatic seeding sits at 0 percent. Check sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats; usually the secondary is missing the GRANT CREATE ANY DATABASE or the data path does not exist there.

Cluster with witness, feature enabled, endpoints with CONNECT, databases in FULL with a backup. Get those right and the rest goes quickly.