Central Management Server jobs for Monday morning ready report!

Question

Central Management Server jobs for Monday morning ready report!

WebTechie

SSChampion

Points: 11405
More actions
April 9, 2019 at 8:17 pm

#3070444
Hello,
As a DBA responsible for a lot of database servers, I'd like to create more proactive notices. I want to create a Monday Morning report that checks for things that have caused problems in the past.
1. Index jobs still running since it kicked on a 12:30 am.
2. ETL jobs that have been running for a couple of days.
3. AOG databases that are not synchronizing.
And others.
I want to create a few queries that will run each Monday morning and then put results in a table. I can then create an SSRS report and subscription that will advise the team if we are ready for business on Monday Mornings. If there is an issue, I can show it the report.
I've added some server instances into Central Management Server (CMS). I've create subfolders for DEV, QA, UAT, and Production.
Now, how do I create jobs that run queries against Central Management Server? Is this even possible? If it is, I am missing it.
Thanks.
- This topic was modified 5 years ago by WebTechie.
Things will work out. Get back up, change some parameters and recode.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

deep_trance_ Mr or Mrs. 500 Points: 529 More actions · Answer 1

Hi WebTechie,

I suggest to use DBATools, a very handy Powershell tool - you can import the modules in your management server and run the require commands.

You can use the tools to get the list of servers in your CMS and then run your required commands.

Example: this is what I use to get a list of failed agent jobs

$management = $env:COMPUTERNAME + '\SQL01'

$servers = Get-DbaCmsRegServer -SqlInstance $management | Select-Object -Unique -ExpandProperty ServerName

$dt = foreach($server in $servers){

Find-DbaAgentJob -SqlInstance $server -IsFailed -Since (Get-Date).AddDays(-7) | Where Category -ne "Report Server" | select-object SqlInstance, Name, Enabled, CurrentRunStatus, LastRunDate, LastRunOutcome

}

$dt | Sort-Object {$_."LastRunDate" -as [DateTime]} -Descending | Format-Table -Autosize

Using the tool you can output the results to a table where you can then using SSRS to create the reports.

I find SSRS outdated and slow so I just output it to a file and send the report via email.

WebTechie SSChampion Points: 11405 More actions · Answer 2

Thank you. I've never used DBA tools. I did find a master server which can run things on target servers. This sounds interesting. I can run multiple check queries and then store the information in one database/table. I can then report on that.

Another solution that came up was to create an SSIS project to hit all of the databases and do my checks. Save the information and then create report.

Thanks for the suggestion though.

Things will work out. Get back up, change some parameters and recode.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Here's what I suggest. Create loose coupling, since your central server could have issues.

Gather the info you want and have that stored on each machine. If you want to know at 5:00am what's still running, have a job on every machine gather this info and store it. Then have your central job query each machine, gather the info from local storage, and compile it for you.

Whether you use dbatools, SSIS, or something else, loose coupling is a better way. Especially since you're now writing monitoring software. There are lots of little things that go wrong, including one error hitting a machine that prevents every subsequent query from running. Do yourself a favor, and both have each machine gather this, and also look at monitoring software. Some, like SQL Monitor, will let you write csutom alerts and reports.

Disclosure: I work for Redgate Software