365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 9 - Putting It All Together

So now that you have some scripts to perform basic monitoring and status checking, you want to run it against your collection of servers and set it up as an automated process, so in comes PowerShell to help you out. The following script reads a list of servers that you specify in a separate text file and loops through all files with a .sql extension in a folder that you specify, running them against your servers and outputting the results into your specified output folder. All of it can be easily customized by changing a few lines here and there. Also, the commented sqlcmd line is for using SQL authentication and passing in the user name and password parameters when calling the PS script so if using SQL authentication, all user names and passwords will have to be the same.

#######################################
#PS Script (change the variables as necessary):

#The list of server to run the scripts against:
$ServerListFile = "C:\Users\davidzahner\Documents\PSScripts\ServerList1.txt"
$Servers = Get-Content $ServerListFile

#Set the folder location and file type filter:
$path = "C:\Users\davidzahner\Documents\PSScripts"
$filter = "*.sql"

#Set the output folder:
$OutputFolder = "C:\Users\davidzahner\Documents\CheckResults"

#Set U and P values from parameters:
$User = $Args[0]
$Pass = $Args[1]

$Date = Get-Date -UFormat "%Y%m%d"
$Date = $Date -replace "/", "_"

ForEach ($Server in $Servers)
{
$ServerString = $Server -replace "\\", "-"
$OutputFile = $OutputFolder + "\" + $Date + "__" + $ServerString + ".txt"
get-childitem -recurse -path $path -filter $filter | % {
        $file = $_
        #Sqlcmd -S $Server -U$User -P$Pass -i $file.FullName | Out-File -append -filePath "$OutputFile"
        Sqlcmd -S $Server -E -i $file.FullName | Out-File -append -filePath "$OutputFile"
    }
}

#######################################
#Server List Example:
SERVER05
SERVER05\INSTANCE2
SERVER04
SERVER04\INSTANCE2
SERVER03
SERVER03\INSTANCE2
SERVER02
SERVER02\INSTANCE2
SERVER01
SERVER01\INSTANCE2