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 state like offline, suspect, emergency etc. This management pack will help you to achieve this.
As we know that at present, Microsoft is providing to monitor SQL server and it’s databases through SQL server Agnostics management packs. But we have certain challenges where we need to do some customization in order to achieve few monitoring of the SQL database.
You can get the management pack from the below link.
Please find the details now this management pack works.
- Firstly, you have to change all the references in the MP according to the version of dependent MP in customer environment.
2. Secondly, make the below changes in the script according to the state the customer wants to monitor. In my case, I only want to exclude Offline mode.
$strSQLCommand = “SELECT name,d.state_desc from sys.databases d with (nolock)
LEFT OUTER JOIN sys.database_mirroring dm with (nolock) ON dm.database_id = d.database_id AND dm.mirroring_role = 2
where (d.state_desc = ‘Emergency’ or d.state_desc = ‘Suspect’ or d.state_desc = ‘Online’ or d.state_desc = ‘Recovery_pending’ or d.state_desc = ‘Recovering’ or
d.state_desc = ‘Restoring’ or d.state_desc = ‘Offline’) and name = ‘$DatabaseName'”
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 enabled for the databases (Custom SQLServer DataBaseStatus Monitor, is the monitor that you need to enable through Authoring Pane), you could see it in the HealthExplorer of DB Engine class:
5. Alert description would be like:
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.”)