July 26, 2023 at 4:41 am
Hello, I'm looking for a way to generate a cross-sever report [using TSQL(without LinkedServers) or Powershell ] to show SQL Server AlwaysOn replicas that are not in the same version. Can someone please guide me? Thanks in advance
July 26, 2023 at 12:51 pm
How about ...
$VerbosePreference = [system.Management.Automation.ActionPreference]::Continue
$VerbosePreference = [system.Management.Automation.ActionPreference]::SilentlyContinue
Trap {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );
write-Error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-Error $err.Message
};
write-host $('{0} Script failed - check !!' -f (get-date -format 'yyyy-MM-dd HH:mm:ss') ) -BackgroundColor Red -ForegroundColor Black;
# End the script.
break
}
Clear-Host
# om geen last te hebben van de plaats die wordt ingenomen door Write-Progress
1..10 | % { ('*' * $_ ) }
###### Modify your inventory
$ActiveSQLInstances = @(
@{
"AGName" = "AG1"
"SQLInstance" = "SQLInstance1"
"DbName" = "DbNameA"
},
@{
"AGName" = "AG1"
"SQLInstance" = "SQLInstance2"
"DbName" = "DbNameA"
}
)
$TsStart = Get-Date
Write-Host $('{0} Script Start' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ) -BackgroundColor Yellow -ForegroundColor Black ;
$ColumnNamesStr = ''
$AllResults = @()
try {
$xCtr = 0 ;
$Setcount = $ActiveSQLInstances.count;
foreach ( $inst in $ActiveSQLInstances ){
$xCtr ++ ;
$pct = $xCtr * 100 / $Setcount ;
Write-Progress -Activity $( 'Progressing [{0}] - {1} ' -f $inst.AGName, $inst.DbName ) -Status $('{0} / {1}' -f $xCtr, $Setcount ) -PercentComplete $pct -SourceId 1 ;
$TsStartSrv = Get-Date
try {
$db = Get-SqlDatabase -ServerInstance $inst.SQLInstance -Name $inst.DbName -ErrorAction Stop ;
$Results = 1 | Select @{n='SQLVersion';e={ $db.parent.Version.ToString() }} , @{n='DbName';e={$db.Name}};
if ( $ColumnNamesStr -eq '' ) {
$ColumnNamesStr = $('AGname,SQLVersion,DbName') -split ','
}
$AllResults += $Results | Select @{n='SQLInstance';e={$inst.SQLInstance}}, *
$db.Parent.ConnectionContext.Disconnect();
}
catch{
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('{0} Trapped error at line [{1}] ( {2} / {3} ) : [{4}]' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss'), $ErrorLineNumber, $inst.SQLInstance, $err.Message );
}
}
}
catch {
# Handle the error
$err = $_.Exception
#Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
$ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
write-warning $('{0} Trapped error at line [{1}] : [{2}]' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ,$ErrorLineNumber, $err.Message );
write-Error $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-error $err.Message
};
}
$TargetFile = $('{0}\SQLServer_Versions_{1}.csv' -f $env:TEMP, ( get-date -Format 'yyyyMMdd_HHmmss'))
if ( $AllResults.Count -gt 0 ) {
if ( Test-Path $TargetFile -PathType Leaf ) {
remove-item $TargetFile -Force
}
$AllResults | Select -Property $ColumnNamesStr | export-csv -Path $TargetFile -NoClobber -NoTypeInformation -Delimiter ';' ;
& "$targetFile"
}
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 27, 2023 at 7:47 am
Hi Johan.
Thanks a lot for the prompt response.
I will try to tweak it to match the requirement of taking a list of server names as a parameter(~100) & check if AlwaysOn is enabled- if yes, then compare versions & send an email containing servers with only mismatches.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply