In Part 1 of this guide, we created a 2-node VM cluster on Windows Server 2022 and installed SQL Standard 2022 on both nodes. Now that SQL is installed, we will proceed with getting it ready, migrating the databases and configuring availability on the SQL side.
If you already had your cluster created, or no longer have the PowerShell ISE session from the previous post, you’ll need to specify some variables to complete the process below. The ones from the following post that will still be needed are:
# Required information $primary = "SQLVM1" $secondary = "SQLVM2" $domain = "corp.checkyourlogs.com" $clustername = "SQLVMCLU01" $SqlMemMin = 8192 $SqlMemMax = 12288 $folderpath = "C:\Temp" $backuplocation = "\\FS1\SQL_Backup$" $database = "DatabaseName" $instance = "MSSQLSERVER" $listener = "SQLAG" $listeneraddress = "10.100.10.103/255.255.255.0"
Let’s get to work! First is to install Cumulative Update 1, Management Studio, and Reporting Services. If you don’t already have these, the script will download them. If you’re not using 2022, the only part of this that you should run is for Management Studio. The rest you need to take care of manually, or update the URLs and filenames below.
#Applying Cumulative Update 1
Write-host "Applying SQL 2022 CU 1 on $nodes..."
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
$filepath="$folderpath\SQLServer2022-KB5023127-x64.exe"
if (!(Test-Path $filepath)){
Write-host "Downloading SQL Server 2022 CU1..." -nonewline
$URL = "https://download.microsoft.com/download/9/6/8/96819b0c-c8fb-4b44-91b5-c97015bbda9f/SQLServer2022-KB5023127-x64.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
else {
Write-host "found the SQL Server 2022 CU1 Installer, no need to download it..."
}
# start the SQL Server 2022 CU1 installer
Write-host "about to install SQL Server 2022 CU1..." -nonewline
$Parms = " /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"
$Prms = $Parms.Split(" ")
"$filepath" $Prms | Out-Null
}
}
Write-Host "done!" -ForegroundColor Green
#Installing SQL Server Management Studio
Write-host "Installing SQL Server Management Studio on $nodes..."
$filepath="$folderpath\SSMS-Setup-ENU.exe"
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
if (!(Test-Path $filepath)){
Write-host "Downloading SQL Server 2017 SSMS..." -nonewline
$URL = "https://go.microsoft.com/fwlink/?linkid=870039"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
else {
Write-host "found the SQL SSMS Installer, no need to download it..."
}
# start the SQL SSMS installer
Write-host "about to install SQL Server Management Studio..." -nonewline
$Parms = " /Install /Quiet /Norestart /Logs SQLServerSSMSlog.txt"
$Prms = $Parms.Split(" ")
"$filepath" $Prms | Out-Null
}
}
Write-Host "done!" -ForegroundColor Green
#Installing SQL Server Reporting Services
Write-host "Installing SQL Server Reporting Services on $nodes..."
$filepath="$folderpath\SQLServerReportingServices.exe"
Invoke-Command -ComputerName $primary -ScriptBlock {
if (!(Test-Path $filepath)){
Write-host "Downloading SQL Server 2022 Reporting Services..." -nonewline
$URL = "https://download.microsoft.com/download/8/3/2/832616ff-af64-42b5-a0b1-5eb07f71dec9/SQLServerReportingServices.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
else {
Write-host "found the SQL RS Installer, no need to download it..."
}
# start the SQL RS installer
Write-host "about to install SQL Server 2019 Reporting Services..." -nonewline
$Parms = "  /IAcceptLicenseTerms True /Quiet /Norestart /Log SQLServerReportingServiceslog.txt"
$Prms = $Parms.Split(" ")
"$filepath" $Prms | Out-Null
}
Write-Host "done!" -ForegroundColor Green
Before we can migrate the database, we need to check the application database requirements and set the Max/Min memory settings for SQL Server:
# Configuring SQL memory
Write-host "Configuring SQL memory on $nodes..." -nonewline
ForEach ($node in $nodes) {
Invoke-Command -ComputerName $node -ScriptBlock {
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$SQLMemory = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ("(local)")
$SQLMemory.Configuration.MinServerMemory.ConfigValue = $SQLMemMin
$SQLMemory.Configuration.MaxServerMemory.ConfigValue = $SQLMemMax
$SQLMemory.Configuration.Alter()
}
}
Write-Host "done!" -ForegroundColor Green
Write-host ""
We’re now ready to migrate the database from the existing cluster to the new one. Log into one of the production SQL servers and run the following command to back up the database.
# Backing up database from production cluster. Write-host "Backing up database from production Cluster..." -nonewline Backup-SqlDatabase -Database "$database" -BackupFile "$backuplocation\$database.bak" -ServerInstance "$primary\$instance" Backup-SqlDatabase -Database "$database" -BackupFile "$backuplocation\$database.log" -ServerInstance "$primary\$instance" -BackupAction Log Write-Host "done!" -ForegroundColor Green
Now we’ll import the database on the cluster nodes. The restore to the 2nd node has to be configured in NORECOVERY mode, otherwise we cannot build the availability group.
# Restoring the database and log from old cluster to primary Write-host "Importing database from production backup..." -nonewline Restore-SqlDatabase -Database "$database" -BackupFile "$backuplocation\$database.bak" -ServerInstance "$primary\$instance" Restore-SqlDatabase -Database "$database" -BackupFile "$backuplocation\$database.log" -ServerInstance "$primary\$instance" -RestoreAction Log Write-Host "done!" -ForegroundColor Green # Restoring the database and log on the secondary using NO RECOVERY Write-host "Restoring database to secondary replica server..." -nonewline Restore-SqlDatabase -Database "$database" -BackupFile "$backuplocation\$database.bak" -ServerInstance "$secondary\$instance" -NoRecovery Restore-SqlDatabase -Database "$database" -BackupFile "$backuplocation\$database.log" -ServerInstance "$secondary\$instance" -RestoreAction Log -NoRecovery
With the database restored on both servers, our next step is to create in-memory representations of the replica data and configure the Availability Group.
# Creating an in-memory representation of the primary replica. $primaryReplica = New-SqlAvailabilityReplica -Name "$primary\$instance" -EndpointURL "TCP://$PRIMARY.$domain:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 12 -AsTemplate # Creating an in-memory representation of the secondary replica. $secondaryReplica = New-SqlAvailabilityReplica -Name "$secondary\$instance" -EndpointURL "TCP://$SECONDARY.$domain:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 12 -AsTemplate # Creating the availability group New-SqlAvailabilityGroup -Name "SQLVMAG" -Path "SQLSERVER:\SQL\$primary\$instance" -AvailabilityReplica @($primaryReplica,$secondaryReplica) -Database "$database" # Joining the secondary replica to the availability group. Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$secondary\$instance" -Name "SQLVMAG" # Joining the secondary database to the availability group. Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$secondary\$instance\AvailabilityGroups\SQLVMAG" -Database "$database"
The final step in this process is to create the Availability Group Listener.
# Creating the Availability Group Listener New-SqlAvailabilityGroupListener -Name "$listener" -Path "SQLSERVER:\SQL\$primary\$instance\AvailabilityGroups\$listener" -StaticIp "$listeneraddress"
Providing you had all the rights to get this far, the cluster should now be functional and the database availability in operation. 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. Launch SQL Server Management Studio to examine the state of the Always-On Availability Group. Just because the steps worked successfully, there could still be an issue with the Cluster or the Listener. Hope this helps!
