Monitor SQL Server

Question

I am managing a very important SQL Server 2008 R2 database. I need a solution that notifies me through email if the performance of the database server is getting bad.

I am imagining the following scenarios:

• Server is simply offline -> email "Server offline"
• Executing a standard test query takes 2x more time than yesterday -> email "Server performance degraded"
• No connections are open -> email "Server overloaded with connections"
• Average CPU usage is above 80% in the last 3 hours -> email "Server very busy"
• No free disk space -> email "Server out of disk space"

I have considered Performance Monitor, SQL Server Activity Monitor and looked through all the other solutions on the web but none of them are as simple and user-friendly as I'd like them to be. If you have no other suggestion than the ones above and in case I have to implement a tool like this how would I do it? Windows service that queries the database in certain intervals and sends an email if the response time gets worse?

Answer 1

Here are a few that work quite well. Not sure they'll satisfy 100% of the cases you want to monitor (such as depleted connection pool). In those cases you might need to just bake something yourself and add it into the monitoring solution, or consider using a blend of products.

• Quest Foglight for SQL Server
• Quest Spotlight on SQL Server
• RedGate SQL Monitor
• Idera's SQL Diagnostic Manager

EDIT
In response to your comment (although I've never used them). Some free tools include

• Spiceworks
• Idera's SQL Check
• ManageEngine SQL Health Monitor

There are a ton of them out there. I just Googled "free sql server monitoring" and these were the top few.

If none of those, or anything else, is fitting the bill then you'll need to put some scripts together that give you the output you want and set them up in SQL Agent jobs or scheduled tasks. Powershell is great for this, especially when utilizing the SQL Server Powershell cmdlets.

If you have development experience you can also write an app (maybe as a Windows service) that will perform your tasks. .NET has plenty of classes and API's that will enable you to get the information you're looking for. You can use the SQL Server SMO classes to get information directly from SQL Server in a .NET app.

Answer 2

SCOM is nice and obviously has a MP for SQL. I'd check it out.

http://technet.microsoft.com/en-us/library/dd767429.aspx

Answer 3

I've used a bunch of these tools over the years. The main thing you need to focus on is exactly what you want out of the tool. For example, if you're looking for basic monitoring and alerting with pretty much everything configured for you out of the box, almost any of the tools will do a good job. I personally, because I work for them, prefer Red Gate's SQL Monitor. I think our interface is better and we get you going much quicker than some of the competition.

That said, if you need something that monitors thousands of servers, you might want to look at Microsoft's SCOM. If you're looking for very specific, detailed, performance metrics, but don't care so much about monitoring & alerting, you might want to check out Confio Ignite.

It's the details of what you want to do that make the difference in which tool will work better for you.