Install-DbaWhoIsActive puts sp_WhoIsActive on a server in one line, and I didn’t know it existed for a long time. I’d been downloading the release from Adam Machanic’s GitHub, opening the file in SSMS, checking I was pointed at the right instance, and running it. That’s maybe two minutes per server, which isn’t much until you’re doing it on every instance you touch.
I use sp_WhoIsActive on pretty much every SQL Server I work with, so it’s one of the first things I want on a new one. 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
I specify -Database master even though I don’t strictly have to. The documentation says it “defaults to master database if not specified in interactive mode,” but that it becomes mandatory in unattended scenarios so it doesn’t sit there prompting. Since the whole point is to stop doing this by hand, I write it out. Installing into master is also what makes the procedure available server-wide rather than in one database.
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.
The loop has no error handling in it, which hasn’t bitten me because I run it against a handful of servers I know are up and I’m watching the verbose output while it goes. I don’t actually know offhand what it does if one of them is unreachable partway through the list, and I’ve never tested it. If I were running this across a real inventory rather than three servers I’d want a try/catch collecting the failures, so I could see at the end which ones didn’t take.