Running SQL Scripts Against Multiple Servers Using PowerShell

Update: From time to time I get questions on this blog post and since I have wrote it dbatools has come out with method do this and if you have not checked out dbatools you should.  Click here to get started with dbatools and here (Invoke-DbaQuery) to see the function you should use to replace this blog post.  They also have a function to replace the Get-CMSHosts function mentioned below Get-DbaRegServer, it doesn’t support text files but is worth considering as a replacement.

In this post I’m going to share a function I use run scripts against multiple instances of SQL servers and run the data into a GridView. I use this mainly for a replacement of the CMS feature of running against a folder and to put the output to a GridView that I can sort and filter any way I want which you can’t do in CMS.

The first function Get-CmsHosts was written by my colleague Mark Wilkinson (m82labs.com) to pull a pattern of hosts from your CMS server or your can pass an -InstanceList as a file or comma separate list.

Then I use PSSessions to run any query I specify and output to a GridView.

Get-CmsHosts -InstanceList 'c:\temp\servers.txt' | % { New-PSSession -ComputerName $_ | out-null}
$sessions = Get-PSSession

$scriptblock = {
$query = @"
SELECT @@VERSION
"@
Invoke-Sqlcmd -Query $query
}

Invoke-Command -Session $($sessions | ? { $_.State -eq 'Opened' }) -ScriptBlock $scriptblock | Select * -ExcludeProperty RunspaceId | Out-GridView
$sessions | Remove-PSSession

function Get-CmsHosts() {
<# 
.SYNOPSIS This function queries a CMS instance and returns a list of instances Written by Mark Wilkinson @m82labs on Twitter, website m82labs.com 

.PARAMETER cmdHost 

.PARAMETER searchPattern !!NOT INJECTION SAFE!! this parameter simply gets inserted into a wildcard query on the list of available instances on the CMS. This parameter accepts a pipe delimeter list of patterns, allowing you to match instance names on multiple conditions. 

.PARAMETER version The SQL Server version (build number) that should be running on the returned instances. !! This will query each instance to get the build version, use in conjuction with searchPattern !! 

.EXAMPLE This will return all instances that start with 'Pattern' and are on SQL 2016 RTM 
Get-CMSHosts -searchPattern 'Pattern' -version '13.0.1605.1' 

.EXAMPLE 
This can be used in a 'ForEach': ForEach ( $instance in Get-CMSHosts -searchPattern 'Pattern' -version '13.0.1605.1' ) { #Do some stuff } 
#>
    param(
        [CmdletBinding()]
        [string]$cmsHost = '',
        [string]$searchPattern = '',
        [string]$instanceList,
        [string]$version
    )
    
    If ( $instanceList -and (Test-Path -Path $instanceList) ) {
        $results = Get-Content -Path $instanceList
    } Else {
        $pattern = ''

        For ( $pat_i = 0; $pat_i -lt ($searchPattern.Split('|')).Count; $pat_i++ ) {
            If ( $pat_i -gt 0 ) { $pattern += " OR " }
            $pattern += "server_name LIKE '$($searchPattern.Split('|')[$pat_i])%'"            
        }

        [string]$query_get_servers = "SELECT DISTINCT server_name FROM msdb.dbo.sysmanagement_shared_registered_servers WHERE {{searchPattern}}"
        $results = (Invoke-SqlCmd -query $query_get_servers.Replace('{{searchPattern}}',$pattern) -ServerInstance $cmsHost).server_name
    }

    If ( $version ) {
        ForEach ( $instance In $results ) {
            Try {
                If ( (Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('productversion') AS v" -ServerInstance $instance -ConnectionTimeout 1 -QueryTimeout 1 -ErrorAction Stop).v -ne $version) {
                    $results = $results | Where-Object { $_ -notmatch $instance }
                }
            } Catch {
                $connect_error += 1
		        Write-Host "failed: $($_.Exception.Message)" -ForegroundColor White -BackgroundColor Red
                $results = $results | Where-Object { $_ -notmatch $instance }
                continue
            }
        }
    }

    If ( $connect_error ) {
        Write-Host " -[$($connect_error) instance(s) skipped due to connection error]- " -ForegroundColor Red -NoNewline
    }

    return $results
}