██████╗ ██████╗  █████╗ ████████╗ ██████╗  ██████╗ ██╗     ███████╗
██╔══██╗██╔══██╗██╔══██╗╚══██╔══╝██╔═══██╗██╔═══██╗██║ ██╔════╝
██║ ██║██████╔╝███████║ ██║ ██║ ██║██║ ██║██║ ███████╗
██║ ██║██╔══██╗██╔══██║ ██║ ██║ ██║██║ ██║██║ ╚════██║
██████╔╝██████╔╝██║ ██║ ██║ ╚██████╔╝╚██████╔╝███████╗███████║
╚═════╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═════╝ ╚══════╝╚══════╝

██████╗ ███████╗ ██████╗██╗██████╗ ███████╗ ██╗ ██╗ ██████╗ ██╗ ██╗ ██╔══██╗██╔════╝██╔════╝██║██╔══██╗██╔════╝ ████████╗██╔═████╗██║ ██║ ██████╔╝█████╗ ██║ ██║██████╔╝█████╗ ╚██╔═██╔╝██║██╔██║███████║ ██╔══██╗██╔══╝ ██║ ██║██╔═══╝ ██╔══╝ ████████╗████╔╝██║╚════██║ ██║ ██║███████╗╚██████╗██║██║ ███████╗ ╚██╔═██╔╝╚██████╔╝ ██║ ╚═╝ ╚═╝╚══════╝ ╚═════╝╚═╝╚═╝ ╚══════╝ ╚═╝ ╚═╝ ╚═════╝ ╚═╝ </pre>

Recipe #04 - Let's cook!

Deserts:

- Deploy sp_whoIsActive across instances

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

Execute to show that it is there


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

Example by getting the list of instances from your central server


In [ ]:
$SQLServers = Invoke-DbaQuery -ServerInstance "CentralServerName" -Query "SELECT InstanceConnection FROM CentralDB.dbo.Instances" | Select-Object -ExpandProperty InstanceConnection
Install-DbaWhoIsActive -SqlInstance $SQLServers -Database $databaseToDeploy