scheduling powershell tasks with sql agent

We’ve had a lot of people ask about the best way to schedule dbatools tasks in SQL Server Agent. I recently switched Agent from Task Scheduler and here’s what I learned.

tl;dr for those of you who just want to skip the commentary of pros vs cons and head straight to the tutorial, click Scheduling PowerShell Tasks using Agent’s CmdExec.

Task Scheduler

Task Scheduler was decent but ultimately, not as cool as SQL Server Agent for a number of reasons:

  • Task Scheduler lacks credential management
  • The console can’t be completely hidden without administrator access
  • Email on failure is a pain
  • Logging is lackluster
  • Scheduling is not as granular as the Agent

Credentials

With regards to credentials, you can add a task under another credential but it’s so manual. Got fifteen scheduled tasks? Get ready to add that credential fifteen different times. Need to update the password? Yep, fifteen times.


Scheduling PowerShell tasks in Task Scheduler

Now, as Fred Weinmann pointed out, you can also use PowerShell itself to manage credentials in a number of ways. But I prefer built-in management.

The dreaded popup window

Next is the PowerShell window that pops up for a millisecond when tasks are running. You can avoid this by selecting “Run whether the user is logged in or not” but this requires Administrator access.

The internet has a bunch of other suggestions that are usually cautioned with “but this does show for a moment”. A moment too long, for sure.

Email

Options for emailing were nasty, too. Unlike SQL Server where it’s managed and built-in, natively emailing in Task Scheduler is awkward at best and deprecated at worst.

Again, Fred pointed out that email can be sent using PowerShell’s Send-MailMessage but the Agent handles emailing on failure in a more straightforward manner. Similar to credentials, email management is just easier and built-in to Agent.

Logging

Like email, logging in Task Scheduler leaves a lot to be desired. Unless you generate a log or write to Windows Events, it is difficult to find out why it failed. In Agent, it’s easy to find the failures in Job History.

(What’s fun about blogging is sometimes, you realize just how bad something is because you have to write it all out. Wow, Task Scheduler is really inferior to SQL Server Agent.)

Granularity of scheduling

Shawn Melton pointed out that in Task Scheduler, he was unable to setup a task that runs every hour between 8am and 6pm. However, this is possible in SQL Server Agent.

SQL Server Agent

Agent is awesome and the solution to all of my issues so far. Well, Agent with a CmdExec Job Step is the solution to all of my issues so far. I really wanted to use the PowerShell Job Step but I’ve lost faith in it. I encountered all kinds of issues, including the inability to access UNC shares without prepending the variable with Microsoft.PowerShell.Core\FileSystem::

In Shawn’s fantastic post about Agent and dbatools, he outlines other problems including the fact that many (all?) external modules can’t load in the SQLPS host, which the PowerShell step still uses, even in SQL Server 2016. Note that the SQLPS host on the server-side is different from the updated SqlServer module, which is now available in the PowerShell Gallery.

CmdExec Job Step vs PowerShell Job Step

Derik Hammer also had a great post back in 2015 that detailed the differences between the two most popular ways to invoke PowerShell in an Agent job.

Here’s a (slightly updated) version of Derik’s chart.

So it basically boils down to this:

PowerShell Job Step CmdExec Job Step
sqlps.exe powershell.exe via cmd.exe
Version varies by SQL Server version Version is always the system version
Doesn’t always (rarely?) work as expected Often (always?) works as expected

And now that we’re all convinced to use the CmdExec Job Step, let’s set one up.  

Scheduling Tasks using Agent’s CmdExec

By default, only members of the sysadmin role are allowed to create jobs with the CmdExec Job Step, but adding non-sysadmins as principals to the CmdExec proxy works as well.

So here are the steps that I use to schedule my tasks:

  • Create a Windows-based Login in SQL Server
  • Ensure dbatools is available to the account
  • Create a SQL Server Credential
  • Create the Agent Proxy
  • Create the PowerShell .ps1 file
  • Create the Job and Job Step

Create a Windows-based login in SQL Server

This is out of scope for this post, but check out Microsoft’s page on logins if you need.

Note that you can easily manage remote SQL Servers from a centralized SQL Server Agent. In order to do this, you will need to provide the login with appropriate permissions on the remote SQL and Windows servers.

Ensure dbatools is available to the account

Basically, you can do this by performing an explicit import of the module (Import-Module path\to\dbatools\dbatools.psd1) to the exact path or, my preference, ensuring dbatools is globally available to all accounts on the server.

You can do this by placing dbatools in Program Files\WindowsPowerShell\Modules or using Install-Module dbatools from an admin prompt, which will do the same.

Setup a SQL Server credential

After the login has been created, I use the credentials (the AD username & password) to create a SQL Server Credential (Instance -> Security -> Credentials).

In this example, I called it the PowerShell Service Account, but admittedly, that’s a bit broad. In reality, I probably created an account like “SQL Server Audit Account” and limited my distinct auditing task requirements to that user.

While the SQL Server Agent service account could be used across the board, it’s better to use a distinct credential for my PowerShell tasks. This enables the granting of specific permissions to the account, as they are required.

Setup a SQL Agent Proxy

Once the credential has been created, it must be associated with the CmdExec proxy (Instance -> SQL Server Agent -> Proxies) to ensure it appears in the drop down once you create your SQL Agent Job Step.

In this case, I named the proxy “PowerShell Proxy” and gave it the credential I just created, “PowerShell Service Account”.

Create the PowerShell .ps1 file

Since we’ll be calling PowerShell from the command line, it’s easiest to create a .ps1 file and save it to a location that is accessible by the the Windows Server running the SQL instance. Remember, the job will run from the SQL Server’s perspective, so that instance should be able to access it.

I usually have a management SQL Server that runs all the jobs, so I save the file locally, but if I didn’t, I’d likely have a repository of scripts on a file share.

It’s also possible to setup an internal PowerShell Gallery as a central repository for scripts and modules that Agent Job Scripts can import. This could make it simpler from a management, version control and updatability point of view.

Create the Job and Job Step

Next, create the job as whatever you like, then when you get to the Job Step, create it as Operating System (CmdExec) and change the Run as: to your newly created proxy account.

In the Command: area, enter powershell.exe -File \\location\to\file.ps1

Need to Run As Administrator? Use -Verb runAs

In place of a -File path\filename, you could actually pass a base64 string into the -EncodedCommand parameter, but I’d rather have the ability to easily modify the script without having to reencode it.

Now you’re set!

Now you’re all set and can schedule this job just like you would any other. You can even have it email an operator on failure after enabling Agent to use Database Mail.

To see PowerShell and SQL Agent in practice, check out Björn Peters’s post Daily Database Copy using Powershell.

Ensuring failures fail

To ensure your failed jobs show a failure in SQL Agent, you must throw a terminating exception.

To throw a terminating exception in dbatools, use –EnableException. In most PowerShell commands, use try/catch with –ErrorAction Stop.

If you’re having issues and just can’t get it to fail, throw in a [System.Environment]::Exit(1) and that should certainly work.

In conclusion

You may be wondering why I am showing you the GUI when we’ve probably got some PowerShell commands to do this for us (we have a lot of ’em!). The reason is that I wanted to convey a concept.

Once we’ve got the whole suite of commands, including creating a credential and proxy, we’ll create a tutorial for doing all of this but with dbatools. For the curious, we do currently offer the following Agent commands, thanks to Sander Stad.

Also, if you have any good posts about SQL Agent and PowerShell to recommend, please let me know in the comments and I’ll update the post.

Hope that was helpful! Let us know if you have any questions.

- Chrissy

19 thoughts on “scheduling powershell tasks with sql agent

  1. Pingback: Running Powershell Tasks With SQL Agent – Curated SQL

  2. Derek Reply

    Have you found a way for SQL Server Agent to report a failure if there is a failure in the script? All my SQL Agent jobs report success even if there is a script error.

    • Chrissy LeMaire Post authorReply

      Hey Derek,
      Great question! That is something I need to add to this article. Failures result when you have a terminating exception. So you can try { Get-Whatever -ErrorAction Stop } catch { throw $_ } or within dbatools, use the -EnableException, which will be added within the next week or so.

      I’ll update this post when we’ve added the functionality.

  3. kena Reply

    @Derek,
    this works for us when executing pshell scripts by having sql agent exec a .cmd file that kicks off the pshell:

    1: always, repeat always, use $ErrorActionPreference=”Stop” for every script
    2: wrap your logic in try,catch,finally
    3: in the catch, populate a variable with the error message you caught
    4: in the finally:
    if($ErrMsg -ne ”)
    {
    Write-Output “Error message=”;
    Write-Output $ErrMsg ;
    ##can’t rethrow the error if we are kicking off this pshell from .cmd file, it wont report the error back to Sql Agent.
    ##but this will
    exit -99
    }

  4. rich Reply

    Hiya,
    I’m trying to schedule a restart of SSRS via SQL Agent.
    I’ve got the following in the Agent Job.
    Stop-Service “SQL Server Reporting Services (MSSQLSERVER)”
    Start-Service “SQL Server Reporting Services (MSSQLSERVER)”
    Although the job appears to run, the message returned is
    Executed as user: Domain\ServiceAcct. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is ‘Stop-Service “SQL Server Reporting Services (MSSQLSERVER)” ‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot find any service with service name ‘SQL Server Reporting Services (MSSQLSERVER)’.

    The job step runs using a proxy of the service account that SQL & SSRS run under (I know, not best practice but i’m trying to get it to work). Isomehow need to elevate permissions to get SQL to run the POSH commands to restart SSRS.

    Any ideas, much appreciated.
    Thanks
    Rich

  5. Pingback: Comparing Agent Jobs across Availability Group Replicas with PowerShell | SQL DBA with A Beard

  6. Pingback: Spotting SQL Agent Job Differences – Curated SQL

  7. Kin Reply

    It would be really good if there is an exit code – True (success) and False (failure) for all dbatools function. This ensures that the function can be used / integrated with salt or chef for automation.

  8. Kin Reply

    Hi Chrissy,

    No, -EnableException does not handle. Automation tools understand exitcode. So a switch like -EnableExitCode will help massively. If -EnableExitCode is turned on, then the command should just show
    0 = pass
    1 = fail.

    This way, a lot of get* dbatools commands can be used for doing qc after a server reboot.

    For now, I have a QC script that runs pre and post reboot of sql server cluster nodes. This is something that I wrote since dbatools does not have the exitcodes.

    Most of the stuff is covered by dbatools so would like to use that and phase out mine once ExitCodes are implemented.

    Thanks,
    Kin

    • Fred Reply

      Hi Kin,

      the problem here is not dbatools (as module code) alas, but rather the way cmdexec works. Powershell.exe properly terminates with an exitcode of 1, but the cmd running it doesn’t pass this on (cmd itself is running just fine and ends with 0).

      Can you try appending this to the code executed as cmdexec: & exit %ERRORLEVEL%
      This should have it try to pass the exitcode of PowerShell through to the cmdexec results (And with -EnableException set, it should be 1 on failure)..

      Cheers,
      Fred

  9. Pingback: Installing dbachecks – dbatools

  10. ob213 Reply

    I am creating an Agent job in the SQL Server 2017.
    Instead of using CmdExec, I am using step type of Powershell.
    The job hungs.
    Anybody has a success creating Powershell type steps? Is it pnly for the SQLPS?

    • Chrissy LeMaire Post authorReply

      hey ob, that was my experience as well, and likely other people’s too, which is why you’ll see cmdexec being so heavily favored in the community. Frustration is the name of the game when you’re using the PowerShell Job Step. I tried to embrace it but couldn’t.

  11. Ewan Reply

    Thanks for the article. Dont think I’ll be able to embrace this either 🙂
    Using a Scheduled Task instead.
    .ps1 script runs perfectly:
    1) manually
    2) as a Scheduled Task
    But not as a scheduled SQL Agent job step.
    Step is a CmdExec step – Run as a CmdExec Proxy
    CmdExec Proxy uses a Credential – sysadmin and full Admin on all the servers involved.
    Job step fails. Complains about “not enough memory or disk space”. There’s plenty of RAM.
    Too painful trying to get this to work to be honest. Spent hours on this.
    Just my 2 cents.

  12. Pingback: Dbatools #Parte2 Export-DbaScript | Reginaldo Silva

  13. Pingback: Dbatools #Parte3 Test-DbaLastBackup | Reginaldo Silva

  14. Pingback: Get-SQLBuildVersion uma forma diferente para se manter atualizado | Reginaldo Silva

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.