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.
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
Pingback: Running Powershell Tasks With SQL Agent – Curated SQL
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.
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.
Post updated!
@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
}
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
Pingback: Comparing Agent Jobs across Availability Group Replicas with PowerShell | SQL DBA with A Beard
Pingback: Spotting SQL Agent Job Differences – Curated SQL
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.
Does `-EnableException` not address that for you? If not, I can ask Fred about adding exit codes.
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
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
Pingback: Installing dbachecks – dbatools
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?
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.
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.
Pingback: Dbatools #Parte2 Export-DbaScript | Reginaldo Silva
Pingback: Dbatools #Parte3 Test-DbaLastBackup | Reginaldo Silva
Pingback: Get-SQLBuildVersion uma forma diferente para se manter atualizado | Reginaldo Silva
Pingback: Em desenvolvimento
Hi Chrissi,
Thanks for your time making this post available.
Do you have any post or do you plan to do one about your central management server and the way you manage jobs centrally?
I would be very keen to learn about this and also get to know how do you manage it ? Isn’t it a single point of failure ?
Thanks
Tiago
Pingback: Em desenvolvimento
Hi Chrissy,
Thanks for sharing such a useful and detailed piece of information on scheduling PowerShell tasks with SQL Agent.
One little thing that is missing is the necessity of using -NoPorfile switch.
powershell.exe -NoProfile -File \\location\to\file.ps1
Otherwise, some of the cmdlets may fail. In particular, when calling Get-DbaBuildReference, we were getting the following error until we added the switch:
Error loading SQL build reference | Cannot bind argument to parameter ‘Path’ because it is null.
Pingback: Export SQL Server Agent Jobs #Dbatools | Reginaldo Silva
I had been using Invoke-DBAQuery in a SQL Agent job for the last few months without an incident but… last week I started receiving an error when I ran a scheduled task which included Invoke-DBAQuery. It runs successfully when I run it manually. I’ve reinstalled the module on numerous occasions using run as administrator. Here is the error.
Date 5/29/2019 1:11:10 PM
Log Job History (DBA – Get SQL Server data)
Step ID 1
Server DSQLB8SM001
Job Name DBA – Get SQL Server data
Step Name Launch PowerShell script
Duration 00:00:04
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Invoke-DbaQuery : The ‘Invoke-DbaQuery’ command was found in the module
‘dbatools’, but the module could not be loaded. For more information, run
‘Import-Module dbatools’.
At C:\scripts\powershell\Get-SQLServerData.ps1:56 char:13
+ $SQLServers=Invoke-DbaQuery -SqlInstance dsqlb8sm001 -Query “SELECT …
+ ~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Invoke-DbaQuery:String) [], Par
entContainsErrorRecordException
+ FullyQualifiedErrorId : CouldNotAutoloadMatchingModule
Any suggestions?
Hey Tim, can you check out this post and see if it helps and let us know? https://github.com/dataplat/dbatools/issues/5640
Pingback: dbatools advanced configuration – dbatools
Hi, I have a ps1 file that startswith “import-module dbatools”, which works fine interactively
In the agent job it returns…
Message
Executed as user: domain\myUser. Join-Path : Cannot bind argument to parameter ‘Path’ because it is null. At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.38\internal\configur ations\configuration.ps1:72 char:44 + $script:path_FileUserLocal = Join-Path $Env:LocalAppData “$psVers … + ~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Join-Path], ParameterBindingVa lidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,M icrosoft.PowerShell.Commands.JoinPathCommandJoin-DbaPath : Cannot bind argument to parameter ‘Path’ because it is an empty string. At C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.38\internal\configur ations\configuration.ps1:90 char:48 + $script:path_FileUserShared = Join-DbaPath $Env:AppData $psVersio … + ~~~~~~~~~~~~ + CategoryInfo : InvalidData: (:) [Join-DbaPath], ParameterBindin gValidationException + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAl lowed,Join-DbaPath. Process Exit Code 0. The step succeeded.
– however the script continues and the tools appear to work ok…
Hey Adrian, yours is the second report and have no idea how to fix it, unfortunately. It’s one of the most challenging issues as we cannot reproduce it. Perhaps you can -ErrorAction Ignore since it continues to work.
HI
Below script is working fine in ISE but if i run the same script in agent job iam getting blank output, can you please look into it and suggest me what changes i need to make
int] $Threshold = 20;
[Object[]] $computers = get-content -Path “”;
[string] $messagebody = “Server Name:” +$computer+”
Disk Name:” +$deviceid +”
Free Space Available:” +$freeSpaceGB +” GB
Recomendation-Either shrink the file or add more disk space to the server”;
[string] $smtpAddress = “”;
[string] $toAddress = “”;
[string] $fromAddress = “”;
foreach($computer in $computers){
$disks = Get-WmiObject -ComputerName $computer -Class Win32_LogicalDisk -Filter “DriveType = 3”;
$computer = $computer.toupper();
$deviceid = $disk.DeviceID;
foreach($disk in $disks){
$freeSpaceGB = [Math]::Round([float]$disk.FreeSpace/1073741824, 2);
if($freeSpaceGB -lt $Threshold){
[string] $username = “”;
[string] $password= “”;
[SecureString] $secpasswd = ConvertTo-SecureString $password -AsPlainText -Force;
[PSCredential] $mycreds = New-Object System.Management.Automation.PSCredential ($username, $secpasswd);
[string] $subject = “Warning Diskspace below the threshold”+ $computer +”for”+ $deviceid +”drive”;
Send-MailMessage -From $fromAddress -to $toAddress -Subject $subject -body $messagebody -smtpserver $smtpAddress -UseSsl -Port 25 -Credential $mycreds;
}
}
}
@wsuhoey and I just worked through an issue with $env:PSModulePath on slack (Thank you, @wsuhoey). Even though I was using the same domain account to develop my script (in vscode) and to run the SQL Agent proxy for cmdExec, it was not using the same profiles, and therefore an Import-Module in my script quietly failed in my job exec even with -Verbose and -ErrorAction “Stop”. Just a note for folks to pay attention to various powershell profiles and $env:PSModulePath. Cheers.