SQL Server cmdlets

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

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'

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:

https://blog.netnerds.net/2016/07/new-powershell-commands-from-the-sql-server-team/

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.
  • 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.

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"

}

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

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

no errors. It just doesn’t return anything.

-Kevin