A Good Monitoring Tool Should…

Part 6 of the Do It Yourself Database Monitoring Series

Want to take a moment here and tie some of this together. Next, I’ll start going into the architecture and show you just how easy this is.  The trick is with the database schema; which can’t be decided upon until we know how this is going to work.…I can picture you wondering how the previous installments of this series tie together.  They do.  Everything ties together, and our monitoring tool(s) effect the whole.   

How nice would it be to type in an instance name and have the tool reach out and store configuration settings, database names, and then give you the ability to add more information?  Cool, huh?  One stop shopping!  Oh, now that the instance name is in the tool, it is being monitored.

With the DIY monitoring we basically can do whatever we want.  Want an alert when there is a cluster failover?  Got it!  Want to know when an application isn’t connected anymore?  Got it!  Want to pull a filtered list of items from a log?  Can do that too!  You’ll be limited by your creativity, not by the tool. 

The types of monitoring matter because we will want to be able to incorporate as much of these types as possible.  Not saying we are going to, but that we will have the ability to if wanted.

 A good monitoring tool should:

  • Be lightweight, minimizing impact on applications and databases.
  • A place for one stop shopping. All information about the tool and the collected data is in one centralized location.
  • Log it’s activity.
  • Not require components to be installed on items being monitored.
  • Be dynamic.  For example, if a database is added or deleted the monitoring will adjust.  
  • Track changes to the databases and servers.
  • Archive, aggregate, and purge the data previously collected.
  • Provide a mechanism for alerting.
  • Have configurable alerting groups.
  • Place incidents from the alerts into an incident tracking application.
  • Have configurable and default thresholds for sending alerts.
  • Automate documentation
  • Allow for real time monitoring as well as scheduled automated monitoring
  • Not have to treat every database server and database the same.
  • Be used for trending and capacity planning.
  • Offer reporting.

The information gathered by the tool will be used for at least one of the following purposes.  Each type could, but not necessarily be handled differently.  These type definitions are needed when developing our database schema to store the metrics gathered by the tool.  All data/metrics will be collected the same way.  The post collection processes will have some variance.

  • Tracking changes - Used to log changes to the instances and databases.  Could be security, a database setting, adding/deleting a database, instance start time, etc.  
  • Current state – Metrics used for real time monitoring.   Data is not stored or saved.  I may later refer to these as Dashboard metrics.
  • Alerting – Data that meets or exceeds a threshold.
  • Documentation – data that is used for documentation purposes. Just off the top of my head I am thinking of editions, passwords, ports, IP addresses, licenses, domains, associated applications, database owners, contacts, maintenance windows, etc. 
  • Trending - Gathered for trending and forecasting purposes.  Examples of these include job run times, Growth rates, table and index space, space used in database files, page splits, etc.

The monitoring tool will help with management.  Doesn’t have to be just about ‘monitoring’.   Personally, I’d like to see this tool work on more than one RDBMS.  No need to be made specifically to one database engine.  In all honesty, this could work for monitoring in general.

I am going to be a little bummed if a company out there looks at this and takes some, or all ideas presented here.  It’s happened before.  Presenting this in good faith.

Here we go, going to start building next.  You’ll see why the SQL script tool was built too…stay tuned!