Find Version Mismatch

Question

Find Version Mismatch

Sreejith!

SSCrazy

Points: 2401
More actions
July 26, 2023 at 4:41 am

#4244669

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135495 More actions · Answer 1

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

Sreejith! SSCrazy Points: 2401 More actions · Answer 2

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.

Find Version Mismatch

Cookies on SQLServerCentral