[Powershell] Running sp_blitz against multiple servers in parallel, saved to table

(update 2013/06/13: I've superceded this with a new framework that will run almost any piece of code, powershell or sql, saving the results to a table, and running with multiple threads. Please give it a look!
http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html )

(update 2013/02/09: for some reason, running with only 6 threads doesn't bring back all the servers.  Using 20, however, does.  Odd, and not expected, and obviously I need a better fix)

(update 2013/02/08: if results from sp_blitz aren't saved for a particular server, run the invoke-sqlcmd2 command for sp_blitz against just that server, manually. For sp_blitz we've found a couple bugs and are sending them to Brent Ozar to fix.  Right now, if there are any errors thrown, even if there are results, then invoke-sqlcmd2 doesn't work right.)


So, I recently found out WHY sp_blitz is awesome, and why "30 second takeover" is a horrid subtitle for it. (I always thought that it meant "break into a SQL server in 30 seconds", so I never went - NOPE).

Anyhow, sp_blitz itself is handy as heck - gets a list of common problems that a particular server has, when it gets handed to you and you basically "takeover" and become its DBA in a matter of 30 seconds.

So, the next thing I wanted to do was save the results to a centralized table.  Actually raced a coworker to do it - he did one in SSIS, naturally I wanted it in Powershell.  Later we plan to do "server thunderdome" and see whose runs fastest.

NOTE: this is almost exactly the same code as in my earlier post http://thebakingdba.blogspot.com/2012/12/powershell-run-query-against-multiple.html . The biggest change is the addition of a ServerName field (which was a lot harder to figure out than you'd think, so many thanks to Chad Miller for the fix, and Graimer for a shorter version).  Given it wasn't a simple thing (for me) to add, and what I'm doing with it, I figure it'd be worth another post.

Future changes: I think I'm going to modify it so it looks at a folder, running each script from that folder and saving it to a table named for that script.  Also getting it to return errors; between invoke-sqlcmd2 and the scriptblock, it basically eats any errors and you never see them.  Need to get that working better.

So, first, create a table to hold the data:

CREATE TABLE [dbo].[BlitzResults](
[Priority] [tinyint] NULL,
[FindingsGroup] [varchar](50) NULL,
[Finding] [varchar](200) NULL,
[DatabaseName] [varchar](50) NULL,
[URL] [varchar](200) NULL,
[Details] [nvarchar](4000) NULL,
[QueryPlan] [xml] NULL,
[QueryPlanFiltered] [nvarchar](max) NULL,
[CheckID] [int] NULL,
[ServerName] [varchar](50) NULL
)



Make sure to save the invoke-sqlcmd.ps1 and the write-datatable.ps1 scripts to a folder (c:\sql_tools, change it however you want).

Now create this script:

#Run SQL query against multiple servers in parallel, saving results to a central table
# 1.00 2012/12/13 mdb / TBD.  Initial release
# 1.10 2012/12/13 mdb / TBD.  Adding throttling scheduler
# 1.11 2012/12/13 mdb adding comment on WHY you want the wait-job
# 1.20 2012/02/07 mdb adding a ServerName field to the datatable. Now we can do sp_blitz w/o modifying it!
# Code cribbed and lessons learned courtesy of: Hey Scripting Guy,
# Aaron Bertrand (the bit with $args[0], his "sad panda face" post)
# Kendra Little
# Throttling scheduler is from "start-automating" Stackoverflow.
# Additional Servername field from Chad Miller and Graimer (stackoverflow)
# Errors are mine, not theirs.
# Please keep this header and let me know how it works. thebakingdba.blogspot.com
# Prerequisite: invoke-sqlcmd2 and write-datatable, courtesy of Hey Scripting Guy & Chad Miller
# Long header courtesy of .... um, me.
clear
. C:\sql_tools\invoke-sqlcmd2.ps1;
#get list of servers that meet our criteria; our code will run against these
$serverlist = invoke-sqlcmd2 -serverinstance "ftw-test-08" `
-query "SELECT server FROM yourlistofservershere WHERE active = 1 and version >=9 order by server"
foreach ($server in $serverlist)
{
    #job running code; allows us to multithread.  -le 8 means 8 jobs run at once. As one drains, the next picks up
    $jobsrunning = @(Get-Job | Where-Object { $_.JobStateInfo.State -eq 'Running' })
    if ($jobsrunning.Count -le 8)
    {
        start-job -argumentlist $server.server -scriptblock `
        {
        #a scriptblock is a wholly separate 'environment'; have to reinvoke functions and reintroduce variables
        . C:\sql_tools\invoke-sqlcmd2.ps1;
        . C:\sql_tools\write-datatable.ps1;
        $server2 = $args[0]

        $quer = invoke-sqlcmd2 -serverinstance $server2 -database "master" `
            -query "exec master.dbo.sp_blitz" -As 'DataTable'

        $quer.Columns.Add("ServerName")

        $quer | %{$_.ServerName = $server2}

        Write-DataTable -ServerInstance "repositoryserver" -Database "dba_stuff" -TableName "BlitzResults" -Data $quer
        }
    }
    else
    {
        $jobsrunning | Wait-Job -Any  #as soon as any job finishes, push the next up
    }
}

#Now that we're finished, cleanup and get rid of any errant jobs  
get-job | wait-job -timeout 120         #wait 120 seconds or until all jobs are done, whichever comes first

get-job|Remove-Job -force              #cleanup and remove the jobs