Rajendra Gupta
iSCSI configurations

Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups

July 14, 2020 by

This article explores the configuration of Windows failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups.

Prerequisites

In this series of articles, we will configure the SQL Server always on availability groups from end to end for your learning purpose. We covered the following topics in the previous articles.

  1. A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
    1. We installed Oracle Virtual Box with three VM’s
    2. We did installation of Windows Server 2016 standard edition with desktop experience
    3. We covered VM network adapter configurations
  2. Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
    1. We installed and configured domain controller, active directory and DNS
    2. Assign static IP’s to all VMs
    3. Join SQL Nodes in the MyDemoSQL.com domain

To follow along better, try to go over these previous articles before proceeding with this one.

Failover Cluster configuration for SQL Server Always On Availability Groups

We require a minimum of two nodes failover cluster for the SQL Server always on availability groups. We can setup AG without cluster as well starting from SQL Server 2017, but it gives you limited AG functionality. It is out of scope topic for this article series.

We prepared the following VMs for our demo purposes.

Server Name

IP address

Role

VDITest3

10.0.2.15

Domain Controller and Active Directory

SQLNode1

10.0.2.21

Primary Node of SQL AG

SQLNode2

10.0.2.22

Secondary Node of SQL AG

Now, we have a requirement to set up the Windows failover cluster for SQLNode1 and SQLNode2. To do so, launch Add Roles and Feature Wizard from the server manager. Enable the Failover Clustering feature in both the SQL nodes.

Failover Cluster configuration  for SQL Server Always On Availability Groups

Confirm the failover cluster installation for SQL Server Always On Availability Groups.

Add features

Put a tick mark on Failover Clustering and click on Add Features to install the feature with dependency.

Failover clustering

Review and confirm the installation. You can see it installs failover Cluster Management Tools along with the Failover Cluster Module for Windows PowerShell.

Confirm installations

It quickly installs the features on your respective server.

Feature installation status

Once you enabled the feature on both nodes, search and launch failover clustering from the start menu. It currently shows no items found because we haven’t configured the cluster yet.

No items found

Before we proceeded further, check the ping response from SQLNode1 to SQLNode2 and vice-versa.

Ping response from SQLNode1 to SQLNode2

Ping response

Ping response from SQLNode2 to SQLNode1

SQLNode2 to SQLNode1 ping

In case it does not work for you, disable the Windows firewall in both the nodes. Search for Windows Firewall in Start and disable all firewalls.

Note: Please do not disable the firewall in a production environment due to security reasons.

Windows firewall off

Validate Configurations for SQL Server always on availability groups

Click on the Validate Configurations in the Actions menu. You can read the description for learning purposes.

Validate Configurations

On the next page, add the nodes you want to add in the failover cluster. Here, I added both nodes for my cluster.

Select servers on a cluster

It performs various tests such as cluster configuration, network, Storage and Hyper-V configuration. We can perform limited tests as well, but it is good to perform all tests.

Run all validations

On the next page, it shows the servers for validation and lists down all tests it is going to perform.

Confirm message

It starts validations one by one for all rules. It shows the result of each test, whether passed, failed or any warnings.

View rules status

You can review the result of all test parameters in a cluster. Once reviewed, put a check on the Create the cluster now using validated nodes… It does not allow any additional nodes at this point. If you want, you can finish the process and revalidate the cluster servers.

Create the cluster now using validated nodes

It launches the Create Cluster Wizard.

Create cluster wizard

On the next page, we define an access point for administrating the cluster. It is a cluster name and cluster IP address.

Cluster name and IP address

Give a unique name for the cluster in your environment along with a virtual IP address. It should be in the IP range of the nodes network.

Cluster configuration

The cluster configuration is now complete. Click Next to start the cluster build process.

Cluster build process

It forms the failover cluster from both SQL nodes specified.

Forming cluster

Once the process is finished, launch the Failover cluster manager and view the nodes. It should show both nodes in the Up status.

View nodes in the cluster

Click on Roles, and it is empty because we have not added any roles yet in this cluster. You can verify the cluster name as SQLAGCLU.MyDemoSQL.com

No roles available

Enable iSCSI feature on Domain Controller server for SQL Server always on availability groups

In this article, we want to add the cluster storage from the domain controller server. For this purpose, connect to the DC server. Choose the iSCSI Target Server in the Add Roles and Features Wizard.

Enable iSCSI feature on Domain Controller server for SQL Server always on availability groups

Here we see, it installed the feature on the domain controller server.

iSCSI taget

We need to configure the iSCSI target server now. For this purpose, in the server manager, click on the File and Storage Services.

File and storage services

It opens another page with storage options. Click on the iSCSI from the menu located on the left-hand side.

iSCSI configurations

In iSCSI, it does not show any iSCSI virtual disks as now. It shows an option – To create an iSCSI virtual disk, start the New iSCSI virtual Disk Wizard.

To create an iSCSI virtual disk, start the New iSCSI virtual disk wizard.

Click on the hyperlink, and it launches iSCSI virtual disk wizard.

In the Virtual disk location, select the volume. We have only C drive available in the VM, so it shows that drive information including used and free space.

iSCSI virtual disk wizard for SQL Server Always On Availability Groups configurations

Specify an iSCSI virtual disk name. You can give it any name as per your preference. It creates a .vhdx file in the C:\iSCSI directory.

iSCSI virtual disk name

Specify a size for the virtual disk. You can configure a fixed or dynamic size. We should use a fixed size virtual disk for better performance.

iSCSI virtual disk size

We do not have any existing iSCSI target, so select the option to create a new iSCSI target.

New iSCSI target

Specify a target name for the iSCSI.

Target name and access

On the next page, we define the servers which will have access to the virtual disk. You can specify the node IP address and add it to the console.

Access servers

Similarly, search for the SQLNod2 IP address and add it.

Add Node 2 IP address

We get both SQL Nodes as part of the virtual disk access list.

Specify server access

Click Next and confirm your selections for the iSCSI target.

Confirm selections

Click Create. We created the iSCSI targets successfully.

iSCSI targets success

We configured the iSCSI target on the domain controller server. We need to use iSCSI initiator wizard on both the nodes to reflect the virtual disks on the failover cluster.

Connect to the SQLNode1 and launch the iSCSI initiator from the Server Manager -> tools -> iSCSI initiator.

In the iSCSI initiator, it asks for an iSCSI target.

iSCSI initiator.

Our iSCSI target is on the domain controller server, so specify the IP address of DC. It is 10.0.2.15 in my case. Click on Quick Connect after specifying the IP address.

Quick Connect

It shows you a list of iSCSI targets available on the specified IP address. We already have configured one iSCSI target, so select the discovered target and click done.

select the iSCSI target

Now, launch the Computer Management and click on Disk Management. In the disk management, it shows you the available Storage.

In the below screenshot, we see a root drive along with a 10 GB unallocated space. It is the same virtual disk of 10 GB that we configured earlier.

View cluster disk

Right-click on this disk and choose New Simple Volume. It opens the simple volume configuration wizard.

New Simple Volume

In the volume size, we can specify a different size for the volume, but it cannot exceed the virtual disk maximum size we specified earlier.

New Simple Volume size

Simple Volume drive letter

On the next page, assign a volume label and select the option to format this volume.

New Simple Volume label

Finish the wizard, and it shows up the drive, as shown below.

View disk

Add the disk as a cluster resource

To add this disk as a cluster resource, open the failover cluster manager and click on Storage -> Disks. Currently, It does not show any clustered disk in the console.

Add the disk as a cluster resource

Click on Add Disk, and it shows up the virtual disk we created earlier.

disk

It adds the cluster disk in the failover cluster manager as shown below.

View cluster disk

Cluster Quorum configuration

Quorum is an essential and critical component of a Windows failover cluster. A quorum keeps running the failover cluster based on the majority of votes in the group. It uses a voting mechanism to check for the node’s majority. It also helps to avoid a split-brain scenario where none of the nodes owns the resources.

We have the following quorum configurations in the Windows server.

  • Node Majority
  • Node and Disk Majority
  • Node and File Share Majority
  • No Majority
  • Dynamic Quorum configuration

I suggest you go through the article Windows Failover Cluster Quorum Modes in SQL Server Always On Availability Groups to understand this in detail.

Now, right-click on the Cluster name and go to More Actions -> Configure Cluster Quorum Settings.

Cluster Quorum configuration

It launches the cluster quorum wizard with a brief introduction.

Quorum configuration wizard

Select the option Advanced quorum configuration from the quorum confirmation options.

Quorum option

We can decide which nodes can do voting in a failover cluster configuration. By default, it selects all failover cluster nodes for voting eligibility.

Quorum voting configuration

Select the file share witness as a quorum witness on the next page.

Quorum witness

Before we proceed for the next step, create a file share in the domain controller VM and permit the Windows account by which we log in to SQL nodes. Ideally, you should not create the file share on the cluster nodes because in case that particular node goes down, file share witness also goes down.

Specify the shared folder path as a file share path.

File share witness

Review your configuration and confirm to proceed further.

Quorum confirmation

It has successfully configured the file share witness in our failover cluster configuration, as shown below.

Quorum settings

You can connect to the failover cluster manager, and it shows the file share witness in the console.

View quorum in a cluster

Conclusion

In this article, we configured the failover clusters on the virtual machine we created earlier. It also shows the iSCSI and file share witness quorum for SQL Server Always On Availability Groups. It completes the underlying foundation or infrastructure for always on configuration. In the next article, we will install SQL Server 2019 and configure an AG group.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events

Rajendra Gupta
3,760 Views