Multithreading
The traditional sequential way
| # 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
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.
This is added so that the -InputObject collection can be used as the first parameter
| -ModulesToImport dbatools
|
Here you can use any dbatools commands as all the commands are imported as part of the module import
| -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.