I am writing a distribution package in PowerShell that will take a list of SQL Server names and distribute data from a host server to various production servers. I am a massive fan of dbatools.io and have started to use two methods from the dbatools suite, Invoke-DbaQuery and Copy-DbaDbTableData. I am interested in performing operations to multiple servers at once, of which I have dozens, but I am only testing with 4 right now.
I use PowerShell 7 and all of my SQL Servers are 2019 Standard Edition
To make sure my tests are isolated, I'm trying to simply insert into a table that has a default GETDATE() as one of the columns. I am running the below code straight from the SqlServer Powershell module and it does execute in parallel as expected, the inserts for these 4 values were within milliseconds of each other on their respective servers.
$results | ForEach-Object -ThrottleLimit 4 -Parallel {
$svrName = $_.svrName
$query = "insert into async_checker (svr_name) values ('$svrName') "
$output = Invoke-Sqlcmd -ServerInstance $svrName -Database myDb -Query $query -TrustServerCertificate
}
When I perform the exact same test but swap to the dbatools function, my first two servers in the list execute in Parallel, the Powershell seems to wait 20 seconds (consistently 20 seconds, no more or less) and then performs the 3rd and 4th inserts.
$results | ForEach-Object -ThrottleLimit 4 -Parallel {
$svrName = $_.svrName
$query = "insert into async_checker (svr_name) values ('$svrName') "
$output = Invoke-DbaQuery -SqlInstance $svrName -Database myDb -Query $query
}
Has anyone experienced this before when using dbatools vs a raw SQL connection?