[Servers] Extensible (Powershell) Repository - just add scripts!

(Version 1.1. I so should've posted this earlier - looks like everybody has been releasing their version of this idea over the past week).


Over the past few months, we've been working on knowing more about our servers.  Besides sp_blitz, there's a lot of data we want: DMVs, WMI info, etc. So a coworker & I had a challenge going - which would be a faster way to query our 80+ production servers, SSIS or PowerShell?  Well, he had it running faster, but then I asked him to up the number of simultaneous threads and it was a chore for him to change.  For me, alter a line of code in a text file.  And it seemed easier for me to add more scripts.

So I decided to make mine, while not (necessarily) the fastest, the easiest to use.  It uses Powershell and some PS scripts originally written by Chad Miller, Arnoud Jansveld, and several other people smarter than me.  I just put it all together.

TL;DR: Drop a query in a folder, and it runs several threads in parallel against all your servers, saving the details out to a table, overwriting the old data if you want.

Pros:

• Trivial install - 6 scripts in a folder, a table with a list of servers, 1 or 2 empty subfolders, and 1 job.
• Low overhead - a sample (simple) script ran on 80+ servers in under 6 seconds.
• Easy to add a new collection - just drop a SQL/PS script in the folder with the name for the table.
• Need more servers done faster?  Up the threads.
• Skips servers it can't connect to.
• No powershell extensions needed - just those 6 scripts.  PS3 below, modify the one line for PS2.

Cons:

• Does not deploy code; just runs scripts. (so sp_blitz, for instance, needs to be installed separately)
• No failure info. That's on my to-do list, but is not nearly as easy as I'd like
• Datatypes for new tables need to be tweaked if the script creates the table - text fields default to varchar(1000)

To install:

• Create a table with a list of servers to monitor.
• Create 3 folders: c:\sql_tools (or wherever; change the actual script's foldername), and underneath it one for scripts where you want to keep the data, and one where you don't.
• Grab add-sqltable.ps1, write-datatable.ps1, and invoke-sqlcmd2.ps1 from Hey Scripting Guy or poshcode. Put in c:\sql_tools.
• http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
• http://poshcode.org/3679 (Split-Job)
• Save the below script to a file called "repository_extensible.ps1", in c:\sql_tools.
• Create a job with 2 job steps, both as Type: "Operating System (CmdExec)":
• powershell "& c:\sql_tools\repository_extensible.ps1 c:\sql_tools\repository_scripts_keep 0"
• powershell "& c:\sql_tools\repository_extensible.ps1 c:\sql_tools\repository_scripts_delete 1"
• The first script runs scripts where each time, records will be added to the table.  The second will delete records from each server, replacing it with the new records. 
• Toss a couple sample scripts in the folders 
• For SQL, I recommend Paul Randall's excellent Wait & Latches scripts
• http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
• http://www.sqlskills.com/blogs/paul/survey-most-prevalent-latch-waits-code-to-run/
• For Powershell, you will need to tell the script what computer to run against.  This is because we're not using something like SQLCMD which has a "server" parameter for the entire script.
• Note the " -computername $args[0]" - that allows us to tell it which server to query.
• Get-WmiObject Win32_LogicalDisk -computername $args[0] 
• Name each scripts tablename_you_want.sql - the base name becomes the table name
• Run the job once.
• Go into your repository database and modify the table; by default strings automatically become varchar(1000).  Yes, it's not great, but will work for now.

	#######################
	<#
	Purpose: Take a list of servers from a table. Run a series of scripts in a folder against each server, saving to a table.
	Requirements: invoke-sqlcmd2, split-job (1.2.1), write-datatable, and add-sqltable.
	See link below for those scripts; split-job is from Poshcode
	Installation:
	Create a folder called c:\sql_tools.
	Put each of the 4 scripts in it.
	Create a folder called c:\sql_tools\repository_scripts.
	Put whatever SQL code you want to run against each server in the repository_scripts folder.
	Each resulting table will also have two fields: ServerName, and Insert_Datetime
	Change the sql_repository to be whatever your repository server is called.
	It will create a table for each script, named whatever the script is. If the table exists it should skip it, but
	even if it throws an error because it exists, it worked.
	For new scripts: if you don't have a table already set up for a script, run this then go back and shrink fields.
	Since we're using powershell "string" data types, the servername gets set as a varchar(1000).
	Alternatively, create a proper table to begin with. CreaTrying to make this simple.
	NOTE: ServerName & Insert_Datetime are added by this code; don't include in yours, or don't name them that.
	Invoking: currently two parameters: script path, and do you delete old records or not (default, aka 0 is to not delete)
	powershell "& c:\sql_tools\repository_extensible.ps1" c:\sql_tools\repository_scripts 0
	Thanks: Chad Miller and Hey Scripting Guy, along with Arnoud Jansveld (and the people on PoshCode) for split-job
	http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
	1.0 - mdb - initial release.
	1.1 - mdb - Powershell script support added, tweaked so that it runs in PS3
	1.11 - mdb 2013/04/16 - dumb bug that only ran last script instead of each script.
	1.2 - mdb 2013/05/22 - adding parameter to do ALL connectable servers.
	1.21 - mdb 2015/03/06 - writing out the current time when a script runs, so we know how long everything is taking.
	code found at http://techibee.com/powershell/powershell-create-a-variable-to-always-show-current-time/1132
	1.22 - mdb 2015/03/09 - adding some TRY CATCH, though this means you need to add a job to parse the log, since
	all the scripts will continue to run even if one breaks.
	Future enhancements: A way to better parse the filename so that it knows whether or not to delete, rather than a parameter.
	???
	#>
	clear
	. C:\sql_tools\invoke-sqlcmd2.ps1;
	. C:\sql_tools\split-job.ps1;
	. C:\sql_tools\write-datatable.ps1;
	. C:\sql_tools\add-sqltable.ps1;
	$script_path=$args[0] #Where the scripts sit that you want run. Done this way so you can have two folders, 1 that deletes, 1 that doesn't.
	$do_we_delete=$args[1] #1 means yes, delete old. 0 means no, keep old
	$include_non_prod=$args[2] #1 means all servers, anything else (including blank) means prod-only
	$server_repository = 'sql_repo'
	$database_repository = 'EPR_repo'
	#get list of servers that meet our criteria; our code will run against these
	if ($include_non_prod -eq 1)
	{
	$serverlist = invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "SELECT server FROM dbo.ServerList WHERE Connect = 1 order by server"
	}
	else
	{
	$serverlist = invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "SELECT server FROM dbo.ServerList WHERE Connect = 1 order by server"
	}
	#list out the servers we'll be doing; this way the errorlog gets the list.
	$serverlist
	#now use Split-Job (from POSHcode) to split into a number of Runspaces ("hosted runspaces")
	#variables need to be inside the split-job due to scope issues, though there's a parameter (see end) that will import them.
	$serverlist|Split-Job {%{
	#because the scope is separate in here, you need to re-dot-source and reinit your parameters.
	. C:\sql_tools\invoke-sqlcmd2.ps1;
	. C:\sql_tools\write-datatable.ps1;
	. C:\sql_tools\add-sqltable.ps1;
	. C:\sql_tools\out-datatable.ps1;
	$server2 = $_.server
	#adding a variable so we can easily write out the time in the log
	$global:currenttime= Set-PSBreakpoint -Variable currenttime -Mode Read -Action { $global:currenttime= Get-Date }
	write-host $server2 #for the logs so we know where we are thus far. Not 100% since we run in parallel.
	#Run each SQL script in our folder
	if (!$script_path.EndsWith("\"))
	{
	$script_path = $script_path + "\"
	}
	#get list of files
	$Dir = get-childitem $script_path -recurse
	$List = $Dir | where {$_.extension -eq ".sql" -or $_.extension -eq ".ps1"}
	#Now run each script
	foreach ($file.Name in $List)
	{
	$tablename = $file.BaseName
	write-host $tablename ' ' $currenttime
	#Doing all of the script running (including table create) as part of the try..catch. But it means that
	# you need to scrape the logfile to make sure everything ran; it won't choke if a file is bad.
	try
	{
	#SQL Scripts
	# Nothing special needed; uses invoke-sqlcmd2 to run same script on each server
	if ($file.extension -eq ".sql")
	{
	#$server2$tablename #list the scripts being run
	#run the actual query
	$quer = invoke-sqlcmd2 -serverinstance $server2 -InputFile $script_path$file -As 'DataTable'
	}
	#Powershell Scripts
	# In the script, you need to tell it which computer to query, like: -computername $args[0]
	if ($file.extension -eq ".ps1")
	{
	$command = ". '$script_path$file'" + " " + $server2
	$command
	$quer = invoke-expression $command | out-datatable
	}
	#add the ServerName to the results and populate it.
	$quer.Columns.Add("ServerName") |out-null
	$quer | %{$_.ServerName = $server2}
	#add the Insert_Datetime field and populate it.
	$quer.columns.add("Insert_Datetime",[DateTime]) |out-null
	$quer | %{$_.Insert_Datetime = [datetime](Get-Date)} #Better version by Nick on Stackoverflow
	#Now that we have our table, complete with ServerName, create the table, delete existing rows, and add new rows
	#create the table if it doesn't exist (can throw error the first time per script, because multiple servers hit it at once)
	$tablecount = invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository `
	-query "select count(*) as tablecount from information_schema.tables where table_name = '$tablename'"
	if ($tablecount.tablecount -eq 0)
	{
	add-sqltable -serverinstance $server_repository -Database $database_repository -Tablename $tablename -DataTable $quer
	}
	#We do this here so that, if a server goes away or becomes unreachable, we still have the last set of data on it.
	if ($do_we_delete -eq 1)
	{
	invoke-sqlcmd2 -serverinstance $server_repository -database $database_repository -query "delete from $tablename where servername = '$server2'"
	}
	Write-DataTable -ServerInstance $server_repository -Database $database_repository -TableName $tablename -Data $quer
	}
	catch
	{
	#throw $_.Exception.Message #doesn't remove enough crap
	Write-Error $_.Exception.Message #-ErrorAction Stop I dont want to stop it, just throw an error.
	}
	}
	}} -MaxDuration 300 -MaxPipelines 6 -Variable script_path, server_repository, do_we_delete, database_repository -NoProgress
	#the variable above allows the split-job to read from outside the scope and put it inside

view raw Repository_Extensible.ps1 hosted with ❤ by GitHub