In a effort to monitor the synchronization status of every database in our SQL Always On cluster, we came up the the following PS script. Worked great in NPM v11. However in NPM V12 the message ouput is returning N/A and the Statistic output is returning NaN. When running the script within Powershell ISE, I get the correct output.
What is different with v12 that prevents me form getting the correct output?
Import-Module SQLPS -WarningAction SilentlyContinue
$SyncStatus = dir \SQL\SQLServer\GENERAL\AvailabilityGroups\General\DatabaseReplicaStates | Where {$_.SynchronizationState -NotLike "Sync*"}
$SyncContainer = @()
$counter = 0
ForEach ($value in $SyncStatus) {
$found = $false
ForEach ($i in $SyncContainer) {
if ($value.AvailabilityDatabaseName -eq $i.AvailabilityDatabaseName)
{
$found = $true
}
}
if (-Not $found) {
$SyncObject = New-Object -TypeName PSObject
$SyncObject | Add-Member -Type NoteProperty -Name AvailabilityDatabaseName -Value $value.AvailabilityDatabaseName -Force
$SyncObject | Add-Member -Type NoteProperty -Name SynchronizationState -Value $value.SynchronizationState -Force
$SyncContainer += $SyncObject
}
}
if ($SyncStatus.Count -eq 0) {
Write-Host Message.$counter : "All databases are synchronized"
Write-Host Statistic.$counter : "0"
exit 0
} else {
ForEach ($Entry in $SyncContainer) {
$counter++
Write-Output "Message.$counter :" $Entry.AvailabilityDatabaseName
Write-Output "Statistic.$counter : " $SyncContainer.Count
If ($counter -ge 10)
{
exit 0
}
}
}
