SQL Server cmdlets

PowerShell Help
1

I have a question regarding this sentence…“SQL PowerShell 2016 starting with the SSMS 2016 now includes 6 cmdlets for working with SQL Agent objects.”

I am trying to write a script to monitor SQL Agent job status on many servers. But I am pretty sure those new cmdlets will ONLY work when connecting to a server that has SQL 2016 installed, right? The servers on my network are 2016,2012, 2008.

-KS

2

I have not used them, specifically because I have not SQL2K16 deployed, but I’d strongly suggest you take look at this module…

'powershellgallery.com/packages/dbatools/0.9.395'
… and the authors article / site here:
scheduling powershell tasks with sql agent 'dbatools.io/agent'
3

You are just performing a GET operation, so install SQL 2016 tools, import the module and see if you can connect to the SQL agent on all of the servers. But also a quick search nets:

with regards to the SQL Agent Cmdlets:

One of the best things about these cmdlets is that it makes failed jobs a whole lot easier to find. Note that SQL Server Management Studio 2016 does not have to be installed on the server — only your workstation. That's because the cmdlet is built on top of SMO which is available in all versions of SQL Server since 2000.
4
  • dont need to install SQL Server tools, you can install the sqlserver or pwsh (PowerShell Core) module on it’s own and get everything they have.
5

Thanks for all the replies. dbatools seems to be a “must have” for a DBA. I wrote this simple script using Find-DbaAgentJob but it only seems to run against my SQL 2014 & 2016 SQL servers. I thought it might be the powershell version on the other servers but they are at v.4 Any ideas?

$allservers = "SVR2016","SVR2014","SVR2008"

$countOfErrors = ($allservers | Find-DbaAgentJob -failed -Since (Get-Date).AddDays(-1) | measure-object).Count

if($countOfErrors -gt 0) {

$allservers | Find-DbaAgentJob -failed -Since (Get-Date).AddDays(-1) | out-file c:\downloads\sqlAgentErrors.txt

Send-MailMessage -From "sextonk@fauqhealth.org" -To ('sextonk@fauqhealth.org') -Subject "failed SQL Agent job(s) in last day" -body "Open the attachment to see the failed SQL tasks within the last day." -attachments c:\downloads\sqlAgentErrors.txt -SmtpServer "myMailServer.fauquierhospital.org"

}
6

I verified powershell remoting is enabled on the servers where my script is not working.

#This works: 
get-DbaAgentJob  -SqlInstance FAUQxxx01
#This does not: 
Find-DbaAgentJob  -isfailed -SqlInstance FAUQxxx01 -Since (Get-Date).AddDays(-10)

-Kevin

7

Are you geting any error ? or its just doesn’t show anything from 2008 Server.

8

no errors. It just doesn’t return anything.

-Kevin