SCOM: SQL Database Monitoring

This management pack is developed to monitor database health state depending on the state of the database. You might be having a requirement to exclude any of the database state like offline, suspect, emergency etc. This management pack will help you to achieve that.

As we all know that at present, Microsoft is providing to monitor SQL server and it’s databases through SQL server Agnostics management packs. But we have some certain challenges where we need to do some customization in order to achieve few of our custom monitoring of the SQL databases.

You can download the management pack from the below link.

https://github.com/souravmahato7/Management-Packs/blob/master/Microsoft.SQLServer.DatabaseStatus.ManagementPack.xml

Please find the details how this management pack works.

1. Firstly, you have to change all the references in the MP according to the version of dependent MP in your environment.

2. Secondly, you need make changes in the XML according to the state you want to monitor. In my case, I only want to exclude Offline mode.

3. MP is disabled by default. (You have to enable the Monitor, but I’d suggest to enable it for some test servers first)

4. Once MP is imported and enable it for the databases (Custom SQLServer DataBaseStatus Monitor, is the monitor that you need to enable through Authoring Pane), you could see the HealthExplorer of DB Engine class as below:

5. Alert description would be like as below:

6. Alert will be raised if 2 sample value having the bad value ( you could increase or decrease the time interval and number of sample through override)
.

7. There is logging available as well, inside the script but by default those are disabled. If you wish to enable them , then you just need to remove the # key from the below and then you would start seeing output in OperationsManager event logs:

#$momapi.LogScriptEvent($ComputerName,$EventID,0,”`n $DatabaseName is starting. `n Running as ($whoami).”)

#$momapi.LogScriptEvent($ScriptName,$EventID,0,”SQL Server $ComputerName and Instance $Instancename having $DatabaseName status is $Status”)

#$momapi.LogScriptEvent($ScriptName,$EventID,0,”SQL Server $SQLServer and Instance $Instancename having $DatabaseName status is $Status”)

#$momapi.LogScriptEvent($ScriptName,$EventID,0,”`n Script Completed. `n Script Runtime: ($ScriptTime) seconds.”)

I hope this will help.

disclaimer
In this blog post, I have shared a possible solution for a problem that I encountered in my work. However, I want to make it clear that this solution is not officially supported by Microsoft, and it may have some risks or side effects that I am not aware of. Therefore, use this solution at your own discretion and responsibility.