PowerShell & SQL Server: Run In Parallel – Collect SQL Results With PRINT Output From Across Your SQL Farm – Fast!

If you have been working with PowerShell and SQL Server for a while now, you know that it is not difficult to capture the PRINT statement output as VERBOSE output (which can then be redirected to a variable or file).

You also know that it is not too difficult to run SQL in parallel against multiple instances to speed-up data collection or updates using PowerShell.

There is just one problem, you don’t have them all together to do something right now!

Why capture TSQL PRINT output?

Why? My goal

As an example, you may have a block of SQL that PRINTs out the current privileges in the database that can then be saved off and used as an independent script.

In my case today, I need to collect information on the Service Pack and Cumulative Updates that need to be installed/applied to 200+ SQL Server instances. MS provides the SQL script to identify the right SP and CU to install. Please make sure you download the zip file and unzip the long SQL. I need to run this SQL against the instances to get the info. However, the information returned is completely with PRINT statements!

Problems with capturing PRINT output in PowerShell

Almost all openly available versions of Invoke-SQLCmd* support capturing PRINT statements as VERBOSE output. We usually only use it to troubleshoot most of the time and at other times, we simply turn off verbose or ignore the output.

dbatools Invoke-SQLCmd2 and Invoke-SQLCmd2 by ramblingcookiemonstor both capture PRINT statement output as VERBOSE output but they also mix in the other VERBOSE output from PowerShell code making it difficult to distinguish between the exact PRINT output and the VERBOSE output from the PowerShell function(s). Sometimes, you want just the pure PRINT output and nothing else!

The native Invoke-SQLCmd from the SQLPS or SQLServer module prefixes every output line with “VERBOSE: ” in the output. It is easy to get rid of the string “VERBOSE: ” in the output plus it is most likely already on the system via the older SQLPS or the newer SQLServer module. However, when running in parallel, it produces this error:

The WriteObject and WriteError methods cannot be called from outside the overrides of the BeginProcessing, ProcessRecord, and EndProcessing methods, and they can only be called from within the same thread. Validate that the cmdlet makes these calls correctly, or contact Microsoft Customer Support Services. 

There is a discussion about the above error here. Unfortunately, I cannot customize how I process in parallel (example later).

What should be so easy is after all not so easy!

Version from PowerShell Gallery – Invoke-SQLCmd2

The easiest option is to use the Invoke-SQLCmd2 available on PowerShell Gallery and commenting out all the Write-Verbose statements except the one that corresponds to writing out the PRINT statements.

https://www.powershellgallery.com/packages/Invoke-SqlCmd2/1.6.4

Running in parallel for speed!

The need for speed is critical when you have hundreds of SQL instances like we do. If you tried to even to “SELECT @@Version” from a couple of hundred instances, it can take minutes when run serially. Parallelization is the key. Please refer my post on how to do run things in parallel when it comes not only to SQL Server and queries but also other things you may have in PowerShell (say for Oracle or AD or Windows server management etc).

The solution that I have been using very extensively is something that I have blogged about and I highly recommend it. In fact, this is the post that gets the most hits on my blog:

https://sqljana.wordpress.com/2015/08/18/invoke-async-asynchronous-parallel-processing-for-any-workload-in-powershell/

The functions used and what they do:

  1. Invoke-Sqlcmd2 – Version downloaded from PowerShell Gallery but with all but one Write-Verbose commented out
  2. Get-PrintAndSQLOutput – The function that gets the actual output and PRINT statement output and wraps it all in an object. Also, allows capture from multiple instances with ability to capture errors and run duration
  3. Invoke-Async – Refer the link above. Needed only if you want to process in parallel

The main function that this blog post is about is really Get-PrintAndSQLOutput and how to invoke it in parallel!

Invoke-SQLCmd2 from PowerShell Gallery

As mentioned, this was downloaded from the PowerShell Gallery and the Write-Verbose statements were commented out. You should get it from the source rather than using the version below which only shows the only Write-Verbose line NOT to comment!

if ($PSBoundParameters.Verbose)
            {
                $conn.FireInfoMessageEventOnUserErrors=$false # Shiyang, $true will change the SQL exception to information
                $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
                $conn.add_InfoMessage($handler)
            }

Every other write-verbose should be commented out as shown below:

function Invoke-Sqlcmd2
{

    [CmdletBinding( DefaultParameterSetName='Ins-Que' )]
    [OutputType([System.Management.Automation.PSCustomObject],[System.Data.DataRow],[System.Data.DataTable],[System.Data.DataTableCollection],[System.Data.DataSet])]
    param(
        [Parameter( ParameterSetName='Ins-Que',
                    Position=0,
                    Mandatory=$true,
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false,
                    HelpMessage='SQL Server Instance required...' )]
        [Parameter( ParameterSetName='Ins-Fil',
                    Position=0,
                    Mandatory=$true,
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false,
                    HelpMessage='SQL Server Instance required...' )]
        [Alias( 'Instance', 'Instances', 'ComputerName', 'Server', 'Servers' )]
        [ValidateNotNullOrEmpty()]
        [string[]]
        $ServerInstance,

        [Parameter( Position=1,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false)]
        [string]
        $Database,

        [Parameter( ParameterSetName='Ins-Que',
                    Position=2,
                    Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [Parameter( ParameterSetName='Con-Que',
                    Position=2,
                    Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [string]
        $Query,

        [Parameter( ParameterSetName='Ins-Fil',
                    Position=2,
                    Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [Parameter( ParameterSetName='Con-Fil',
                    Position=2,
                    Mandatory=$true,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [ValidateScript({ Test-Path $_ })]
        [string]
        $InputFile,

        [Parameter( ParameterSetName='Ins-Que',
                    Position=3,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false)]
        [Parameter( ParameterSetName='Ins-Fil',
                    Position=3,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false)]
        [System.Management.Automation.PSCredential]
        $Credential,

        [Parameter( ParameterSetName='Ins-Que',
                    Position=4,
                    Mandatory=$false,
                    ValueFromRemainingArguments=$false)]
        [Parameter( ParameterSetName='Ins-Fil',
                    Position=4,
                    Mandatory=$false,
                    ValueFromRemainingArguments=$false)]
        [switch]
        $Encrypt,

        [Parameter( Position=5,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [Int32]
        $QueryTimeout=600,

        [Parameter( ParameterSetName='Ins-Fil',
                    Position=6,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [Parameter( ParameterSetName='Ins-Que',
                    Position=6,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [Int32]
        $ConnectionTimeout=15,

        [Parameter( Position=7,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [ValidateSet("DataSet", "DataTable", "DataRow","PSObject","SingleValue")]
        [string]
        $As="DataRow",

        [Parameter( Position=8,
                    Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    ValueFromRemainingArguments=$false )]
        [System.Collections.IDictionary]
        $SqlParameters,

        [Parameter( Position=9,
                    Mandatory=$false )]
        [switch]
        $AppendServerInstance,

        [Parameter( ParameterSetName = 'Con-Que',
                    Position=10,
                    Mandatory=$false,
                    ValueFromPipeline=$false,
                    ValueFromPipelineByPropertyName=$false,
                    ValueFromRemainingArguments=$false )]
        [Parameter( ParameterSetName = 'Con-Fil',
                    Position=10,
                    Mandatory=$false,
                    ValueFromPipeline=$false,
                    ValueFromPipelineByPropertyName=$false,
                    ValueFromRemainingArguments=$false )]
        [Alias( 'Connection', 'Conn' )]
        [ValidateNotNullOrEmpty()]
        [System.Data.SqlClient.SQLConnection]
        $SQLConnection
    )

    Begin
    {
        if ($InputFile)
        {
            $filePath = $(Resolve-Path $InputFile).path
            $Query =  [System.IO.File]::ReadAllText("$filePath")
        }

        #Write-Verbose "Running Invoke-Sqlcmd2 with ParameterSet '$($PSCmdlet.ParameterSetName)'.  Performing query '$Query'"

        If($As -eq "PSObject")
        {
            #This code scrubs DBNulls.  Props to Dave Wyatt
            $cSharp = @'
                using System;
                using System.Data;
                using System.Management.Automation;

                public class DBNullScrubber
                {
                    public static PSObject DataRowToPSObject(DataRow row)
                    {
                        PSObject psObject = new PSObject();

                        if (row != null && (row.RowState & DataRowState.Detached) != DataRowState.Detached)
                        {
                            foreach (DataColumn column in row.Table.Columns)
                            {
                                Object value = null;
                                if (!row.IsNull(column))
                                {
                                    value = row[column];
                                }

                                psObject.Properties.Add(new PSNoteProperty(column.ColumnName, value));
                            }
                        }

                        return psObject;
                    }
                }
'@

            Try
            {
                Add-Type -TypeDefinition $cSharp -ReferencedAssemblies 'System.Data','System.Xml' -ErrorAction stop
            }
            Catch
            {
                If(-not $_.ToString() -like "*The type name 'DBNullScrubber' already exists*")
                {
                    Write-Warning "Could not load DBNullScrubber.  Defaulting to DataRow output: $_"
                    $As = "Datarow"
                }
            }
        }

        #Handle existing connections
        if($PSBoundParameters.ContainsKey('SQLConnection'))
        {
            if($SQLConnection.State -notlike "Open")
            {
                Try
                {
                    #Write-Verbose "Opening connection from '$($SQLConnection.State)' state"
                    $SQLConnection.Open()
                }
                Catch
                {
                    Throw $_
                }
            }

            if($Database -and $SQLConnection.Database -notlike $Database)
            {
                Try
                {
                    #Write-Verbose "Changing SQLConnection database from '$($SQLConnection.Database)' to $Database"
                    $SQLConnection.ChangeDatabase($Database)
                }
                Catch
                {
                    Throw "Could not change Connection database '$($SQLConnection.Database)' to $Database`: $_"
                }
            }

            if($SQLConnection.state -like "Open")
            {
                $ServerInstance = @($SQLConnection.DataSource)
            }
            else
            {
                Throw "SQLConnection is not open"
            }
        }

    }
    Process
    {
        foreach($SQLInstance in $ServerInstance)
        {
            #Write-Verbose "Querying ServerInstance '$SQLInstance'"

            if($PSBoundParameters.Keys -contains "SQLConnection")
            {
                $Conn = $SQLConnection
            }
            else
            {
                if ($Credential)
                {
                    $ConnectionString = "Server={0};Database={1};User ID={2};Password=`"{3}`";Trusted_Connection=False;Connect Timeout={4};Encrypt={5}" -f $SQLInstance,$Database,$Credential.UserName,$Credential.GetNetworkCredential().Password,$ConnectionTimeout,$Encrypt
                }
                else
                {
                    $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2};Encrypt={3}" -f $SQLInstance,$Database,$ConnectionTimeout,$Encrypt
                }

                $conn = New-Object System.Data.SqlClient.SQLConnection
                $conn.ConnectionString = $ConnectionString
                Write-Debug "ConnectionString $ConnectionString"

                Try
                {
                    $conn.Open()
                }
                Catch
                {
                    Write-Error $_
                    continue
                }
            }

            #Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
            if ($PSBoundParameters.Verbose)
            {
                $conn.FireInfoMessageEventOnUserErrors=$false # Shiyang, $true will change the SQL exception to information
                $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
                $conn.add_InfoMessage($handler)
            }

            $cmd = New-Object system.Data.SqlClient.SqlCommand($Query,$conn)
            $cmd.CommandTimeout=$QueryTimeout

            if ($SqlParameters -ne $null)
            {
                $SqlParameters.GetEnumerator() |
                    ForEach-Object {
                        If ($_.Value -ne $null)
                        { $cmd.Parameters.AddWithValue($_.Key, $_.Value) }
                        Else
                        { $cmd.Parameters.AddWithValue($_.Key, [DBNull]::Value) }
                    } > $null
            }

            $ds = New-Object system.Data.DataSet
            $da = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

            Try
            {
                [void]$da.fill($ds)
            }
            Catch [System.Data.SqlClient.SqlException] # For SQL exception
            {
                $Err = $_

                #Write-Verbose "Capture SQL Error"

                #if ($PSBoundParameters.Verbose) {Write-Verbose "SQL Error:  $Err"} #Shiyang, add the verbose output of exception

                switch ($ErrorActionPreference.tostring())
                {
                    {'SilentlyContinue','Ignore' -contains $_} {}
                    'Stop' {     Throw $Err }
                    'Continue' { Throw $Err}
                    Default {    Throw $Err}
                }
            }
            Catch # For other exception
            {
                #Write-Verbose "Capture Other Error"  

                $Err = $_

                #if ($PSBoundParameters.Verbose) {Write-Verbose "Other Error:  $Err"} 

                switch ($ErrorActionPreference.tostring())
                {
                    {'SilentlyContinue','Ignore' -contains $_} {}
                    'Stop' {     Throw $Err}
                    'Continue' { Throw $Err}
                    Default {    Throw $Err}
                }
            }
            Finally
            {
                #Close the connection
                if(-not $PSBoundParameters.ContainsKey('SQLConnection'))
                {
                    $conn.Close()
                }
            }

            if($AppendServerInstance)
            {
                #Basics from Chad Miller
                $Column =  New-Object Data.DataColumn
                $Column.ColumnName = "ServerInstance"
                $ds.Tables[0].Columns.Add($Column)
                Foreach($row in $ds.Tables[0])
                {
                    $row.ServerInstance = $SQLInstance
                }
            }

            switch ($As)
            {
                'DataSet'
                {
                    $ds
                }
                'DataTable'
                {
                    $ds.Tables
                }
                'DataRow'
                {
                    $ds.Tables[0]
                }
                'PSObject'
                {
                    #Scrub DBNulls - Provides convenient results you can use comparisons with
                    #Introduces overhead (e.g. ~2000 rows w/ ~80 columns went from .15 Seconds to .65 Seconds - depending on your data could be much more!)
                    foreach ($row in $ds.Tables[0].Rows)
                    {
                        [DBNullScrubber]::DataRowToPSObject($row)
                    }
                }
                'SingleValue'
                {
                    $ds.Tables[0] | Select-Object -ExpandProperty $ds.Tables[0].Columns[0].ColumnName
                }
            }
        }
    }
} #Invoke-Sqlcmd2

Get-PrintAndSQLOutput – The key function of this blog post

This is the function that captures both the SQL output and the PRINT statement output. For example if the SQL was

select @@version as version; PRINT 'This is the print message'

The code to run the above and the output would be

$instances = @('MySQLServerHost\MyInst')
$sql = "select @@version as version; PRINT 'This is the print message'"
$rslt = Get-PrintAndSQLOutput -ServerInstance $instances -SQL $sql -OutputAs DataRow

$rslt 

ServerInstance : MySQLServerHost\MyInst
Database       : master
Status         : Success
StatusMessage  : Results fetched successuflly
StatusDate     : 3/16/2018 8:09:17 PM
Duration       : 00:00:00.1456139
Output         : System.Data.DataRow
PrintOuput     : This is the print message

Note that the Output of the SQL itself is accessible as shown below

$rslt.Output

version
-------
Microsoft SQL Server 2014 - 12.0.2269.0 (X64) ...

As can be seen, we get a nice wrapper around the output and the PRINT output with some timing/status

We can also run it against lots of instances instead of just one instance. Just add the instances to the $instances array.

You could even read-in a list of instances to run against from a text file

$instances = Get-Content c:\Temp\MyInstances.txt

We are getting ahead of ourselves as we would use a parallel/asynchronous method of execution if we were to run against a lot of instances.

The code for the function is below. It also contains a lot of examples in the code comments.

WordPress messes up PowerShell block comments. So, I am going to separate out the comments that go at the top of the code into its own section.

#
#######################
#
.SYNOPSIS
    Runs SQL against given instances and captures both the acutal output and PRINT statement ouput

.DESCRIPTION
    Sometimes we write/find scripts whose primary output is the PRINT output and
        it needs to be captured to be acted upon (or saved).

    This wrapper function captures both the actual output and the PRINT output and
        wraps them in an object before returning the same

    It also inclues a status column that may be checked when running against
        multiple instances

    For example, to get the SQL Server Service Pack and Cumulative Update(s) necessary for a instance
    This is the link to the site that MS maintains, they update this with each release, download the zip file.
    https://gallery.technet.microsoft.com/Determining-which-version-af0f16f6

    The SQL's only output is PRINT statement output and nothing else!

.INPUTS
    SQLInstance(s) and SQL to run + optional information

.OUTPUTS
    Output object with the inputs, error if any or the ouput

.EXAMPLE 

    #This example gets the info for a single host
    #  Notice that it has both output and PRINT output

    $instances = @('prodcmsserver')
    $sql = "select @@version as version; PRINT 'This is the print message'"
    $rslt = Get-PrintAndSQLOutput -ServerInstance $instances -SQL $sql -OutputAs DataRow
    $rslt

.EXAMPLE 

    #This example gets the info for multiple hosts (pipeline input for instances)

    $sql = "select @@version as version; PRINT 'This is the print message'"
    $rslt = @('prodcmsserver', 'MyOtherSever\instname', 'ThirdServer\MyInstance') |
                Select-Object @{name="ServerInstance";expression={$_}} |
                Get-PrintAndSQLOutput -SQL $sql -OutputAs DataRow
    $rslt

.EXAMPLE 

    #This example gets the info for multiple hosts and exports the output to a file

    $sql = "select @@version as version; PRINT 'This is the print message'"
    @('MySQLHost', 'MyOtherSever\instname', 'ThirdServer\MyInstance') |
                Select-Object @{name="ServerInstance";expression={$_}} |
                Get-PrintAndSQLOutput -SQL $sql -OutputAs DataRow |
                Set-Content C:\~Tmp\SQLServer\SQLServerVersionUpdates\DetermineVersionOfSQLServer.out

.EXAMPLE 

    #This example shows how to get the info for a lot of instances asynchronously!
    #Find out more about this at:
    #https://sqljana.wordpress.com/2015/08/18/invoke-async-asynchronous-parallel-processing-for-any-workload-in-powershell/

    [string] $sql = "select @@version as version; PRINT 'This is the print message'"
    [string[]] $instances = Get-Content c:\~Tmp\SQLServer\AllInstances.txt

    $sb = [scriptblock] `
            {
                param($ServerInstance, $BasePath, $SQL, $QueryTimeout, $As)  

                #Import all required functions that are needed for this scriptblock's functionality!
                . C:\~Tmp\Invoke-SqlCmd.ps1
                . c:\~Tmp\Get-PrintAndSQLOutput.ps1

                Get-PrintAndSQLOutput -ServerInstance $ServerInstance -SQL $SQL -OutputAs $As -QueryTimeout $QueryTimeout
            }

    #Build the variable need to splat the parameters (for the other parameters)
    $params = @{
                    BasePath = Get-PoShBasePath
                    SQL = $sql
                    QueryTimeout = 10
                    As = "DataRow"
                }   

    $rslts = Invoke-Async `
                -Set $instances `
                -SetParam ServerInstance  `
                -ScriptBlock $sb `
                -Verbose `
                -Measure:$true `
                -Params $params `
                -ThreadCount 8
    $rslts | ft

    #Now you can loop through and do your own row-by-row (object by object) processing if needed
    foreach($item in $rslts)
    {
        if ($item.Output.Status -eq 'Error')
        {
            "$item.Output.ServerInstance encountered an error!"
        }

        $item.Output.ServerInstance
        $item.Output.Status
        $item.Output.StatusMessage
        $item.Output.StatusDate
        "-----------------------------------------"
    }

.NOTES

    If you are working with a lot of instances,
        typically, you would want to capture this output to a table/file +
        invoke asynchronously as shown in
        https://sqljana.wordpress.com/2015/08/18/invoke-async-asynchronous-parallel-processing-for-any-workload-in-powershell/

Version History
    v1.0  - Mar 16, 2018. Jana Sattainathan [Twitter: @SQLJana] [Blog: sqljana.wordpress.com]

.LINK
    sqljana.wordpress.com

#
#-----------------------------------------------------------------------

The actual function code is below and it is quite simple! Please integrate the above comments with the code so that the ready to use examples can come in handy

function Get-PrintAndSQLOutput
{
    [CmdletBinding()]
    param
    (
        [Parameter(ParameterSetName='ServerInstance',
                   Mandatory = $false,
                   ValueFromPipeline=$true,
                   ValueFromPipelineByPropertyName=$true)]
        [string[]]$ServerInstance = @('.'),

        [Parameter(Mandatory = $false,
                   ValueFromPipeline=$false,
                   ValueFromPipelineByPropertyName=$true)]
        [string]$Database = 'master',

        [Parameter(Mandatory = $true,
                   ValueFromPipeline=$false,
                   ValueFromPipelineByPropertyName=$true)]
        [string]$SQL,

        [Parameter(Mandatory = $false,
                   ValueFromPipeline=$false,
                   ValueFromPipelineByPropertyName=$true)]
        [Int32]$QueryTimeout=600,         

        [Parameter(Mandatory = $false,
                   ValueFromPipeline=$false,
                   ValueFromPipelineByPropertyName=$true)]
        [ValidateSet('DataSet', 'DataTable', 'DataRow')] [string]$OutputAs='DataRow'

    )

    begin{}

    process
    {
        #Loop through each of the instances to get the output
        foreach ($inst in $ServerInstance)
        {
            Write-Verbose "Working on $inst"

            $object = New-Object –TypeName PSObject
            $duration = $null

            try
            {
                #The ouput produced by the SQL is done by PRINT statements
                # Invoke-SQLCmd2 writes the PRINT statement output as VERBOSE output
                # We redirect the VERBOSE output to STD output and assign the result to the variable $output
                # This is done by "4>&1" (redirect VERBOSE to STDOUT)

                #
                #Open connection, run and capture PRINT output
                #

                $duration = Measure-Command {

                        #https://www.powershellgallery.com/packages/Invoke-SqlCmd2/1.6.4
                        $verboseMessage = $($output = Invoke-Sqlcmd2 `
                                            -ServerInstance $inst `
                                            -Database $Database `
                                            -Query $SQL `
                                            -QueryTimeout $QueryTimeout `
                                            -As $OutputAs `
                                            -ErrorAction Stop `
                                            -Verbose) 4>&1

                        #Wish I could use the native SQLPS or SQLServer PowerShell module but...
                        #Get this error though: The WriteObject and WriteError methods cannot be called from
                        #    outside the overrides of the BeginProcessing, ProcessRecord, and EndProcessing methods,
                        #    and they can only be called from within the same thread. Validate that the cmdlet makes
                        #    these calls correctly, or contact Microsoft Customer Support Services.
                        #$verboseMessage = $($output = Invoke-Sqlcmd `
                        #                   -ServerInstance $inst `
                        #                   -Database $Database `
                        #                   -Query $SQL `
                        #                   -QueryTimeout $QueryTimeout `
                        #                   -ConnectionTimeout $QueryTimeout `
                        #                   -OutputAs DataRows `
                        #                   -Verbose) 4>&1

                    }

                $object | Add-Member –MemberType NoteProperty –Name ServerInstance –Value $inst
                $object | Add-Member –MemberType NoteProperty –Name Database –Value $Database
                $object | Add-Member –MemberType NoteProperty –Name Status –Value 'Success'
                $object | Add-Member –MemberType NoteProperty –Name StatusMessage –Value 'Results fetched successuflly'
                $object | Add-Member –MemberType NoteProperty –Name StatusDate –Value ([DateTime]::Now)
                $object | Add-Member –MemberType NoteProperty –Name Duration –Value $duration
                $object | Add-Member –MemberType NoteProperty –Name Output –Value $output
                $object | Add-Member –MemberType NoteProperty –Name PrintOuput –Value ($verboseMessage | Out-String).Replace('VERBOSE: ', '')

            }
            catch
            {
                Write-Error $_.Exception.Message

                $object | Add-Member –MemberType NoteProperty –Name ServerInstance –Value $inst
                $object | Add-Member –MemberType NoteProperty –Name Database –Value $Database
                $object | Add-Member –MemberType NoteProperty –Name Status –Value 'Error'
                $object | Add-Member –MemberType NoteProperty –Name StatusMessage –Value $_.Exception.Message
                $object | Add-Member –MemberType NoteProperty –Name StatusDate –Value ([DateTime]::Now)
                $object | Add-Member –MemberType NoteProperty –Name Duration –Value $duration
                $object | Add-Member –MemberType NoteProperty –Name Output –Value $null
                $object | Add-Member –MemberType NoteProperty –Name PrintOutput –Value $null
            }
            finally
            {
                #Return value
                $object
            }
        }
    }

    end{}

}

Capture Verbose Ouput to variable and return value to another variable:

Also, pay attention to a nice technique to capture Verbose output to a variable while at the same time capturing the actual return value output to another variable. Do checkout the technique that I found in the below blog. Very neat!

http://blog.simonw.se/programmatically-capture-verbose-output-in-a-powershell-variable/

This simple snippet illustrates the technique that I use in the function to capture the PRINT output as well as the SQL result output.

$VerboseMessage = $($Output = Write-ToStreams) 4>&1

Invoke-Async – The parallel PowerShell executioner!

As I said, I definitely urge you to take a look at the detailed blog post I did on this function but the below example should take it home for you as it runs the above function in parallel to maximize performance. Please use the code from the referenced link for Invoke-Async.

The example below only shows how to use Invoke-Async function for our purposes.

Let us say you have a huge list of instances to run your SQL against (plus capture PRINT output of-course). You would ideally have your instance list in a table or a file that can be read from


    #This example shows how to get the info for a lot of instances asynchronously!
    #Find out more about this at:
    #https://sqljana.wordpress.com/2015/08/18/invoke-async-asynchronous-parallel-processing-for-any-workload-in-powershell/

    [string] $sql = "select @@version as version; PRINT 'This is the print message'"
    [string[]] $instances = Get-Content c:\~Tmp\SQLServer\AllInstances.txt

    $sb = [scriptblock] `
            {
                param($ServerInstance, $BasePath, $SQL, $QueryTimeout, $As)  

                #Import all required functions that are needed for this scriptblock's functionality!
                . C:\~Tmp\Invoke-SqlCmd.ps1
                . c:\~Tmp\Get-PrintAndSQLOutput.ps1

                Get-PrintAndSQLOutput -ServerInstance $ServerInstance -SQL $SQL -OutputAs $As -QueryTimeout $QueryTimeout
            }

    #Build the variable need to splat the parameters (for the other parameters)
    $params = @{
                    BasePath = Get-PoShBasePath
                    SQL = $sql
                    QueryTimeout = 10
                    As = "DataRow"
                }   

    $rslts = Invoke-Async `
                -Set $instances `
                -SetParam ServerInstance  `
                -ScriptBlock $sb `
                -Verbose `
                -Measure:$true `
                -Params $params `
                -ThreadCount 8
    $rslts | ft

    #Now you can loop through and do your own row-by-row (object by object) processing if needed
    foreach($item in $rslts)
    {
        if ($item.Output.Status -eq 'Error')
        {
            "$item.Output.ServerInstance encountered an error!"
        }

        $item.Output.ServerInstance
$item.Output.Database
        $item.Output.Status
        $item.Output.StatusMessage
        $item.Output.StatusDate
$item.Output.Duration
        "-----------------------------------------"
    }

Above, pay attention to the lines that read the list of instance from a file and the two dot-sourced files referenced in the Scriptblock. The script block is the payload that gets executed in parallel. The call to Invoke-Async itself says how many threads to run in parallel.

Here is the output of

$rslts.Output | Out-Gridview

SQLPrintOutputCapture
$rslts.Output | Out-Gridview

It took around a minute or so to spin through 200+ instances and return the information with 10 threads running in parallel. I think that is very fast!

Conclusion

My hope is that you will find this function very useful. More than the function itself, I highly recommend that you pay attention to the parallelization code and try to adopt that to your own functions. As always, your comments and suggestions are welcome.

Advertisements

2 thoughts on “PowerShell & SQL Server: Run In Parallel – Collect SQL Results With PRINT Output From Across Your SQL Farm – Fast!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s