Configuring AlwaysOn Availability Groups on SQL Server

INTRODUCTION

AlwaysOn AG implements HA at the database level, even though the nodes that host SQL instances and participate in the configuration are part of a Windows Failover Cluster. We refer to each node as an Availability Replica.

In a typical configuration, there is a Primary Replica and one or more Read-Only Secondary Replicas. A group of databases configured in an Availability Group can be failed over to an alternate replica together.

PREREQUISITES

1.1 Prerequisites

SUMMARY

1.2 The configuration procedure involves the following stages:

PROCEDURE

1.3 Enable Clustering on All Nodes

Figure 1: Add Roles and Features

1.3.2 Launch Add Roles and Features Wizard

Windows Failover Clustering is a Windows Feature. You can enable it via the wizard.

Figure 2

1.3.3 Accept defaults till you reach the Features stage.

At this stage, we need to select a server to configure. In this scenario, we are dealing with the local server (see Figure 4).

We have hidden the server names for security reasons, but you will see it in the bottom pane (Figure 4).

Figure 3
Figure 4

At this time, we don’t plan to configure a role. So, we click NEXT and follow until we get to Features (Figure 6).

Enable the checkbox for Failover Clustering. Then, click INSTALL.

Figure 5. Server Roles

1.3.4 Select Failover Clustering in the Features stage

Figure 6: Server Features

1.3.5 Close the wizard once done

Figure 7

1.4 Validate the Servers for Clustering

Windows currently supports up to 64 nodes in a failover cluster. Windows 2019 enhances the usefulness of this large number by providing a feature known as Cluster Sets. Validating potential member servers of a cluster means checking if they have all been appropriately configured to participate in the cluster.

1.4.1 Open Failover Cluster Manager

Figure 8: Validate Configuration

1.4.2 Open the Validate Configuration Wizard on the right.

Figure 9

1.4.3 On the Select Servers stage, specify all servers intended for the cluster. Once again, we have hidden selected servers.

We can add all participating servers from this screen.

Figure 10

1.4.4 Accept the default “Run all tests” option.

Figure 11
Figure 12

1.4.5 Ignore the disk-related warnings. You don’t require shared disks for AlwaysOn configuration.

Figure 13. Validated Tests

1.4.6 Complete the Wizard

Figure 14

At this point, you can review the Validation Report. Should there be any issues, you can correct them before proceeding. Some warnings can be ignored, but make sure to examine their meaning.

1.5 Create a Cluster

Figure 15. Create Cluster
Figure 16

1.5.2 Add all servers intended for clustering.

This screen is like the Validate a Configuration Screen. We can also add all the required nodes at once.

To add servers, click the Browse button.

Figure 17

1.5.3 Provide the desired Cluster name and an associated IP Address

This name works to create a Computer Name Object on the domain. Thus, you need permissions to create computer objects for the account performing installation. Also, note that all IP Addresses used for all members of the cluster must be in the same subnet, except if you are creating a multi-site cluster.

Figure 18

When creating a Failover Cluster for AlwaysOn Availability Groups, we do not need adding disks to the cluster. So, leave the option unchecked (See Figure 19).

Figure 19

1.5.4 Complete the Create Cluster Wizard

Figure 20
Figure 21

1.6 Configure Quorum

Typically, it should be an odd number of members. However, some configurations allow us to use an even number and remove voting rights from one node. A Witness is a non-server object that you can use to make up the odd number of voters. A File Share Witness gets preference in AlwaysOn configuration since there is no shared disk in an AlwaysOn configuration.

1.6.1 Create a share on a reliable server that does not participate in the cluster. Then, grant full permissions to the Computer Name Object bearing the same name as your cluster (step 4.3.3).

Figure 22
Figure 23

1.6.2 Right-click the cluster you’ve just created, and select More Actions > Configure Cluster Quorum Settings.

Figure 24
Figure 25

1.6.3 Select the option to configure a Quorum Witness

Figure 26

1.6.4 Select the option to configure a File Share Witness

Figure 27

1.6.5 Select the File Share (refer to 4.4.1):

Figure 28
Figure 29

1.6.6 Complete the Witness configuration

Figure 30
Figure 31

1.7 Install the SQL Server

Note that the SQL Server instances are not clustered in an AlwaysOn configuration. You configure and manage HA at the database level for the database groups. Refer to the relevant documentation for installing the SQL Server.

1.8 Enable AlwaysOn Availability Groups

Figure 32

1.8.2 Right-click the SQL Server service. Open the AlwaysOn High Availability tab.

1.8.3 Select the Enable AlwaysOn Availability Groups checkbox

Note that at that stage, you already get identification of the cluster previously configured. This way, you reinforce the order of the steps. You must enable clustering on each cluster node before enabling AlwaysOn on each SQL Server instance.

Figure 33

1.8.4 Restart SQL Server Service

1.9 Create a New Availability Group

1.9.2 Right-Click AlwaysOn High Availability > Availability Groups and select New Availability Group Wizard.

Figure 34

1.9.3 Follow the Wizard

Figure 35

1.9.4 Specify the Availability Group name. This name will be used to create a cluster role.

The Availability Group name is also a Virtual Name Object with an entry in Microsoft Active Directory. Thus, before this works, the Computer Name Object used as the Cluster Name must have permissions to (1) Create Computer Objects and (2) Read All Properties on Active Directory.

Figure 36

1.9.5 Select the databases desired to be part of this Availability Group. There can be several Availability Groups in one set of instances. The databases involved must be configured with the Full Recovery Model, and you have to take a backup. The wizard will check for these prerequisites.

Figure 37

1.9.6 Select the instances that will act as Secondary Replicas

Determine whether you require Synchronous or Asynchronous replication and Automatic or manual-only failover. Details of these choices’ implications are present in Microsoft documentation defined at the end of this document.

Typically, Synchronous-Commit Mode with Automatic Failover is for local resilience, and Asynchronous-Commit Mode is for using AlwaysOn as a DR solution due to the latency concerns.

Figure 38

1.9.7 Create a File Share for the initial backup

This file share is used to initialize the replication. The SQL Server service account should have full permissions to this file share. It is taken for granted that the SQL Server instances participating in this configuration use the same domain account as the SQL Server service account.

Figure 39

1.9.8 Select the share created in step 4.7.9 as the initial database backup location.

Figure 40

1.9.9 Review the validation stage. Note that the listener has not been created. You can do it later.

Figure 41

1.9.10 Review the summary of options and complete the configuration

Figure 42
Figure 43
Figure 44

1.10 Add an AG Listener

1.10.1 Right-Click AlwayOn High Availability > Availability Group Listener and select Add Listener.

Figure 45

An Availability Group Listener is also a Virtual Name Object (VNO) that you will create on the domain. Thus, it requires an associated IP Address.

As mentioned earlier, the Cluster Name Object (CNO) specified when creating the cluster must have two permissions on the Active Directory Domain for this to work:

i. Create Computer Objects
ii. Read All Properties

Figure 46

1.10.2 The configuration is complete. You can view the dashboard:

Figure 47

1.11 Issues Faced

During the first run of the cluster creation, you all disks on the cluster nodes are added to the cluster. It is not the desired state for AlwaysOn Availability Groups.

The solution was destroying the cluster followed by cleanup, and recreating it with the checkbox Add all Eligible Storage to the cluster cleared in step 4.3.3.

It was impossible to recreate the cluster until cleaning up the nodes with the Clear-ClusterNode PowerShell Commandlet.

Figure 48

Automatic Failover

Testing Automatic Failover by stopping the SQL Server Service from SQL Server Configuration Manager did not work. The PowerShell Commandlet Get-ClusterLog generated the cluster log -Node <NodeName> -TimeSpan 10, and it identified the following error:

000007f4.00002320::2016/07/07–06:10:59.654 WARN [RCM] Not failing over group EPG-SERVER-AG, failoverCount 25, failoverThresholdSetting 20, lastFailover 1601/01/01–00:00:00.000

The solution was to change the “maximum Failures” in the specified period for the Availability Group Cluster Role to a much higher value.

Figure 49

1.12 References

  1. Failover and Failover Modes
  2. Availability Modes

Originally published on https://codingsight.com/configuring-alwayson-availability-groups-on-sql-server/, an awesome blog for SQL Server, .NET, and JavaScript Developers.

Awesome blog focused on databases and Microsoft, .NET and cloud technologies. http://codingsight.com/