SQL Process Automation with PowerShell – Donnie's Azure, Automation, Monitoring, and Management Blog

Here is a handy way to handle SQL processes that you find yourself needing to schedule. Of course you can always setup that kind scheduling via the SQL Server Agent, but there are two good reasons to do this kind of scheduling via PowerShell.

1: You don’t have rights to add jobs to via the SQL Server Agent. Some security teams will restrict non-dba access to either the agent or insist on setting the agent to a manual start.
2: You wish to have easier tracking, easier configuration, and just want to do something cool with PowerShell.

The other option you have when trying to schedule something like SQL processes would be to simply use Task Scheduler. Indeed – in my solution I actually use Task Scheduler as a base engine to run every minute or so. What I don’t like about Task Scheduler is trying to put SQL command lines in it. It’s flat out a pain. So, I built something that was easy to configure – even by someone who is not skilled in PowerShell, easy to implement, and has all of the typical good stuff you want with PowerShell.

The solution utilizes 3 main pieces. First, is a script that we will schedule to run every minute via Task Scheduler. Second, a configuration file in JSON format. I choose JSON since it’s simple to read and easy to write. Lastly, we will have XML file that tracks when the last time something ran. Let’s examine each piece:

The script file is fairly straight forward, and there are only a couple of pieces that need explaining. In simple terms this script file will be used as the engine that calls the various SQL commands we specify in the config.json file. We import a few modules, setup a couple of base variables, and then loop through the SQL commands, updating the runhistory.xml file with the timestamp. Schedule this file to run every minute via task scheduler.

$log = $PSScriptRoot + '\'+ ($MyInvocation.MyCommand.Name).split('.')[0] + '.log'
$ModulesDir = 'D:\pwsh\modules'
$ModulesToImport = 'DDTLogging','SQL'
foreach ($module in $ModulesToImport){Get-ChildItem $ModulesDir\$module\*.psd1 -Recurse | resolve-path | ForEach-Object { import-module $_.providerpath -force }}
$Date = Get-Date
$Commands = (get-content $PSScriptRoot\config.json|convertfrom-json).commands
[xml]$RunHistory = get-content "$PSScriptRoot\runhistory.xml"
foreach ($command in $Commands){
    $commandname = $command.name
    $LastRunTime = ($runhistory.Catalog.dataset|where-object {$_.name -eq $commandname}|select-object -Property time).time
    if ($LastRunTime -lt $Date.AddMinutes(-$command.TimePeriod) -or $LastRunTime -eq $null)
    {
        if ($LastRunTime -eq $null){
            $newdata = $RunHistory.Catalog.AppendChild($RunHistory.CreateElement("dataset"))
            $newdata.SetAttribute("name","$commandname")
            $newdata.SetAttribute("time","$date")
            $RunHistory.Save("$PSScriptRoot\runhistory.xml")
        }
        else {
            $RunHistory.Catalog.dataset|where-object {$_.name -eq $commandname}|foreach {$_.time = [string]$date}
            $RunHistory.Save("$PSScriptRoot\runhistory.xml")
        }
        try{
            invoke-sql -server $command.server -database $command.database -method $command.commandtype -integratedsecurity $true -statement $command.statement
        }
        catch{
            write-log -text "Error in $commandname.  $_" -level ERROR -log $log
        }
    }
}

If you are wondering about the Invoke-SQL command, it’s something that I wrapped up in a quick module. You can get the module here.

Now that we have the base script, let’s look at the config.json file. This is where the meat of the information about your commands come from:

{
    "Commands":
    [
        {
            "name":"SQL_Views_Stored_Proc",
            "timeperiod":15,
            "statement":"exec refreshviews",
            "server":"sqlserver1.constoso.com",
            "database":"customers",
            "commandtype":"execute"
        },
        {
            "name":"Update_Time",
            "timeperiod":15,
            "statement":"update cogsuppliers set time = getdate()",
            "server":"sqlserver2.constoso.com",
            "database":"suppliers",
            "commandtype":"update"
        }       
    ]
}

As you can see – it’s a pretty straight forward. Put in the frequency you would like the statement to run (timeperiod), the statement itself, the server and database to run on, and finally the ‘type’ of command it is. This is just to tell the SQL module whether or not to load the data into a data table. That’s it! Anyone with a basic knowledge of how to write a json file can add or remove from this config file quickly!

The final piece is the runshistory.xml file. This simply lets the main script keep track of the last time each statement was run. You shouldn’t have to ever update this file manually.

<Catalog>
  <dataset name="SQL_Views_Stored_Proc" time="11/15/2018 10:49:12" />
  <dataset name="Update_Time" time="11/15/2018 10:49:12" />
</Catalog>

For full transparency, there are a few things I just need to swing back around and address. First – the commands runs sequentially. Long running SQL statements might cause others to fail or fall behind. The plan for that is to use something like PoshRSJob to run the jobs in parallel, and each in it’s own runspace. Secondly, there is a small chance that if someone was to manually edit the runhistory.xml file and remove one of the lines the script could error. I will update the script with a catch to make sure this doesn’t happen.

Leave a Reply Cancel reply