Video Screencast Help

MS 7 - Monitor Pack for SQL (2005/2008) - Updated July 2009

Created: 01 Jul 2009 • Updated: 09 Nov 2009 | 10 comments
Joseph_Carson's picture
0 0 Votes
Login to vote

Now update to include Instance Name for Database State:

Updated Counters:
Is Server Clustered
Database Locks for Master
Database State with Alert
Database Read/Write Errors
Process Name by Database(Top 10 Processes)
SQL Process Count(Top 10 Processes)
Blocked Process Count

Existing Counters:
Monitors all Critical NT Events in the Application Log for MSSQLSERVER Source
Monitor Database State for SUSPECT/EMERGENCY/Offline (requires Monitor Windows Configuration to have SQL User configured)
SQL Percent Log used > 80%
SQL Server Buffer Manager Cache Size Ratio > 98
SQL DB Size > 10GB (This can be easily changed)
SQL Server Process and Remediation of Service if not running
SQL Server Service and Remediation of Service if not running (Other services like Reporting and Analytical Service can be easily added)
SQL T_Log > 1GB
SQL Server Batch Request/sec > 1000
SQL Server Compilation Requests/sec > 100
SQL Reporting Service Status with Auto Remediation
SQL Analysis Service Status with Auto Remediation
SQL Server Locks
SQL Server Memory Pages/Sec
SQL Server User Connections
SQL Server Access Methods Object: Full Scans/Sec
SQL Server Backup Device Object: Device Object Throughput/Sec
SQL Server Buffer Manager: Page Life Expectancy < 200
SQL Server Locks Object: Average Wait Time in ms > 500
SQL Server Memory Pages/Sec
SQL Server Page Splits > 200
SQL Server User Connections

Comments 10 CommentsJump to latest comment

Joseph_Carson's picture

Hello All,

Please note that this Monitor Pack contains Auto Remediation and if detects that any of the SQL Services are not running it will start them.  Please be aware that if you are running a Clustered Environment that you may want to have two Monitor Packs one for the Active Server with Auto Remediation and one without applied to the Standby Server or you may want to remove the Remediation completely.

Many Thanks for your attention. 

0
Login to vote
Joseph_Carson's picture

Updated with some minor Rule changes.

0
Login to vote
Coolio2's picture

Hello Joesph

Does this SQL Pack also Support SQL 2008 R2?

0
Login to vote
Joseph_Carson's picture

This Monitor Pack should work ok with SQL 2008 R2.  If anyone has any issues with this pack on SQL 2008 R2 please let me know.

Kind Regards,

0
Login to vote
MortenT_'s picture

Hi!

What is the best way of adapting this pack to support monitoring of multiple MSSQL instances? From what I can see, this is not supported due to the structure of the performance counters used by the metrics. Are there any way of solving this without making custom metrics and rules for each named instance on every MS SQL server?

Thanks!

Best regards

 

0
Login to vote
Joseph_Carson's picture

Right now this is something that is not as quite dynamic as i would like in Monitor Solution.  To do this you simply need to clone the Metric and modify it for each Instance and then add it to a rule.

So no way to do this without making custom metrics, though this will be getting easier in future releases.

Kind Regards,

0
Login to vote
dlopes's picture

Great monitor pack Joseph!

I only got a problem, when i monitor a cluster SQL Server i always get one node with Critical errors, is it possible to integrate that with cluster service? To monitor node changes and alert if active node changed, but not to display a critical error if the other node is up.

Regards,

Daniel Oliveira

Daniel Lopes de Oliveira
Technical Team Leader - Endpoint Management & Mobility

0
Login to vote
Joseph_Carson's picture

Hi Daniel,

You will need to use two policies for that, one policy for Active Node and one policy for Inactive Node and to do this you need to use the detection rules that will detect which server is the active server.

Best Regards,

0
Login to vote
donp213's picture

Actually, I would not suggest a detection rule as the default interval for detection rules is 1 hour; meaning you might have an hour of inaccurate monitoring (of course you can change this).  I monitor several clusters (Exchange, SQL, and others) and simply use a powershell command to return a 1 or a 0, indicating if this node is active (script below).  Therefore, in my Monitoring Rule, I have two metrics defined, e.g.

  1. If  “SQL Cluster” Group is active on this node
  2. And If SQL Server Service is not running

powershell -noprofile -command "$x = Get-WmiObject -namespace root\mscluster -class MSCluster_NodeToActiveGroup | Where {$_.Partcomponent.substring(30, 11)-eq 'SQL Cluster'} | where {$_.groupcomponent.substring(21, 12) -eq (gc env:computername)} | measure-object; write-host $x.count"

+3
Login to vote
dlopes's picture

Donp213,

I really appreciate all the information, but as i´m rather new to monitor solution, do you mind sending me some samples? So i can try to replicate those metrics?

 

 

Daniel Lopes de Oliveira
Technical Team Leader - Endpoint Management & Mobility

0
Login to vote