In these articles, we’ll look at how to set up high availability on one or more databases with the AlwaysOn feature, using the « dbatools » PowerShell library. So we need to install and configure a Windows Failover Cluster. To do this, we’ll need to set up an active directory and two SQL server instances on two different servers and install, using Failover Clusterusing. In this article you will learn :
- How to install the Failover Cluster feature
- How to configure Failover Cluster
- How to create a Cluster
- How to configure Quorum settings
Installation of the Failover Cluster
Now that we have our two SQL Server instances on our two VMs, we need to install and configure a Windows Failover Cluster.
So you can execute the powershell command on each of uor two VM :
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
Or, we're going to add a new feature on each of our two VMs, "Manage" -> "Add Roles and Features" and do "Next" to the "Feature" section, then check the "Failover Clustering" feature.Click "Next" then "Install". Then restart the VM.
Now, the following actions are to be carried out on a single server: for my part, I've chosen the "Server1" VM. To validate the configuration, go to "Server Manager" -> "Tools" -> "Failover Cluster Manager".
Configuration of the Failover Cluster
Click on "Validate Configuration", then in the "Select servers or cluster" section, select "Browse".
Click on "Advanced" then "Find Now" and select our two SQL servers.
Then click "Next" to the end and click "Finish".
Creation of the Cluster
We are now going to create the cluster.
To do this you can execute the powershell command :
New-Cluster -Name TestCluster -Node Server1,Server2 -StaticAddress 172.168.1.5Or, click on "Create Cluster" in the "Failover Cluster Manager".In the "Select servers" section, select "Browse".
Click on "Advanced" then "Find Now" and select our two SQL servers.
Then choose the cluster name and select an IP address, e.
g. "172.168.1.5".Click on "Next" to install the cluster.Check whether your cluster appears in "Computers" in the "Active Directory Users and Computers" active directory.
Configuration of Quorum settings
Finally, we're going to configure the "Cluster Quorum". To do this you can execute the powershell command on our VM, which hosts our AD :
$sharePath = "C:\ShareFolder"
New-Item -Path $sharePath -ItemType Directory
$shareName = "ShareFolder"
New-SmbShare -Name $shareName -Path $sharePath -FullAccess SQLSAV
$acl = Get-Acl $sharePath
$rule = New-Object System.Security.AccessControl.FileSystemAccessRule("SQLSAV", "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
$acl.AddAccessRule($rule)
Set-Acl $sharePath $aclAfter that you need to go in Sharing section of the folder, click on "Share" and click "share" again to share the folder at the domain level. Or, you can create manually a share directory on our VM, which hosts our AD.Right-click on the directory, select "Properties" and go to the "Sharing" tab.
Click on "Share" and add the SQL service account with "Read/write" rights. Finally, copy the directory's Network Path, which should look like this: \\NameVM\ShareFolderBack on our "Server1" VM, in the "Failover Cluster Manager", execute the powershell command :
Set-ClusterQuorum -NodeAndFileShareMajority "\\NameVM\ShareFolder"Or, right-click on the cluster, then "More Actions" and "Configure Cluster Quorum Settings".In the "Select Quorum Configuration Option" section, select "Select the Quorum Witness", then click on "Next".
In the "Select Quorum Witness" section, select "Configure a file share witness", then click "Next".
In the "Configure File Share Witness" section, paste the network path of the shared directory, then click "Next".
Click on "Next" until you reach "Finish".
Good ! You have successfully install and configure a Windows Failover Cluster.
Conclusion
You now know how to install and configure a Windows Failover Cluster and you have a two-node cluster with one SQL Server instance on each node. In the event of an incident, failover is automatic. In the last section, we'll look at how to create an availability group on SQL Server.
