Skip to content

Multithreading

The traditional sequential way

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Get the list of servers. You can read directly from a text file or you can connect to your central database to get the list using tsql.
$servers = Get-Content D:\DBA\serverList.txt

# store credential in a variable which will be used for SQL authentication to your sql servers
$cred = Get-Credential -UserName sqladmin -Message "Enter your sqladmin Password"

# use foreach to loop through the collection and get the desired data
foreach ($server in $servers) {
    # here you can use any dbatools commands (Get-Dba*)
    Get-DbaAgDatabase -SqlInstance $server -SqlCredential $cred
}

Using PoshRSJob module to leverage Runspaces & Multithreading

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
# Get the list of servers. You can read directly from a text file or you can connect to your central database to get the list using tsql.
$servers = Get-Content D:\DBA\serverList.txt

# store credential in a variable which will be used for SQL authentication to your SQL Servers
$cred = Get-Credential -UserName sqladmin -Message "Enter your sqladmin Password"

# PoshRSJob has throttle parameter. I usually keep it to the number of processors installed on the server
$throttle = $env:NUMBER_OF_PROCESSORS

# Multithread! See below for a breakdown
Start-RSJob -InputObject $servers -Throttle $throttle -ModulesToImport dbatools -ScriptBlock {
    Param($server)
    Get-DbaAgDatabase -SqlInstance $server -SqlCredential $Using:cred
}

# Get-RSJob will display all jobs that are currently available to include completed and currently running jobs.
# Receive-RSJob Gets the results of the Windows PowerShell runspace jobs in the current session. Also you can use -OutVariable variableName e.g. -OutVariable Results and then do $Results to get all the output
Get-RSJob | Where-Object  {{State -like "Completed"} -and  {HasMoreData -like "False"} -and {HasErrors -like "False"} }  | Receive-RSJob

# cleanup only the jobs that are completed, does not have more data and no errors
Get-RSJob | Where-Object  {{State -like "Completed"} -and  {HasMoreData -like "False"} -and {HasErrors -like "False"} } | Remove-RSJob

Breakdown

1
-InputObject $servers

Here we are passing the collection item – $servers as an input object that will take that object and use it as the first parameter in the script block as long as you add a Param() block in it.

1
Param($server)

This is added so that the -InputObject collection can be used as the first parameter

1
-ModulesToImport dbatools

Here you can use any dbatools commands as all the commands are imported as part of the module import

1
-SqlCredential $Using:cred

This is pretty cool way of passing a local variable to the script block i.e. you can use variables from parent PS Process into PSJob’s scriptblock

On a side note, if you want to get the results or output of Start-RSJob into a variable, you can do it using $results = Get-RSJob | Receive-RSJob or Get-RSJob | Receive-RSJob -OutVariable Results (now you can use $Results same like former example) and then you can work with the result set as usual, and even write the entire result set to a database using Write-DbaDataTable.