February 14, 2018 at 10:53 pm
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
February 14, 2018 at 11:02 pm
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
February 15, 2018 at 7:49 am
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...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply