Thursday, February 7, 2013

[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! )

(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 . 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.
# Prerequisite: invoke-sqlcmd2 and write-datatable, courtesy of Hey Scripting Guy & Chad Miller
# Long header courtesy of .... um, me.
. 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 | %{$_.ServerName = $server2}

        Write-DataTable -ServerInstance "repositoryserver" -Database "dba_stuff" -TableName "BlitzResults" -Data $quer
        $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


Brent Ozar said...

Cool, glad you found the script useful! I chuckled about the takeover angle - I never thought of it that way, but makes sense.

bourgon said...

Crazy useful, and I love that you're taking submissions for new things to check. We've already found one bug for either Sharepoint or Reporting Services, where the database name is longer than 50. Coworker should be mailing it to you.

Thanks for commenting - more MVP notice can't be a bad thing!