Share via

No Alert from SQL MP when clustered services go down

  • Article

I recently ran into the following issue:

The SQL Server Management Pack has several monitors to monitor various SQL Services:

image

However, on a SQL Cluster, if one of these services is taken offline:

image

We don't get an alert from SQL (we do get a cluster alert saying that a cluster is offline), and the monitor stays healthy:

image

This happens because:

  1. By default, a Basic Service Monitor will only monitor services whose startup type is Automatic
  2. On a Clustered SQL instance, the service startup type will be set to Manual

To fix this, you simply need to set the "Alert only if startup type is automatic" override to "False" for the Clustered SQL Instances

image

image

image

 

Now, the health state is changed when the service is down and we are properly alerted:

image

 

NOTES:

  • The SQL Monitors affected by this are:

    • Microsoft.SQLServer.2008.DBEngine.ServiceMonitor
    • Microsoft.SQLServer.2008.ReportingServices.ServiceMonitor
    • Microsoft.SQLServer.2008.AnalysisServices.ServiceMonitor
    • Microsoft.SQLServer.2008.IntegrationServices.ServiceMonitor
    • Microsoft.SQLServer.2008.DBEngine.FullTextSearchServiceMonitor
    • Microsoft.SQLServer.2008.Agent.ServiceMonitor
    • Microsoft.SQLServer.2005.DBEngine.ServiceMonitor
    • Microsoft.SQLServer.2005.ReportingServices.ServiceMonitor
    • Microsoft.SQLServer.2005.AnalysisServices.ServiceMonitor
    • Microsoft.SQLServer.2005.IntegrationServices.ServiceMonitor
    • Microsoft.SQLServer.2005.DBEngine.FullTextSearchServiceMonitor
    • Microsoft.SQLServer.2005.Agent.ServiceMonitor

  • You must have at least version 6.0.6441.0 of the SQL Server Management Pack for this to work.  The latest version is 6.0.6460.0 and can be downloaded here.

  • If you manually create a Basic Service Monitor in the OpsMgr console, the "Alert only if startup type is automatic" override will not work.  You'll need to export the MP and edit the XML to add <CheckStartupType>true</CheckStartupType> to the monitor configuration (this is already done in the latest SQL MP):

Before change (not working):

<ComputerName>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkNam
e$</ComputerName>
<ServiceName>Messenger</ServiceName>
</Configuration>
</UnitMonitor>

After change (working):

<ComputerName>$Target/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkNam
e$</ComputerName>
<ServiceName>Messenger</ServiceName>
<CheckStartupType>true</CheckStartupType>
</Configuration>
</UnitMonitor>

Comments

  • Anonymous
    January 01, 2003
    Interesting....just checked the SQL 2000 MP myself and CheckStartupType is not there.  So, for SQL 2000, you would need to do the following:
  1. Disable the current service monitor in the SQL 2000 MP
  2. Create a new service monitor in your custom MP.
  3. Modify the XML to include "<CheckStartupType>true</CheckStartupType>" I haven't verified this yet, but I believe that if you are using R2, the "<CheckStartupType>true</CheckStartupType>" will already be in the XML when you create a service monitor (of course, that doesn't fix the current SQL 2000 MP).

  • Anonymous
    January 01, 2003
    You'll only want to target the SQL Cluster with this override....so it will only apply to the current active node of the cluster.  When setting the override, select "for a specific object of type: SQL 2005 DB Engine" and select your SQL cluster...or, create a group of the clustered DB Engines and target that group.  Again, the key is to target only the clustered DB Engines.

  • Anonymous
    January 01, 2003
    Can you verify how the override is set?  Are you targeting the override at "SQL 2005 DB Engine" and selecting ONLY the SQL Clusters, and not the physical cluster nodes?

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    February 23, 2009
    The comment has been removed

  • Anonymous
    April 21, 2009
    The comment has been removed

  • Anonymous
    September 09, 2009
    I have the same problem with SQL 2000 cluster. I override the value from blank to "false", but the monitor still not work as excepted. Any suggestion?

  • Anonymous
    September 09, 2009
    I have checked SQL 2000 MP and found: there is no such state "<CheckStartupType>true</CheckStartupType>" then how to fix this problem on SQL 2000 cluster?

  • Anonymous
    September 14, 2009
    I will try it and let you know the result. Thanks!

  • Anonymous
    September 15, 2009
    The comment has been removed

  • Anonymous
    September 15, 2009
    I have tried input "$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$" into the service name field as the SQL MP does. But got an error said "Unknown alias [SQL]" How to define such alias in my custom MP? Thanks!

  • Anonymous
    September 15, 2009
    seems I need to define those alias in my custom MP. How to do it?

  • Anonymous
    September 15, 2009
    oh! really a good way!! Another small question: what is the difference between "SQL Server Windows Service Name" and "SQL Server Windows Service Name If Clustered Resource"? Thanks Again!!

  • Anonymous
    July 29, 2015
    The comment has been removed