As I’ve been running a lot of queries against multiple servers of late and doing the majority of the work through PowerShell, I’ve constantly been running into issues of the query only working for Version X and above! This gets rather annoying and covers my screen in the nasty red.. Rather than constantly cleaning up my lists of servers before each query I thought it would be nice to have a function that would take a list of SQL boxes check that the server meets the criteria its set, and pipe out the servers that are greater than or equal to that version.

Hopefully others find it as useful as I have.

<#

.FUNCTION
 
Get-OnlySQLVersion
 
.SYNOPSIS
 
Returns list of Servers from an orginal list that are above or equal to the 
verison you enter
 
.PARAMETER Servers
 
The name of the instance(s) you wish to check connections to
 
.PARAMETER Version
 
Version of SQL Server you want to query against
 
.EXAMPLE
 
Get-OnlySQLVersion -Servers $servers -Version 2008
 
.EXAMPLE
 
Get-OnlySQLVersion -Servers "DEV-SQL1", "DEV-SQL2008" -Version 2012
 
.EXAMPLE
 
$importservers = get-content C:\Temp\test.txt
Get-OnlySQLVersion -Servers -Version 2005
 
.Release
 
1.0 2014/05/29 Stephen First Release (no checks for 2008r2, or service packs)
 
#>
FUNCTION Get-OnlySQLVersion {
    [CmdletBinding()]
    Param 
        (
           # List of server(s) to run against
           [Parameter(Mandatory=$true)]
           [string[]]
           $Servers
           ,
           # Define SQL Version you want to find 
           [ValidateSet("2005","2005SP1","2005SP2","2005SP3","2005SP4","2008","2008SP1","2008SP2","2008SP3","2008r2","2008r2SP1","2008r2SP2","2012", "2012SP1","2014"
           )]
           [string]
           $Version
        )
BEGIN {
# SQL Versions Hash Table
$SQLVersions = @{
                    "2005"      = "9.00.1399.06"
                    "2005SP1"   = "9.00.2047"
                    "2005SP2"   = "9.00.3042"
                    "2005SP3"   = "9.00.4035"
                    "2005SP4"   = "9.00.5000"
                    "2008"      = "10.00.1600.22"
                    "2008SP1"   = "10.00.2531"
                    "2008SP2"   = "10.00.4000"
                    "2008SP3"   = "10.00.5500"
                    "2008r2"    = "10.50.1600.1"
                    "2008r2SP1" = "10.50.2500"
                    "2008r2SP2" = "10.50.4000"
                    "2012"      = "11.00.2100.60"
                    "2012SP1"   = "11.00.3000"
                    "2014"      = "12.00.2000.8"
                }
}
PROCESS {

        foreach ($s in $servers)
            {
                $v = Get-SqlVersion $s
                IF ($v.Version -ge $SQLVersions.Get_Item($version)) 
                    {
                        Write-Verbose "$s"
                        $obj = New-Object –typename PSObject
                        $obj | Add-Member –membertype NoteProperty –name ComputerName –value ($s)
                        Write-Output $obj
                    }
                ELSE
                    {
                        Write-Verbose "$s : This is not high enough version to run"
                    } 
            } ## end foreach
    } ## end process
} ## end function
Advertisements