Increase PowerShell available commands in a SQL Server Agent Job step

Problem

It is often required to perform advanced operations within a SQL Server Agent Job step, like interact with the operating system or import custom modules. For this, you’re able to create a PowerShell type step, but the problem is that the Powershell version varies when it runs inside a job step, so many people recommend (link link link) creating a .ps1 file and specify an “Operating system (CmdExec)” type step.

But what if you need to implement this job as a Multi-Server job? It will be a nightmare when you need to update the script in all your servers, keeping up to date the existing servers and copy this script to the new ones, not to mention that the path may not always be the same. So, what other option do you have?

Solution

Here’s the thing: when you run a “PowerShell” type step, it runs as SQLPS.exe which is a sub-set of PowerShell, so not all commands are available. Here is a comparison of the first commands available in each of them:

As you can see, there are a lot few commands in SQLPS.exe than when it runs from PowerShell.exe.

Let’s start by looking at a command to get the perf counters of the local machine, and what is the output in the different PowerShell options.

From PowerShell.exe:

PS C:\Users\PE> $result = Get-Counter | select -ExpandProperty CounterSamples
PS C:\Users\PE> $result[0]
Path                              InstanceName       CookedValue
----                              ------------       -----------
\\server01\network interface(...  core               122376.167085421
PS C:\Users\PE>

From SQLPS.exe:

PS C:\Users\PE> $result = Get-Counter | select -ExpandProperty CounterSamples
The term 'Get-Counter' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:1 char:22
+ $result = Get-Counter <<<<  | select -ExpandProperty CounterSamples
    + CategoryInfo          : ObjectNotFound: (Get-Counter:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
PS C:\Users\PE>

From a PowerShell type step:

But this doesn’t mean we can’t use all the available PowerShell commands and functions, because you can invoke PowerShell.exe passing the command to execute, just like you would do from a CmdExec type step. If we modify the script, we’re able to get the results.

Script:

$result = powershell.exe -command "Get-Counter | select -ExpandProperty CounterSamples"
$result[1]
$result[2]
$result[3]

Output:

Path                               InstanceName       CookedValue
----                               ------------       -----------
\\server01\network interface(...   core               109935.744974759

As you may have already noticed, the result is an array of strings with the output from PowerShell.exe, so if you need the object with its properties and methods, you must think about serialize/deserialize.

However, if you can deal with this string output (as in my case), here are a few things you must consider:

The final script, the one that runs inside a PowerShell type step, contains the following lines:

$list = New-Object System.Collections.Generic.List[System.Object]
$availCounters = powershell.exe -command "Get-Counter -ListSet * | select -expand Counter"
$availCounters = $availCounters -join ","
$results = powershell.exe -command "`$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size(500,25); Get-Counter -Counter @($availCounters) | select -ExpandProperty CounterSamples | select Path, '|', CookedValue | Format-Table -Property * -AutoSize | Out-String -Width 100"
$pos = $results[1].IndexOf("|")
$results2 = $results | where {$_.StartsWith("\\")} | select @{l="Path";e={$_.Substring(0, $pos).Trim()}}, @{l="CookedValue";e={$_.Substring($pos, $_.length - $pos).Trim()}}
foreach ($r in ($results2 | select Path, CookedValue)) { $list.add($r) }

And what is the benefit of this way vs. CmdExec? As I mentioned earlier, if you need to create a multi-server job, you don’t have to create a .ps1 file in each of your servers, avoiding the cumbersome of finding a common path and dealing with updating the file when you make a change to the script.

You could also have a stored procedure that can be easily updated in multiple servers at once by running a multi-server query, but you would have to manage all the programming (string, split, tables, etc.) through T-SQL, which will require a huge amount of work and at the end the code will be illegible.

Next Steps

• Think of what other command you would like to use and you couldn’t because it was not available in a PowerShell type job step.
• Here is a list of powershell commands available in SQL Server 2016 link
• In the next tip we’re going to get the top priority perf counters with their thresholds, to have an automated email alert system when there are issues.

Pablo Echeverria

Pablo Echeverria has worked for more than 10 years as a software programmer and analyst, during which time I studied parallel programming and became a senior programmer specialist. Afterward, he switched to a DBA position implementing new processes and creating better monitoring tools, while growing his data scientist skills to improve my customer’s businesses.  Check out Pablo’s most recent book, “Hands-on Data Virtualization with Polybase“. This book brings exciting coverage on establishing and managing data virtualization using Polybase. It teaches how to configure Polybase on almost all relational and nonrelational databases, to setup a test environment for any tool or software instantly without any hassle, and to rapidly design and build high performing data warehousing solutions.

• MSSQLTips Awards: Rising Star (50+ tips) – 2024 | Author Contender – 2018, 2022, 2023 | Rookie Contender – 2017