Vision: SQL Homeostasis

I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy. I needed a query (DMV) to get details on what alerts are setup on each server. And the result is the following query that I will run as a multi-server query. SELECT a.[id] [alert_id], a.[name] [alert_name], a.[enabled] [is_alert_enabled], o.[enabled] [is_operator_enabled], o.[email_address] [email_address], o.[pager_address] [pager_address], o.[netsend_address] [netsend_address], j.[name] [job_name], a.[event_source] [alert_event_source], a.[event_category_id] [alert_event_category_id], sc.[name] [alert_category_name], CASE sc.[category_class] WHEN 1 THEN 'JOB' WHEN 2 THEN 'ALERT' ...

Do you think you need to use commercial tools  like SQL Diagnostic Manager, SQL Sentry, Spotlight etc.. ?  Or do you think the built-in features are enough? There are complelling arguments in favor of each. In this blog I hope to touch on every built-in feature to gather the performance information, some give  real-time overviews of your databases whereas others provide detailed, on-demand data on SQL Server health and performance. I hope you learn something new. Using T-SQL There are several ways to use T-SQL queries to monitor SQL Server, using dynamic management views, built-in functions, stored procedures, or system data collections… Dynamic management views DMVs are virtual tables that you can query on adhoc or as part of your custom, automated monitoring.   Some shows sql server state at a particular instant whereas others, especially those that deal with performance counters, measures values on a regular internal and show you the difference between two samples...

Here is a DMV script to check whether a or any database has the auditing configured and running, along with few other useful attributes if it does. Few things to keep in mind: Auditing is available from SQL version 2008 and up.... Until very recently (SQL version 2016 and up), granual (object level) auditing at the database level is available only on Enterprise edition.  Although you could still use server level auditing to setup auditing at the database level So keeping that in mind, I wrote this in a way to be able to execute as a multi-server query either using CMS or just a bunch of locally registered servers. If you are running this as a multi-server script, you should turn on the "Merge Results" setting in the SSMS. Finally, if you feel that this script is unncessarily complicated, its because it is written in a way so that it can be executed against any version and editon of sql server, whether it supports auditing or not! Hope you find this use and please feel free to...