AlwaysOn Availability Groups

Terms (SQL Server 2012, 2014, 2016, 2017 Enterprise)

AG = Availability Group = AlwaysOn Availability Group = SQL Server technology that protects one or more databases on a SQL Server instance as a logical group, for HA or DR.

Node = A Windows Server participating in an AG.

SQL Server Instance = A complete installation of SQL Server. Including user databases, system databases, logins, linked servers, jobs etc. A single default-instance plus a number of named-instances could be installed on one node.

Replica = the user database(s) being protected by an AG.


List (high level)

Configure Always On (Operating System)
– Enable windows clustering on each node
– Add participating nodes to cluster
– Validate cluster
– Create cluster
– Multi-subnet?

Configure Availability Groups (SQL Server)
– Enable AG on each SQL Server instance through config manager
– Create endpoint on each replica
– Grant connect on each endpoint/replica
– Create an AG (primary replica)
– Join each secondary to the new AG (secondary replicas)

Configure Databases (Database)
– Join database to AG
– Configure synchronous / asynchronous replication
– Configure manual / auto failover
– Configure read only / non read only secondary

Configure the Listener (Availability Group)
– Select IP for each subnet
– Configure listener
– Test listener
– Test failover

Configure Advanced Options
– Read only routing
– Offloading backups
– Failover behavior
– setup monitoring

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s