We have an existing physical SQL database cluster that needs a hardware refresh, but with a new virtual infrastructure we decided to place the new cluster on VMs instead. The application requirements state that its database cannot be accessed directly from multiple hosts, but still must be highly available.
To accomplish this, we will first create a 2-node Failover Cluster and use a file share witness for the quorum vote. Next, we’ll install SQL Standard on the nodes, back up the existing database, and restore it on both cluster nodes. We’ll then create replicas of the two database copies and finally configure an Availability Group for the database.
These scripts assume Server/SQL 2022. If you’re using SQL 2019 or 2016, you’ll need to modify or manually install SQL, the latest Cumulative Update that’s supported by the application, and Reporting Services. It will not work on versions prior to 2016.
Before getting into the script, a little prep is involved:
-
Create 2 virtual machines with Windows Server 2022
- Use separate VHDX files for the hard drives. This is so they can be placed on different physical storage.
- Create at least two NICs on each VM, and note the name of the second one (should be Ethernet 1 or Ethernet1). If you’re using Dave’s BigDemo script to create the cluster, we’re only concerned about the Heartbeat network for this guide.
- Download SQL Standard 2022
- Create a shared folder that will be accessible from both VMs. This has to be an SMB2 share. I just created a folder on my Domain Controller called FSW$ and gave full control of the share and NFTS permissions to both VMs, and eventually the Cluster Named Object as well.
To populate the script, there are some variables that need to be specified. All these are at the top, so we won’t need to change anything in the scripts later on.
# Required information
$nodes = @("SQLVM1", "SQLVM2")
$primary = "SQLVM1"
$secondary = "SQLVM2"
$domain = "corp.checkyourlogs.com"
$nicname = "Ethernet 1"
$clustername = "SQLVMCLU01"
$witness = "\\DC1\FSW$"
$SqlMemMin = 8192
$SqlMemMax = 12288
$SQLsource = "C:\Temp\SQL2022Std"
$folderpath = "C:\Temp"
$backuplocation = "\\FS1\SQL_Backup$"
$database = "DatabaseName"
$instance = "MSSQLSERVER"
$listener = "SQLAG"
$listeneraddress = "10.100.10.103/255.255.255.0"
$staticIPAddress = "10.100.10.100"
With our variables in place, we will run the following lines which will get the VMs ready to be clustered:
#Installs Failover Clustering and Configures Networking for the AG Cluster
Write-host "Installing Failover Clustering..."
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
Install-WindowsFeature -Name File-Services, Failover-Clustering -IncludeManagementTools
}
}
Write-Host "done!" -ForegroundColor Green
#Configuring Additional Network Adapter for AG and HB
Write-host "Renaming Hearbeat NICs on Cluster Nodes..."
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
Rename-NetAdapter -Name '$nicname' -NewName 'SQLAG'}
}
Write-Host "done!" -ForegroundColor Green
#Restarts VM before Cluster Configuration
Write-host "Restarting Cluster nodes..."
Restart-Computer -ComputerName $nodes
Write-Host "done!" -ForegroundColor Green
Next step is to create the cluster:
#Configuring Cluster Write-host "Configuring Windows Server Failover Cluster..." New-Cluster -Name $clustername -Node $nodes -NoStorage -StaticAddress $staticIPAddress Write-Host "done!" -ForegroundColor Green #Configuring File Share Witness for Quorum Set-ClusterQuorum -FileShareWitness $witness
We will also need to go into Active Directory and delegate permissions to the cluster named object for the OU that it’s in, as well as RW access to the FSW$ share. The steps for this can be found at https://learn.microsoft.com/en-us/windows-server/failover-clustering/prestage-cluster-adds#step-3-grant-the-cno-permissions-to-the-ou-or-prestage-vcos-for-clustered-roles.
With the cluster created, we now need to prepare the VMs for SQL, which includes a host of firewall rules:
#Configuring Firewall settings for SQL
Write-host "Configuring SQL Server 2019 Firewall settings..."
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
#Enabling SQL Server Ports
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName "SQL Admin Connection" -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Database Management" -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Service Broker" -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName "SQL Debugger/RPC" -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow
#Enabling SQL AG Ports
New-NetFirewallRule -DisplayName "SQL AG Endpoint" -Direction Inbound –Protocol TCP –LocalPort 16333 -Action allow
New-NetFirewallRule -DisplayName "SQL AG Listener" -Direction Inbound –Protocol TCP –LocalPort 5022 -Action allow
#Enabling SQL Analysis Ports
New-NetFirewallRule -DisplayName "SQL Analysis Services" -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow
New-NetFirewallRule -DisplayName "SQL Browser" -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow
#Enabling related Applications
New-NetFirewallRule -DisplayName "HTTP" -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow
New-NetFirewallRule -DisplayName "SQL Server Browse Button Service" -Direction Inbound –Protocol UDP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName "SSL" -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow
#Enabling Windows Firewall
Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True
}
}
Write-Host "done!" -ForegroundColor Green
Now we’re ready to install SQL. If you have a CONFIGURATIONFILE.ini already created for the installation, and have it placed in C:\Temp, the following will automatically install SQL on both nodes. If not, manually install SQL the first time and copy the .ini file it creates to the 2nd VM. This will ensure that the two installations are exact.
#Installing SQL
Write-host "Installing SQL 2022 Std on $nodes..."
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
Try
{
if (Test-Path $SQLsource){
Write-host "about to install SQL Server 2022..." -nonewline
$fileExe =  "$SQLsource\setup.exe"
$CONFIGURATIONFILE = "$folderpath\ConfigurationFile.ini"
$fileExe  /CONFIGURATIONFILE=$CONFIGURATIONFILE
Write-Host "done!" -ForegroundColor Green
} else {
Write-host "Could not find the media for SQL Server 2019..."
break
}}
catch
{Write-host "Something went wrong with the installation of SQL Server 2019, aborting."
break}
}
}
Providing you had all the rights to get this far, the cluster should now be functional, and SQL installed on both nodes. Though also possible from PowerShell, I prefer to launch Failover Cluster Manager and run the Validate Cluster activity in the UI. Viewing the report will tell you if there are any issues with the cluster configuration. The next post will migrate the database from the production cluster and create the availability.
