Need help on developing SQL Servers health check dashboad to find problematic instances quickly!

• shamshad.ali

  Hall of Fame

  Points: 3850

  More actions

  February 14, 2018 at 10:53 pm

  #410245

  I need to find following to monitor several sql servers from a dashboard. I will do it manually because there is no such freeware good tool (If any let me know please?).
  List of items in Dashboad.

  1- Disk space on SQL Server
  2- Backup verification or find failed backups
  3- Performance counters
  4- Deadlocks if occured
  5- Bottleneck - find such worst queries
  6- Index missing
  7- Defragmentation on index
  8- Database log file status full etc ...
  9- Which jobs failed
  10- Which security threat exists like who is sysadmin
  11- WHich user changed pwd
  12- who modified the job/schedule

  If there is anything else I am missing here please add and share its query too, to add results in dashboad reporting tables.
  I will query these all on each server and record results in specific reporting history tables, Then make a dynamic page (Dashboad) from where i can monitor the results based on my selection criteria. Like (Server IP, Date, Most critical alert if any, range of items in selection criteria etc.)
  I also need a list of performance counters that are quired to monitor server health. disk/memory/processor (peak consumption of resources) making performance slow and identify and pin point what is reason behind this - I don't know how to find these counters which one to select and how frequently to collect.
  Review in short:
  1- Add/ update above list of monitoring items (anything incorrect or missing let me know please).
  2- If there is any freeware tool please share.
  3- Need list of counters and their specific namespace/object and their value (range), what to check in their values <Range between ?>  to collect data remotely, how. WMI ? how to?
  4- How the dashboad should be?

  Agreed! Its a time consuming task, I do not know from where to start thats why I am here to get feedback from experienced professionals for their guidance which may keep me on right track.

  Regards,

  Shamshad Ali
  shamshad.ali.khatri@gmail.com

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  February 14, 2018 at 11:02 pm

  #1979870

  Steve Stedman has a great free tool for monitoring SQL Server.

  Check out Brent Ozar's sp_blitz(whatever) sprocs.

  Check out Glenn Berry's Diagnostic Scripts.

  sp_whoisactive

  Outside of that, what you ask goes WAY beyond free forum help.

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• HanShi

  SSC-Dedicated

  Points: 33510

  More actions

  February 15, 2018 at 7:49 am

  #1979904

  shamshad.ali - Wednesday, February 14, 2018 10:53 PM

  ...
  Agreed! Its a time consuming task, I do not know from where to start thats why I am here to get feedback from experienced professionals for their guidance which may keep me on right track.
  ...

  Start with just a single thing. Research the basics of that topic to get a good understanding and search for a (basic) script to get the data you want. For most (if not all) you can rather easy find a script that can be use as a starting point. Rebuild that script to your specific needs. Test and tweak until you're fine with the results on different instances. Create a process (agent job, powershell, etc.) to execute the script periodically and generate an alert when the result falls outside your set boundaries. Then repeat these steps for the next thing/topic and add them to your process.
  Instead of or in addition to generating an alert you can build a report (like in SSRS) to show the results of the query's and/or alerts.

  Basically: just start small - one topic at a time - use what others already have created as a starting point. Build, test and tweak from there...

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **