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
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?
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.
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"
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,
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
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,
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
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.
Hello Joesph
Does this SQL Pack also Support SQL 2008 R2?