Technical Blog

Using PowerShell Parallel Workflows to Run DBATools Against Hundreds of Servers

PowerShell workflows provide a powerful way to run PowerShell modules and scripts against multiple servers in parallel.

Iain Evans
Iain Evans

Published

There are a lot of different ways in PowerShell to run scripts against multiple instances, but most methods simply run serially one server at a time. This can be very time consuming when running scripts against hundreds of servers.

This blog will cover how to use the ‘ForEach -parallel’ workflow command to run scripts and modules (using DBATools as an example) against multiple servers simultaneously. Working with workflows introduces several challenges when compared to functions or modules, so I’ll also provide some useful tips for writing PowerShell scripts, and a template script I created for you to use.

ForEach-Object -parallel

The Powershell ForEach-Object cmdlet with the -parallel flag must be called within a workflow. We can create an extremely simple workflow to enable parallel execution like so:

Workflow TestSQLParallelWorkflow {
    PARAM ([array]$ServerList)
    FOREACH -parallel ($Server in $ServerList.Server) {
        # Do stuff
    }
}

TestSQLParallelWorkflow $ServerList

What are Powershell Workflows?

Workflows convert PowerShell code into .NET Framework using Windows Workflow Foundation (WWF) and not all Powershell commands have a WWF equivalent.

As a workaround, we can call an inline script to tell Powershell to treat this section of code as regular Powershell script. This includes functions, custom modules (such as DBATools, Azure PowerShell Az module, etc.) or command-line tools such as sqlpackage.exe

When calling variables in inline scripts, we can’t simply call the parameters passed into the parent workflow. This is because inline scripts treat the code block as an independent (aka remote) PowerShell session. Instead, we need to call our variable with the $Using scope modifier like so.

InlineScript {
    Invoke-Sqlcmd -ServerInstance $Using:Server -Database $Using:TargetDatabase -InputFile $Using:Script  -QueryTimeout 600 -AbortOnError -Verbose
}

Checkpoint

One of the major advantages of Workflows over modules or functions is the checkpoint feature. This allows the script to be resumed if it’s suspended or interrupted.

We can use this to our advantage in a couple of ways. Using the Checkpoint-Workflow will manually create a checkpoint when called, or setting the $PSPersistPreference preference variable within a workflow to $true will set a checkpoint automatically after each action.

Handling Errors

Powershell provides many different tools to facilitate error handling, my personal favourite of these is the try-catch block. An important point to note with inline scripts is that code executes within a separate workspace, meaning any errors raised will not be passed to the parent workflow.

To work around this, we can capture the output of the inline script then parse the text for certain keywords.

$Message = InlineScript {
    Invoke-Sqlcmd -ServerInstance $Using:Server -Database $Using:TargetDatabase -InputFile $Using:Script -QueryTimeout 600 -AbortOnError -Verbose 2>> $Using:LogPath
}

After this, the error details have been successfully passed to the parent workflows and can be handled in whichever way you prefer.

IF($Message -contains 'An error occurred while the batch was being executed.') {
    $ErrorMessage = $Message
    Write-Log -Path $LogPath -Message "Error encountered during deployment" -Level 'Error'
    }
}

A word of caution though, this strategy does increase execution time notably as variables are passing back and forth between the parent workflow and the remote instances, and parsing the output of a potentially very large message.

Write-Log Function

My favourite way to log errors is to use the Write-Log function found in this Microsoft TechNet post.

This brilliant little function writes to a log file, throws an error (which will terminate the script depending on the $ErrorAcationPreference setting) and writes to the console (so long as $VerbosePreference = “Continue”; all in a single line of code.

Write-Log -Path $LogPath -Message "$Message" -Level 'Error'

Executing

Workflows, like modules or functions, do not automatically execute. Instead, they must be called when required, allowing code to be reused when necessary, or stored remotely and called via CLI. For ad-hoc execution, just call the workflow at the bottom of the script:

TestSQLParallelWorkflow $TargetDatabase $ServerList $LogDirectory $SQLScripts $maxThreads

Putting It All Together

Here’s the complete script. Feel free to tear it apart and use it as required.

<# .DESCRIPTION
   Basic template script to demonstrate using DBATools and sqlcmd.exe with FOREACH -parallel
   #requires -version 3
   #>

$ErrorActionPreference = "Continue"
$DebugPreference = "Stop"
$Error.Clear()

# Parent directory used by other variables
[string]$WorkingDirectory = "C:\PowershellDemo"

# List of SQL Server instances to run against
[array]$ServerList = (Get-ChildItem $WorkingDirectory -Include ServerList.csv -Recurse | Select-Object -ExpandProperty FullName | Import-CSV -Header Server)

# Location where the Log files will be written to when calling the Write-Log function
[string]$LogDirectory = "$WorkingDirectory\TestSQLParallelWorkflow_" + (Get-Date -Format yyyy_MM_dd_HH_mm_ss) + "\"

# Name of the database .sql scripts will be run against
[string]$TargetDatabase = "Adventureworks"

# Location of the scripts to run against all SQL Instances
[array]$SQLScripts = (Get-ChildItem $WorkingDirectory -Include *.sql -Recurse | Select-Object -ExpandProperty FullName)

# Max number of Threads to use when running the workflow
[int]$maxThreads = 5

Workflow TestSQLParallelWorkflow {

    PARAM 
    ( 
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]
        [string]$TargetDatabase,

        [Parameter(Mandatory=$true)]
        [array]$ServerList,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [string]$LogDirectory,

        [Parameter(Mandatory=$true)]
        [array]$SQLScripts,

        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [int]$maxThreads
    )
    $PSPersistPreference = $true
    function Write-Log 
    { 
        [CmdletBinding()] 
        Param 
        ( 
            [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)] 
            [ValidateNotNullOrEmpty()]
            [Alias("LogContent")] 
            [string]$Message, 

            [Parameter(Mandatory=$false)] 
            [Alias('LogPath')] 
            [string]$Path='C:\Logs\PowerShellLog.log', 
        
            [Parameter(Mandatory=$false)] 
            [ValidateSet("Error","Warn","Info")] 
            [string]$Level="Error", 
        
            [Parameter(Mandatory=$false)] 
            [switch]$NoClobber 
        ) 

        Begin 
        { 
            # Set VerbosePreference to Continue so that verbose messages are displayed. 
            $VerbosePreference = 'Continue' 
        } 
        Process 
        { 
        
            # If the file already exists and NoClobber was specified, do not write to the log. 
            if ((Test-Path $Path) -AND $NoClobber) { 
                Write-Error "Logfile $Path already exists, and you specified NoClobber. Either delete the file or specify a different name." 
                Return 
                } 

            # If attempting to write to a Logfile in a folder/path that doesn't exist create the file including the path. 
            elseif (!(Test-Path $Path)) { 
                Write-Verbose "Creating $Path." 
                New-Item $Path -Force -ItemType File
                } 

            else { 
                # Nothing to see here yet. 
                } 

            # Format Date for our Logfile 
            $FormattedDate = Get-Date -Format "yyyy-MM-dd HH:mm:ss" 

            # Write message to error, warning, or verbose pipeline and specify $LevelText 
            switch ($Level) { 
                'Error' { 
                    Write-Error $Message 
                    $LevelText = 'ERROR:' 
                    } 
                'Warn' { 
                    Write-Warning $Message 
                    $LevelText = 'WARNING:' 
                    } 
                'Info' { 
                    Write-Verbose $Message 
                    $LevelText = 'INFO:' 
                    } 
                } 
        
            # Write log entry to $Path 
            "$FormattedDate $LevelText $Message" | Out-File -FilePath $Path -Append 
        } 
        End 
        { 
        } 
    }

    # Call a DBATools module and deploy scripts using SQLCMD
    FOREACH -parallel -throttlelimit $maxThreads ($Server in $ServerList.Server) {
        [string]$LogPath = $LogDirectory + $Server +".log"
        [string]$SpConfigFile = $LogDirectory + $Server + "\SpConfigure.sql"

        IF (!($ErrorMessage)) {
            Try {
                Write-Log -Path $LogPath -Message "Exporting SpConfigure settings for $Server using Dbatools" -Level 'Info'
                InlineScript {
                    $Using:SpConfigFile | ForEach-Object {
                        IF (Test-Path -Path $_) { 
                            # Do Nothing. File exists.
                        } 
                        ELSE {
                            New-Item -Path $_ -Force
                        } 
                    }
                    Export-DbaSpConfigure -SqlInstance $Using:Server -FilePath $Using:SpConfigFile
                }
            }
            Catch {
                    $ErrorMessage = $Message
                    Write-Log -Path $LogPath -Message "$Message" -Level 'Error'
            }
        }

        # Run the .sql scripts if no errors have been found
        IF (!($ErrorMessage)) {
            FOREACH ($Script in $SQLScripts) {
                IF(!($Message -contains 'An error occurred while the batch was being executed.')) {
                    Write-Log -Path $LogPath -Message "Executing $Using:Script on $Using:Server" -Level 'Info'
                    $Message = InlineScript {
                        Invoke-Sqlcmd -ServerInstance $Using:Server -Database $Using:TargetDatabase -InputFile $Using:Script -QueryTimeout 600 -AbortOnError -Verbose 2>> $Using:LogPath
                    }
                    IF($Message -contains 'An error occurred while the batch was being executed.') {
                        $ErrorMessage = $Message
                        Write-Log -Path $LogPath -Message "Error encountered during deployment" -Level 'Error'
                    }
                }
            }
        }
        ELSE {
            # Nothing to see here
        }
    }
    $PSPersistPreference = $false
}

# Call the workflow
TestSQLParallelWorkflow $TargetDatabase $ServerList $LogDirectory $SQLScripts $maxThreads

If($Error) {
    Write-Warning "Error(s) were detected during deployment. Review $LogDirectory for details."
}

Get Started With

Start Now
icon_bluestone98