SQL Server AlwaysOn Groups and FCIs Part2

Welcome to part 2. Following on from Part1 , it's time to push on with presenting the shared storage to Node1 and Node2 and also create the Windows 2012 cluster. We'll also look at the quorum too and see how this affects failovers. So, without further ado, it's time for the 2nd brutal assault on your grey matter :-)

For the purposes of this article the following apply

AO AlwaysOn
FCI Failover Cluster Instance
iSCSI Internet Small Computer System Interface
SAN Storage area network
Replica An instance of SQL Server that is part of an AlwaysOn group
WSFC Windows Server Failover Cluster
vNIC Virtual network card
vCPU a proportionate share of a physical host CPU (no one to one representation)
OS Windows Operating System

You should, by now, have the 5 virtual machines deployed and configured. The Windows 2008 R2 and Windows 2012 R2 installers are extremely straight forward to use. Thoroughly test all networking to ensure good communications across the specified network ranges. We're now going to start presenting the LUNS we created previously, these will be presented over iSCSI to Node1 and Node2. This is done as follows.

Presenting the iSCSI LUNs 

On Node1, open the iSCSI Initiator, you may find this on the "Server Manager" page from the "Tools" menu. The first time you open this tool it will ask you if you wish to start the service and set it to auto start, select "Yes" to this dialog box.

 

Once the initiator main dialog opens, on the first tab displayed (the "Targets" tab), we are prompted for the Target Address which will be used for the "Quick Connect". If you feel comfortable, you may skip this tab and go straight to the "Discovery" tab, here you'll need to manually enter the Target Portal address and port number, before going back to the "Targets" tab to discover any iSCSI targets. Personally, I feel it's quicker and easier to just use the quick connect for simple configurations.

Enter the target address on the "Targets" tab and click "Quick Connect", 

 

 

If you have set up your networking correctly, you'll see the list of iSCSI targets ready to be connected to the node. Click "Connect" for each target, click "Done" when all targets are connected and move to the "Favourite Targets" tab.

Connected

  

  

 

The listing of the iSCSI targets on this dialog just ensures they auto connect on a server restart. They're automatically added here when you "Quick Connect" the LUNs.

Move to the "Volumes and Devices" tab.

 

 

Click the "Auto Configure" button to add all volume bindings. Click "OK" to close the initiator.

 

 

In Computer Management the new disks will appear, they will be offline initially. Bring each disk online and then select any disk, then select "Initialise". A list of un initialised disks will be shown, this should basically represent all the new SAN disks that have just been connected via iSCSI. The disks are shown below online and initialised.

 

 

Now we can start to create the volumes and set up the mount points. Select the volume that will become the root drive and select "New simple volume". The wizard will start as shown below, click "Next" at the first screen.

 

 

Specify the volume size (accept the default offered to use the whole volume). Click "Next" to continue.

 

 

For the root drive, assign the drive letter and click "Next" to continue. 

 

 

Provide a volume label and click "Next" to continue. 

 

 

Click "Finish" to complete the wizard.

 

 

Now open Explorer and create the folder structure which will be used to hold the mounted volumes.

 

 

For the remaining volumes, invoke the "New Simple Volume" wizard. When you reach the option to assign a letter or path, either browse for the folder (as shown in the 2nd image below) or simply type the full path (in the 1st image below), for example "M:\Data". Click "Next" to continue.

  

 

 

If you wish to set the correct NTFS allocation unit size (64K) for your SQL Server data volumes, do this at this point in the wizard. Click "Next" to continue. 

 

 

All volumes created, formatted and letters or paths assigned.

 

Set all the newly attached SAN disks to Offline on Node1.

You must now perform the initiator steps on Node2. To do this, logon to Node2 and setup the initiator as described in the previous steps. Once the disks are attached to Node2, bring them online and check the drive letter and path assignments, they should reflect the configuration used previously, if not, correct them and then set them back to Offline.  

Creating the Windows Server Failover Cluster 

Now we have the storage attached to the 2 nodes which will host the FCI, we may start to form the Windows cluster. On each node, from the "Server Manager" dashboard, add the "Failover Clustering" feature. This is wizard driven and extremely straight forward. You merely need to invoke the "Add roles and features" wizard on the dashboard and step through to the features section where you will be able to select the "Failover Cluster" option. Once all 4 nodes have the feature installed, give each node a reboot to ensure all pending actions are cleared.

On Node1, from the "Server Manager" > "Tools" option, open the "Failover Cluster Manager" application. Click the "Create Cluster" option, if no validation has been previously performed the cluster validation will start first.

 

 

 

Click "Next" through any informational dialogs and eventually you will be required to provide details of the nodes with which you wish to form the cluster. Click "OK" to continue.

 

 

 Click "Next" to continue. 

 

 

 Select the test options and click "Next". 

 

 

 Click "Next" after reviewing the configuration. 

 

 

 The following dialog shows the cluster validation process.  

  

 

Once the validation finishes, review your cluster validation report and ensure success. With the "Create the cluster now using the validated nodes" checkbox selected, click "Finish", this will invoke the "Create Cluster" wizard.

Note:  If you have any failures you must remedy these first and re run the validation until successful.

 

  

When the "Create Cluster" wizard starts you will be required to supply a unique virtual networkname and a unique virtual IP address for the cluster access point. Let's take a short paragraph here to discuss the VNN and VIP.

When referred to as Virtual, this merely means assigned to the client access point, which in itself is a virtual connection point. For the virtual IP address and network name the following apply

  • The virtual IP address should be unique within the network range
  • The virtual networkname follows the same rules as an ordinary computername which would be assigned to a Windows computer
  • The virtual networkname should be a unique computername within the Windows domain

Just select a valid, but unique IP address and computername, these are usually assigned by your network and\or domain administrators. With the values supplied, click "Next" to continue.

 

 

 

 Deselect the "Add all eligible storage to the cluster" checkbox and click "Next". 

 

 

 Below is an example of the cluster configuration in process.

 

 

 Click "Finish" to create the cluster. 

 

 

 On Node1 open Computer Management and bring online the root drive and 4 mounted volumes. In the "Failover Cluster Manager", right click Disk storage (shown below) and select "Add Disk".  

  

 

I have all the volumes required online on Node1, so these are the disks I select as shown below. 

 

If when the disks have been added they complain that the storage is not connected to the current node, the cluster manager has attempted to online the disks on a node where the storage has not been presented, helpful huh? To remedy this, highlight all the volumes and select "Move available Storage" and using the "Select Node" option, send it to Node1.

In the "Failover Cluster Manager" main dialog, right click "Roles" and select "Create empty role". Give the role a meaningful name as shown below. Ensure the role is owned by Node1, if it isn't simply move it. With the group owned by the correct node, right click the role and select "Add Storage". 

 

 

 

 These are the disks I'll be adding to the new role, click "OK" here. 

  

 

Now to set the disk dependencies. For each of the mounted volume disks (6, 7, 9 and 10 in my case) open the "Properties" and on the "Dependencies" tab add the root drive cluster disk to the resource list.

 

 

That's the clustered volumes added to Node1 and Node2. Before going any further, don't be afraid to experiment a little. Perform the following and observe the results 

  • Take the root drive offline, all mounted volumes should go offline first
  • Right click the role and select "Start Role", all volumes come online
  • Move the role back and forth between Node1 and Node2
  • Take the root drive offline. Once all volumes go offline, select one of the mounted volumes and bring it online
  • Right click the role and select "Start Role" 

 

You should now have an excellent grasp of the cluster creation process as well as a firm understanding on the types of storage required for the cluster nodes. Now it's time to look at the Quorum. 

Quorum within the Windows Cluster

This subject has been a somewhat major cause of confusion on the forums in the past, this section will seek to address this topic and remove any area of uncertainty.  The Windows cluster requires some form of mediation to determine resource ownership during the normal active cluster operation. Mediation is necessary to avoid the situation where a catastrophic failure causes multiple nodes to attempt to claim the same resources. Let's start by reviewing the Quorum modes available to us, they are as follows;

 

Node majority (no witness)

Only nodes have votes. No quorum witness is configured. The cluster quorum is the majority of voting nodes in the active cluster membership.

 

Node majority with witness (disk or file share)

Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes in the active cluster membership plus a witness vote.

A quorum witness can be a designated disk witness or a designated file share witness.

No majority (disk witness only)

No nodes have votes. Only a disk witness has a vote. The cluster quorum is determined by the state of the disk witness.

The cluster has quorum if one node is available and communicating with a specific disk in the cluster storage. Generally, this mode is not recommended, and it should not be selected because it creates a single point of failure for the cluster.

 

On top of this, Windows 2012 R2 now offers "Dynamic Node Weight" configuration. Let's now look at the quorum in detail and also the new dynamic option to see how this works. We'll look firstly at Windows 2008 and then Windows 2012. 

 

Windows 2008

 Take our 4 node cluster and examine the quorum under Windows 2008 R2.

We have 4 notes each with an active vote, ordinarily under Windows 2008, we would receive cluster warnings stating that the quorum configuration does not meet the recommended practice. We would be required to introduce a witness to increase the number of active votes to 5. In the present configuration, a failure of 2 nodes in the 4 node Windows 2008 cluster would result in the cluster services going offline to avoid the "split brain" scenario. Execute the following PowerShell script on a Windows 2008 cluster and you'll see the configuration information available to you, it's fairly limited.

 

Get-ClusterNode | ft *

  

Windows 2012

However, within the Windows 2012 cluster, voting has been dynamically managed for us. Looking at the results below for our quorum configuration, we can see that each node has an equal weight or vote (Node Weight parameter), but look, Dynamic Node Weight has re balanced the cluster for us. Node 4 has had its vote dynamically revoked to keep the vote configuration to an odd number of nodes. 

 

Now let's add in a fileshare witness and re check the difference;

Right click the cluster name in the Failover Cluster Manager console and select "More Actions" > "Configure cluster quorum settings". This will launch the configuration wizard, the first selection available is shown below. Selecting the "Advanced quorum configuration" option and clicking "Next" will proceed to advanced options. This is shown in the next dialog screenshot.

 

  

These are the advanced options, note that you can elect certain notes for cluster voting or none or all. Clicking "Next" takes you to the "Select quorum configuration option" screen shown in the next dialog below.

 

 

Here we are back at the original configuration screen, I'm going to select "Select the quorum witness" and click "Next" to proceed.

 

 

I'm electing to configure a fileshare witness, click "Next" to continue.

 

 

You may browse for and create the share all in one operation. The browser below will allow you to select the server to store the fileshare (the DC\SAN in this case).  

 

 

 

Create the share by supplying a share name, local path on the DC\SAN server and permission level (Admin full, users read will be sufficient). Click "OK" to continue.

  

 

Click "Next" to proceed and complete the configuration.

  

 

Click "Finish".

 

 Rechecking the node vote configuration and the quorum mode, we can see the following; 

  • Quorum type is now Majority Node Set and Fileshare Witness.
  • All nodes have an active vote in the quorum, Dynamic Node Weight has not intervened as the quorum type now meets the required configuration for our number of cluster nodes. 

 

To finish the quorum configuration discussion, as a small test exercise, go ahead and shutdown random nodes. Re run the PowerShell query above and check the information returned, what differences do you see?

You should now have a firm grounding with the Windows operating system, the failover cluster manager application and the quorum configuration requirements. You should now have a virtual cluster that is both stable and ready to receive the Failover Cluster Instance and the 2 stand alone instances of SQL Server. These will be required to create our AlwaysOn group.

We'll deploy the FCI in Part 3 and look into this in more detail. We'll also install the 2 stand alone instances, before moving onto Part 4 where we’ll deploy the AO group.

As always, work through the guide and if you have any questions relating to Part 2 or get stuck in any way, post back in the discussion thread.