PowerShell and Failover Clustering (WSFC)

This is one article in a series that cover how to use PowerShell scripts to automatically install SQL Server. The full script set can be found here.

High availability in production SQL Servers is often a desirable feature. Both Clustered Instances (shared storage) and Availability Groups (non-shared storage) require the Windows Server Failover Clustering (WSFC) on each of the cluster nodes. In this article, we’re going to look at how to use PowerShell to create a new failover cluster.

Before we can create the failover cluster, there is some information to be gathered and decisions to be made.

1. Cluster Name – Name of the cluster’s Active Directory computer object.
2. Cluster IP Address – Static IP address for the cluster.
3. Cluster Nodes – The names of the servers that will be nodes of the cluster.
4. Subnets To Ignore – Any network adapters, that are not on DHCP enabled subnets, must either be ignored or have static addresses assigned.
5. Quorum Mode – Methodology used to determine the cluster’s quorum voting.
   1. Node Majority – Active cluster nodes determine the quorum. At least 1/2 of the possible votes must be affirmative for the cluster to maintain a healthy state.
   2. Node and File Share Majority – A remote file share acts as a voting witness for the active nodes involved in a quorum vote. As with Node Majority, at least 1/2 of the possible votes must be affirmative for the cluster to maintain a healthy state.
   3. Node and Disk Majority – A shared disk acts as a voting witness, along with the active nodes involved in a quorum vote. Same limitation on possible votes as other Node based options.
   4. Disk Only – A shared disk acts as a witness. Quorum is determined by which nodes can access the disk. There is no minimum number of possible votes required.

Before the failover cluster object can be created in
Active Directory, the Remote Server Administration Tools (RSAT) will need to be installed on the computer running the PowerShell script
(Windows 10 or Server 2016) .

In a previous post, we covered how to use a JSON file to provide parameter values to a PowerShell script.

Param ([string]$ConfigJSON)
$Config = ConvertFrom-Json -InputObject (Get-Content -Path “$ConfigJSON” -Raw);

Next, we’ll import our failover cluster information into a PowerShell variable ($Config).

[array]$Nodes = (($Config).Servers).ServerName
$ClusterName = (($Config).WSFC).ClusterName;
$ClusterIPAddress = (($Config).WSFC).ClusterIP;
$IgnoreIPRange = (($Config).WSFC).IgnoreIPRange;
$QuorumType = (($Config).WSFC).QuorumType;
$FileShareSecurityGroup = (($Config).WSFC).FileShareSecurityGroup;
$FileShareWitness = (($Config).WSFC).FileShareWitness;
$DNSRoot = ($Config).DomainName;

The Windows Server Failover Clustering feature (WSFC) must be enabled on each server that is going to be a part of the cluster. To accomplish this, we’ll use a ForEach loop to add the feature to each server in the $Nodes array.

ForEach ($Server in $Nodes) {
    Invoke-Command -ComputerName $Server -ScriptBlock {
    Import-Module ServerManager;
    Add-WindowsFeature Failover-Clustering -IncludeManagementTools;
}}

Next import the Active Directory and Failover Clustering modules to the current session on the local computer.

Import-Module ActiveDirectory;
Import-Module FailoverClusters;

Then we’ll create a comma-separated list (string) of the servers in the cluster, and validate the cluster configuration using the Test-Cluster commandlet. If the validation fails, we can abort the script using error handling (more on that in another post).

$ClusterList = [string]::Join(“,”,$Nodes);
Test-Cluster -Node $ClusterList;

Once the Test-Cluster validation succeeds, the cluster object in Active Directory can be created using the New-Cluster commandlet.

New-Cluster -Name $ClusterName -Node $ClusterList -NoStorage -StaticAddress $ClusterIPAddress -IgnoreNetwork $IgnoreIPRange;

After the cluster object is created, the Set-ClusterQuorum commandlet is used to configure the Quorum Mode.

Note: If the quorum mode is going to be Node and File Share Majority, the file share will need to be created prior to setting the quorum mode. A file share can be used by multiple clusters. The cluster object must be granted read/write access to the file share (or a subdirectory of the share). If the share is going to be used by multiple clusters, it may be easier to create an AD security group, grant it access to the file share, and then populate the group with the computer objects of each cluster. Adding the cluster objects to the security group can be accomplished using Add-AdGroupMember.

Add-ADGroupMember $FileShareSecurityGroup -Members “$ClusterName$”;
Start-Sleep -Seconds 10 # Wait for cluster object & permissions to replicate across DCs

The syntax of Set-ClusterQuorum varies depending on the Quorum mode chosen. An If statement can be used in the PowerShell script to look at the $QuorumType variable from the JSON file to determine which syntax is used with the Set-ClusterQuorum command.

For Node Majority:

If ($QuorumType -eq “NodeMajority”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -NodeMajority;} 

For Node and Disk Majority:

If ($QuorumType -eq “NodeAndDiskMajority”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -NodeAndDiskMajority;} 

For Node and File Share Majority:

If ($QuorumType -eq “NodeAndFileShareMajority”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -FileShareWitness $FileShareWitness;}

For Disk Only:

If ($QuorumType -eq “DiskOnly”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -DiskOnly “Cluster Disk 1”;}

Once the quorum is configured, the cluster can be started using Start-Cluster.

Start-Cluster -Name “$ClusterName`.$DNSRoot”;

Here is the complete script:

[array]$Nodes = (($Config).Servers).ServerName
$ClusterName = (($Config).WSFC).ClusterName;
$ClusterIPAddress = (($Config).WSFC).ClusterIP;
$IgnoreIPRange = (($Config).WSFC).IgnoreIPRange;
$QuorumType = (($Config).WSFC).QuorumType;
$FileShareSecurityGroup = (($Config).WSFC).FileShareSecurityGroup;
$FileShareWitness = (($Config).WSFC).FileShareWitness;
$DNSRoot = ($Config).DomainName;

ForEach ($Server in $Nodes) {
    Invoke-Command -ComputerName $Server -ScriptBlock {
    Import-Module ServerManager;
    Add-WindowsFeature Failover-Clustering -IncludeManagementTools;
}}

Import-Module ActiveDirectory;
Import-Module FailoverClusters;
$ClusterList = [string]::Join(“,”,$Nodes);

Test-Cluster -Node $ClusterList;

New-Cluster -Name $ClusterName -Node $ClusterList -NoStorage -StaticAddress $ClusterIPAddress -IgnoreNetwork $IgnoreIPRange”;

If ($QuorumType -eq “NodeMajority”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -NodeMajority;
} 
If ($QuorumType -eq “NodeAndDiskMajority”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -NodeAndDiskMajority;
} 
If ($QuorumType -eq “NodeAndFileShareMajority”) {
	Add-ADGroupMember $FileShareSecurityGroup -Members “$ClusterName$”;
	Start-Sleep -Seconds 10 # Wait for cluster object & permissions to replicate across DCs
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -FileShareWitness $FileShareWitness;
}
If ($QuorumType -eq “DiskOnly”) {
	Set-ClusterQuorum -Cluster “$ClusterName`.$DNSRoot” -DiskOnly “Cluster Disk 1”;
}
Start-Cluster -Name “$ClusterName`.$DNSRoot”;