Compare SQL Failover Cluster Instance SQL and OS Node Versions using PowerShell
It is a common check to see if all of the nodes in a cluster are equally patched. The reason behind this is because in order to ensure stable operations and a consistent experience, it is best practice to have all nodes of a failover cluster instance (FCI) at the same build, including the SQL Server version, Windows Server version, and the OS hot fixes installed.
In order to get this information quickly and efficiently, my recommendation would be to use PowerShell and WMI (and a dash of SMO) in order to accomplish this task. There are a few benefits to this route:
1. No need to failover anything
2. It is scripted, and therefore has the ability to be reused for future requirements
3. It takes minimal time to complete this action
Diving right into the script, it includes two different functions (one to compare the SQL Server versions, and the other to compare the OS versions and hotfixes), and then a little looping and logic directly in the script.
The script starts with a param block specifying a required single parameter:
## $SqlServerName : the clustered instance name as input ##
param (
[Parameter(Mandatory = $true, Position = 0)]
[String]$SqlServerName
)
This mandatory parameter is simply the SQL Server instance name that you are looking to test for the cluster.
The first function is created in order to compare the SQL Server versions across two nodes:
Function Compare-SqlVersion {
param ( [Parameter(Mandatory = $true, Position = 0)]
[String]$Server1,
[Parameter(Mandatory = $true, Position = 1)]
[String]$Server2,
[Parameter(Mandatory = $true, Position = 2)]
[String]$SqlServerName,
[Parameter(Mandatory = $true, Position = 3)]
[Int32]$SqlVersion,
[Parameter(Mandatory = $true, Position = 4)]
[String]$SqlServiceName
)
# get the version of the first server
$Server1Version = Get-WmiObject -ComputerName $Server1 -Class SqlServiceAdvancedProperty -Namespace "root\Microsoft\SqlServer\ComputerManagement$SqlVersion" |
Where-Object {$_.SqlServiceType -eq 1 -and $_.PropertyName -eq "FILEVERSION" -and $_.ServiceName -eq $SqlServiceName} |
Select-Object -ExpandProperty PropertyStrValue
# get the version of the second server
$Server2Version = Get-WmiObject -ComputerName $Server2 -Class SqlServiceAdvancedProperty -Namespace "root\Microsoft\SqlServer\ComputerManagement$SqlVersion" |
Where-Object {$_.SqlServiceType -eq 1 -and $_.PropertyName -eq "FILEVERSION" -and $_.ServiceName -eq $SqlServiceName} |
Select-Object -ExpandProperty PropertyStrValue
# if either are null, then one server isn't participating in the given FCI
# so exist and notify the script that SQL Server for this FCI doesn't live here
if (!$Server1Version -or !$Server2Version) {
return "NonexistentSql"
}
# otherwise compare the two versions
Compare-Object $Server1Version $Server2Version |
Select-Object @{Name = "InstanceName"; Expression = {$SqlServerName}},
@{Name = "OwningNode"; Expression = {
if ($_.SideIndicator -eq "<=") {
$Server1
}
elseif ($_.SideIndicator -eq "=>") {
$Server2
}
}},
@{Name = "Issue"; Expression = {"SqlVersionMismatch"}},
@{Name = "Discrepancy"; Expression = {$_.InputObject}}
}
As per the comments in the script, all this function does is compare the SQL Server version on two servers and returns that result if they differ. If either of the servers doesn’t house a node of the FCI, then there is no need to compare these two servers and return with a status of “NonexistentSql” for conditional logic later on in the script.
The second function is to compare the Operating System side, both with OS version and hotfixes installed:
Function Compare-OsVersion {
param ([Parameter(Mandatory = $true, Position = 0)]
[String]$Server1,
[Parameter(Mandatory = $true, Position = 1)]
[String]$Server2,
[Parameter(Mandatory = $true, Position = 2)]
[String]$SqlServerName
)
# compare the OS versions
Compare-Object (Get-WmiObject -ComputerName $Server1 -Class Win32_OperatingSystem | Select-Object -ExpandProperty Version) (Get-WmiObject -ComputerName $Server2 -Class Win32_OperatingSystem | Select-Object -ExpandProperty Version) |
Select-Object @{Name = "InstanceName"; Expression = {$SqlServerName}},
@{Name = "OwningNode"; Expression = {
if ($_.SideIndicator -eq "<=") {
$Server1
}
elseif ($_.SideIndicator -eq "=>") {
$Server2
}
}},
@{Name = "Issue"; Expression = {"OsVersionMismatch"}},
@{Name = "Discrepancy"; Expression = {$_.InputObject}}
# get the first server's hotfixes installed
$Server1HotFix = Get-HotFix -ComputerName $Server1 |
Select-Object -ExpandProperty HotFixID
# get the second server's hotfixes installed
$Server2HotFix = Get-HotFix -ComputerName $Server2 |
Select-Object -ExpandProperty HotFixID
# if the first server has no hotfixes, set to empty string
if (!$Server1HotFix) {
$Server1HotFix = ""
}
# if the second server has no hotfixes, set to empty string
if (!$Server2HotFix) {
$Server2HotFix = ""
}
# compare the hotfixes installed on each server
Compare-Object $Server1HotFix $Server2HotFix |
Select-Object @{Name = "InstanceName"; Expression = {$SqlServerName}},
@{Name = "OwningNode"; Expression = {
if ($_.SideIndicator -eq "<=") {
$Server2
}
elseif ($_.SideIndicator -eq "=>") {
$Server1
}
}},
@{Name = "Issue"; Expression = {"OsHotfixMissing"}},
@{Name = "Discrepancy"; Expression = {$_.InputObject}} |
Where-Object {$_.Discrepancy -ne ""}
}
As the comments explain, this function first compares the OS version, and then subsequently compares the hotfixes installed on the provided two servers.
Now comes to the looping and the logic to get our final result set of disparate versioning on all of the nodes in the cluster. First we need to utilize SMO in order to connect to the instance. We also need to get the service name for the given FCI: If is a default instance it’ll be “MSSQLSERVER”, and if it is a named instance then it’ll be in the format of “MSSQL$InstanceName”.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |
Out-Null
# connect to the SQL Server instance to be tested
$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerName)
# in order to get the service name, we will do some logic to get
# the service name (default: MSSQLSERVER, named: MSSQL$InstanceName)
if ($SqlServer.InstanceName -eq "") {
$SqlServiceName = "MSSQLSERVER"
}
else {
$SqlServiceName = 'MSSQL$' + $SqlServer.InstanceName
}
Now we need to get an actual listing of all the nodes that the given cluster contains. We will use this node list in order to loop through each of the servers. With that list of node names, we will then utilize our above two worker functions to compare the SQL Server version, and the OS version and hotfixes installed. And you will see below that there are nested FOR loops. I didn’t want to be constrained to a two-node cluster, and the nested FOR loops allow me to compare each server to every other server, regardless of how many nodes the cluster has.
# ensure first that the instance is clustered
if ($SqlServer.IsClustered) {
# retrieve the cluster node names directly from SQL Server
$SqlNodesStats = $SqlServer.Databases["master"].ExecuteWithResults("select NodeName from sys.dm_os_cluster_nodes;").Tables[0] |
Select-Object -ExpandProperty NodeName
# save the results in the an array
# loop through each server, for each server
# this will compare all servers to each other
$IssuesResults = for ($i = 0; $i -lt ($SqlNodesStats.Count - 1); $i++) {
for ($j = 0; $j -lt $SqlNodesStats.Count; $j++) {
if ($i -ne $j) {
# compare the SQL Server version
$SqlVersionReturn = Compare-SqlVersion -Server1 $SqlNodesStats[$i] -Server2 $SqlNodesStats[$j] -SqlServerName $SqlServerName -SqlVersion $SqlServer.VersionMajor -SqlServiceName $SqlServiceName
# check to make sure that the server houses the FCI node
if ($SqlVersionReturn -ne "NonexistentSql") {
# if the server has a SQL Server FCI node retrieve the SQL and OS versions, and hotfixes
$SqlVersionReturn
Compare-OsVersion -Server1 $SqlNodesStats[$i] -Server2 $SqlNodesStats[$j] -SqlServerName $SqlServerName
}
}
}
}
# sort the results and remove duplicates
$IssuesResults |
Sort-Object -Property InstanceName, Issue, OwningNode, Discrepancy -Unique
if (!$IssuesResults) {
Write-Host "No identified version/hotfix mismatches"
}
}
else {
Write-Host "$($SqlServer.Name) is not a clustered instance"
}
The last statement is used to sort the results and remove duplicates (since we’re comparing each server to every other server, there is a high chance for duplicate version/hotfix mismatch). On my test cluster (with intentionally mismatched versions), my output looks like the following.
Here today I’ve illustrated one way using PowerShell to verify if all of the cluster nodes are utilizing the same version. This method gives you added flexibility and the opportunity for code reusability. Enjoy!
Download : Compare SQL and OS Failover Cluster Node Versions
Thomas Stringer - SQL Server Premier Field Engineer
Twitter: @SQLife