I use sp_WhoIsActive on pretty much every SQL Server I work with. Installing it by hand is fine, but it feels unnecessary now that dbatools can do it for me. If you don’t have dbatools installed yet, I keep a short reference here: Installing dbatools.
If dbatools is already available, the install looks like this:
$instance = Connect-DbaInstance -SqlInstance "localhost\MSSQLSERVER" -TrustServerCertificate
Install-DbaWhoIsActive -SqlInstance $instance -Database master -Verbose
When I need it on several servers, I just loop through the list:
$servers = @(
"prod1\SQL"
,"prod2\SQL"
,"dev\SQL"
)
foreach ($s in $servers) {
$i = Connect-DbaInstance -SqlInstance $s -TrustServerCertificate
Install-DbaWhoIsActive -SqlInstance $i -Database master -Verbose
}Nothing complicated here. This just saves a few minutes and keeps things consistent across the environments I touch. It does the job without adding more steps to my day.