██████╗ ██████╗ █████╗ ████████╗ ██████╗ ██████╗ ██╗ ███████╗
██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔═══██╗██╔═══██╗██║ ██╔════╝
██║ ██║██████╔╝███████║ ██║ ██║ ██║██║ ██║██║ ███████╗
██║ ██║██╔══██╗██╔══██║ ██║ ██║ ██║██║ ██║██║ ╚════██║
██████╔╝██████╔╝██║ ██║ ██║ ╚██████╔╝╚██████╔╝███████╗███████║
╚═════╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═════╝ ╚══════╝╚══════╝
██████╗ ███████╗ ██████╗██╗██████╗ ███████╗ ██╗ ██╗ ██████╗ ██╗ ██╗ ██╔══██╗██╔════╝██╔════╝██║██╔══██╗██╔════╝ ████████╗██╔═████╗██║ ██║ ██████╔╝█████╗ ██║ ██║██████╔╝█████╗ ╚██╔═██╔╝██║██╔██║███████║ ██╔══██╗██╔══╝ ██║ ██║██╔═══╝ ██╔══╝ ████████╗████╔╝██║╚════██║ ██║ ██║███████╗╚██████╗██║██║ ███████╗ ╚██╔═██╔╝╚██████╔╝ ██║ ╚═╝ ╚═╝╚══════╝ ╚═════╝╚═╝╚═╝ ╚══════╝ ╚═╝ ╚═╝ ╚═════╝ ╚═╝ </pre>
NOTE: I wrote about it in my blog post: New version of sp_WhoIsActive (v11.20) is available – Deployed on 123 instances in less than 1 minute
Set variables
In [ ]:
$dbatools1 = "localhost,1433"
$dbatools2 = "localhost,14333"
$secureString = ConvertTo-SecureString "dbatools.IO" -AsPlainText -Force
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "sqladmin", $secureString
$databaseToDeploy = "master"
Deploy the stored procedure on a list of instances
In [ ]:
# If you have internet access
Install-DbaWhoIsActive -SqlInstance $dbatools1, $dbatools2 -SqlCredential $cred -Database $databaseToDeploy -Confirm:$false
If you have a file version of it
In [ ]:
$deploySplat = @{
SqlInstance = $dbatools1, $dbatools2
SqlCredential = $cred
Database = $databaseToDeploy
LocalFile = "D:\Presentations\PASS Marathon Portuguese 2020\Deploy-spWhoIsActive\who_is_active_v11_32.sql"
#Confirm = $false
}
# Without internet access but with a version saved on a local file
Install-DbaWhoIsActive @deploySplat
In [ ]:
Invoke-DbaQuery -SqlInstance $dbatools1 -SqlCredential $cred -Query "exec sp_whoIsActive @show_sleeping_spids = 2" -Database $databaseToDeploy
There is also a command to invoke the stored procedure
In [ ]:
Invoke-DbaWhoIsActive -SqlInstance $dbatools1 -SqlCredential $cred -ShowSleepingSpids 2
In [ ]:
$SQLServers = Invoke-DbaQuery -ServerInstance "CentralServerName" -Query "SELECT InstanceConnection FROM CentralDB.dbo.Instances" | Select-Object -ExpandProperty InstanceConnection
Install-DbaWhoIsActive -SqlInstance $SQLServers -Database $databaseToDeploy