SQL MP Extensions – Unused Databases and More

Figuring out (monitoring) unused SQL databases using SCOM was one of the common requests I got from DBAs of enterprise customers. I authored a management pack (SQL MP Extensions – download available) to  address this feature and also took this an opportunity to provide a tutorial for MP authoring with a real life scenario.

I hopefully will be updating this MP with new features and publish the new versions monthly within this post. The MP Authoring tutorial will be a series of different posts explaining the elements of the MP starting with this one.

MP Features (7.0.2.1)

• Monitors / Alerts / Reports Unused Databases based on Index Usages (only for 2008 databases currently , next version)

• Scripts in this MP support CookDown (only one script per SQL instance)
• Uses existing SQL MP Profile (SQL Server Monitoring Account) so no extra security configuration required (if there’s already one)
• Well documentation via <KnowledgeArticles> (just check the Product Knowledge of the Rule / Monitor / Reports etc. Everything needed is there).

• Noise is pre-filtered (only necessary objects are enabled)

• Ready to Run Reports included (objects are pre-filtered)

Support Statement

• There is no support for this MP, test and use under own risk
• I only will test this mp against Operations Manager 2012 test environment therefore the schema is 2.0, for 2007 users I reccomend to move to 2012

Important Notes

The “Database Usage” Monitor is based on Index usages (kept on masterdb of every instance) and these counts are reset every time the SQL service starts. So this monitor will resetting to green on every server/service restart but if the database is unused again for the same days (default 7) the state will go back to RED. This is not a big deal and does not break figuring out Unused Databases but only worth to notice to inform about the behavior.

Requirements

• System Center Monitoring Pack for SQL Server; https://www.microsoft.com/en-us/download/details.aspx?id=10631 has to be installed and configured (please check SQL MP guide)
• Currently Workflows are targeted to only SQL Server 2008 Databases.

How to Use the MP

• Before importing the MP Ensure that System Center Monitoring Pack for SQL server is installed and configured
• Please create a new Management Pack for Overrides and save the Overrides in this New Overrides Management Pack
• Import the MP using any method desired
• Change the thresholds / Alerting / Intervals as required after checking “Workflows in This MP” table
• Please check Product Knowledges, the behavior of the workflows/reports are explained in detail.

Workflows in This MP

Following is the table for the Workflows included in this MP and their defaults

SQL.MPExtensions.mp