SQL Server - Extensible SQL/PS Repository - thebakingdba

Repository_Extensible.ps1

#######################  
<#  
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