?
Solved

Monitor MS SQL server health for non-DBA - SQL monitoring tools?

Posted on 2013-06-04
11
Medium Priority
?
643 Views
Last Modified: 2014-08-02
Hello,

I have a couple of SQL servers I need to take care of. I'm no DBA, but have done well doing the basics for many years.

One thing that does seem to catch me is that if there is a problem with a database, I usually don't figure it out until someone tells me there is something wrong. Often by that time there has been something wrong for a while so restoring from a backup isn't a great option.

While I try to be religious about looking through logs, it's tough sometimes to pick the problem through all the noise.

What are some good techniques and easy to use (hopefully) tools that will do a reasonable job of letting me know when there is a real problem with something, without me having to scour logs and sort through tons of irrelevant notifications?

Thanks
0
Comment
Question by:willp2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39219920
Depends on what kind of problems you need to trap.
0
 
LVL 1

Author Comment

by:willp2
ID: 39219930
In this case, specifically problems with the databases themselves. So backups or maintenance not completing correctly because of problems and to a lesser extent machine problems, like problems with Windows or the VM hardware its running on.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39219934
0
Need protection from advanced malware attacks?

Look no further than WatchGuard's Total Security Suite, providing defense in depth against today's most headlining attacks like Petya 2.0 and WannaCry. Keep your organization out of the news with protection from known and unknown threats.

 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39219961
"... specifically problems with the databases themselves."
What problems specifically?  How are they discovered?

Are we talking problems with indexes?  Data corruption?  Backups not completing?  

You can schedule some routine maintenance under Management -> Maintenance Plans, but there's no way to know if that will help since we don't know what the problem is.
0
 
LVL 1

Author Comment

by:willp2
ID: 39220034
Sorry for being vague as I'm not talking about something specific exactly.

We do run maintenance and backups. In the most recent case of a database issues, maintenance started kicking errors after a server reboot followed later by problems backing up the database. In retrospect it was easy to spot, but with all the info emails I get about various things, it got lost in the mix.

I guess I'm hoping to get something in place that basically says "Hey Dumb Ass, this is important!) without telling me that everything is important.

So I was hoping there was some reasonable SQL monitoring tool that will look at things like maintenance, backups and overall health and let me know if it looks like something is out of whack.
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 39220064
You can have the maintenance plan notify you if it fails at any step.  You can also look at the Maintenance Logs to see if maintenance is having problems.  Lastly, if your databases backup, they should be okay, so check those periodically.

The tools listed by [lcohan] may be of some use, but I've never used them so don't know.
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39220157
For SQL Jobs only you could use "Operations Manager"
http://blogs.technet.com/b/kevinholman/archive/2011/08/05/how-to-monitor-sql-agent-jobs-using-the-sql-management-pack-and-opsmgr.aspx


If you do not have too many servers to manage you could use undocumented proc SP_GET_COMPOSITE_JOB_INFO like in example below:

http://blog.sqltechie.com/2009/03/job-monitoring-part-1.html
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1000 total points
ID: 39220167
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39220228
I'm going to +1 lcohan's suggestion about using SCOM (the artist formerly known as MOM).  

I will just add one warning: if you are wanting to get tuning information out of the monitoring solution, MOM (I refuse to call it SCOM) doesn't have much right out of the gate.  Even with the SQL Server management pack added, there is still a fair bit of work that you must do to bring her up to date with SQL monitoring.  Most people create a custom management pack to pick up a lot of the metrics that MOM is missing.  For example, a lot of metrics should really be compared as a function of batches/sec, which doesn't exist in MOM's SQL Server Management Pack - goofy, I know.

The stuff that it does have, as mentioned above, is the SQL Server agent job monitors, as well as some other straightforward ones - % space used within the data/log files, memory/cpu used by sql server instance(s), etc.  It has those and it you can lay it all out in a pretty dash too.
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 1000 total points
ID: 39220416
Use a monitoring tool.  My preference goes for Redgate SQL Monitor.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Introducing Priority Question, our latest feature.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question