I want to rationalise SQL monitoring to parallel (functionally) the monitoring of our non-SQl databases. Using that approach, all jobs of a specific category are checked from a central server, and an email is sent in which the Subject indicates whether the email contains error details (so needs to be read) or whether the monitoring was successful and the email can be filed.
The existing SQL Server monitoring is very "out of the box", so emails get sent when jobs fail. We have twenty something servers and two hundred (?) databases and many more jobs, so success notification is not something we can reliably check. The approach also fails to cater for email ceasing to work, Agents not being started, jobs being disabled and not re-enabled, and more I am sure.
I have considered several options and favour using linked servers from a central server to perform monitoring. The monitoring jobs would use local tables to target queries at a number of remote servers, at the end of which an email would be sent to indicate alarm or success. This would allow us to expect one email per day (or reporting period) per category, categories to include "backups", "space monitoring", "standby status" and perhaps one or two more generic.
I have spoken to various product vendors and Microsoft consultants and the impression is that we will not find an out the box product but with some hard work, there are suitable vehicles out there (e. g. MOM) through which we can implement our design.
Before I embark on this approach I'd like to ask the more experienced SQL Server community whether there is a suitable alternative, or whether they have experience of an out the box product which does this for the DBA.
I would very much appreciate any feedback.