Prepare Failover Cluster for AG configuration

In this blog I will show you some important powershell commands to prepare a Failover Cluster for AG configuration. Please keep in mind, this is a test environment and I will not talk about firewall rules.

Our objective is to configure a failover cluster with two nodes for a AlwaysOn Availability Group (AG) configuration.

As this is for my tests environment and we need an Active Directory domain name (AD), so I will give you a little guide to install and configure an AD as well.

AD Configuration

• Install the Active Directory domain service feature including tools
  Install-WindowsFeature -name AD-Domain-Services -IncludeManagementTools
• Promote Server as Domain Controller
  You will be prompted to insert the safe mode Administrator password.
  Install-ADDSForest -CreateDnsDelegation:$false -DatabasePath "C:\Windows\NTDS" -DomainMode "WinThreshold" -DomainName "mydomain.internal" -DomainNetbiosName "MYDOMAIN" -ForestMode "WinThreshold" -InstallDns:$true -LogPath "C:\Windows\NTDS" -NoRebootOnCompletion:$false -SysvolPath "C:\Windows\SYSVOL" -Force:$true
• Create a new Organizational Unit (new folder) under your domain in AD
  This is not mandatory but I prefer to have everything in one place at my AD so, I create this OU.
  New-ADOrganizationalUnit -Name "sqlserver" -Path "DC=MYDOMAIN,DC=INTERNAL"
• Create users
  – sqlengine (manage SQL server engine)
  – sqlagent (manage SQL server agent)
  – sqldba (manage SQL server instance)
• Create group for our DBAs admin
  – AdminDBAs –> add sqldba to this group
• Prestage cluster computer objects(Microsoft Documentation)
  – Create CNO (Automate CNOs and VCOs for SQL Server AAG)
  – security: full control to the AdminDBAs group
  – Create DNS Entery (new host A or AAAA)
  – IP address: 192.168.56.10
  – name: same name as your CNO
  – security: read and write to the CNO

Nodes configuration

We have 2 nodes wintest01 and wintest02. We need to add these two nodes to our domain previously created.

First you need to confirm your communication (connection) between your servers (all three).
Test-NetConnection -ComputerName 192.168.56.6

Then add your nodes to the domain
Add-Computer -DomainName MYDOMAIN -Restart

Here are some important points:

• Check your firewall rules
• The default gateway for your IPv4 address is AD server IP address

Failover Cluster configuration

Install the failover module with all tools on each node (restart is mundatory)
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

Validate failover cluster hardware and settings. This will generate a report in HTML format.
Test-Cluster -node WINTEST01,WINTEST02 -Ignore 'Storage', 'List Fibre Channel Host Bus Adapters', 'List iSCSI Host Bus Adapters', 'List SAS Host Bus Adapters'"

In my case, I have a warning about network, because for failover cluster it’s recommended to have at least two network cards. I have only one, I will ignore it as this setup is only for test.

Once your test is successful, you can create your Failover Cluster:
New-Cluster -Name CLUSTERTEST -Node WINTEST01, WINTEST02 -StaticAddress 192.168.56.10 -NoStorage

re-run the test command
Test-Cluster -node WINTEST01,WINTEST02 -Ignore 'Storage', 'List Fibre Channel Host Bus Adapters', 'List iSCSI Host Bus Adapters', 'List SAS Host Bus Adapters'"

You will notice that the Quorum is missing. In our case, I will put the Quorum file in AD server. It’s important to understand, this is not a best practice. In prod configuration it’s recommended to put this file in a file share server.

Set-ClusterQuorum -Cluster CLUSTERTEST -FileShareWitness "\WIN-ASA-AD\Quorum_clustertest"

Here are some important points:

• Create your CNO and DNS entery before (Prestage cluster computer objects)
• Make sure your CNO is disabled. This CNO will be activated during the creation of your failover cluster
• Make sure you have -NoStorage option. This will ignore all shared storage during the cluster creation. You can find more information at the following blog: SQL Server AlwaysOn node “Add all eligible storage to the cluster”

Here we go, now you have your Failover Cluster 😀