SCOM: SQL Browser Service Monitoring MPs

I’m currently working with a client on an Operations Manager engagement. A few days ago, we were discussing their monitoring requirements for SQL Server, which included monitoring the status of the SQL Browser service. I didn’t know for certain off the top of my head whether or not the official SQL management packs included monitoring of the browser service. Knowing that the SQL management packs are pretty thorough, I assumed the SQL Browser service monitor would be built in. I committed to double-checking just in case that assumption was wrong, though. Much to my surprise, upon checking the management pack guides and the Authoring workspace in SCOM, I confirmed that the SQL management packs do not contain any monitors targeting the SQL Browser service!

 

Out of Box SQL 2012 DB Engine Monitors - no SQL Browser service monitor

Out of Box SQL 2012 Service Monitors

 

Even more to my surprise, running a few Bing searches didn’t turn up any results on the subject; while it is true that the SQL Browser service isn’t something clients often mention the desire to monitor, I find it hard to believe no one has ever had to configure monitoring for the SQL Browser service before. Surely, I am not the first, and I doubt I’ll be the last; as such, I decided to share my solution and save the next person needing this monitor the time.

 

Fortunately, this wasn’t a challenging requirement to meet. To do so, I created four custom management packs – one for each version of SQL from 2005 to 2014. Each of these management packs contain a Windows service monitor targeting the SQL <version> DB Engine class, a console task designed to start the SQL Browser service (also targeted at the SQL <version> DB Engine class), and a recovery task to automatically restart the service if it fails. The recovery task is disabled by default, but can be enabled via an override. The monitor is configured to only monitor instances of the SQL Browser service which are set to automatically start. Additionally, the monitor also contains a knowledge article with some basic troubleshooting information borrowed from the out-of-box SQL Windows service monitor.

 

Updated SQL 2012 DB Engine Monitors, now with a SQL Browser service monitor

SQL 2012 Service Monitors – With Browser Service Monitor

 

The decision to make multiple version-specific management packs stems from the structure of the official SQL management packs. As each version of SQL has its own set of management packs, and thus are not all required to be installed in order to perform monitoring of a specific SQL version, I wanted to ensure that my custom management packs could be implemented in the same way. If the SQL Browser service only needs to be monitored on SQL 2012 instances, then the SQL 2012 management pack is the only one needing to be imported. In this way, compatibility and flexibility are both maximized.

 

One important point to note is that, unlike the other SQL services, the SQL Browser service is not instance-specific. Instead, all SQL instances on a server share a common SQL Browser service. My custom monitor targets individual SQL instances rather than servers hosting SQL, though. What this means is that, should the SQL Browser service  on a server stop, all SQL instances on that server will go unhealthy and generate an alert. This decision was made for two reasons: First, the class structure in the official SQL management packs don’t contain a good class to target which would identify the server hosting the instances. Instead, the SQL <version> DB Engine class (analogous to a single SQL instance) is the top-level class for SQL instances. Second, while I could have created a new class and targeted the monitor there, it made more sense to me to leave it tied to each instance. This is because if the SQL Browser service’s functionality is needed for an instance or database to serve its purpose, we want that instance to go unhealthy when the service stops. While the SQL Browser service may not always be needed, there’s not an easy programmatic way I am aware of to dynamically determine which instances would need the service and thus where to create relationships. In the end, it is easier to monitor the service on all instances and simply disable the monitor for SQL instances for which the monitoring is unnecessary. Additionally, if the service is not needed, it will likely be set to “Manual” rather than “Automatic”, resulting in the monitor ignoring it anyway.

 

I’ve attempted to publish the management packs as a contribution to the TechNet Gallery, but as of this writing, the site keeps erring whenever I try to save. Therefore, I’ve included the download link below. Once I get the TechNet Gallery contribution published, I’ll update this page. All are welcome to download them and evaluate them in Operations Manager. Please feel free to post any questions here or over at the TechNet site and I’ll get back to you as soon as possible. The link follows:

 

SQL Server Browser Service Addendums.zip

 

Standard disclaimer: These management packs are not provided by Microsoft and are thus not supported by Microsoft. Always test any management packs you find on the internet in your Test or Development environment before using in Production.

By |2016-04-12T11:06:26+00:00April 15th, 2015|SCOM|0 Comments

About the Author:

Solutions Architect / Team Lead – Model Technology Solutions Gabriel specializes in SCOM, SCSM, Orchestrator, and PowerShell, along with experience in several Azure technologies and other server roles. Gabriel has spoken at several regional conferences and user group meetings about leveraging Microsoft’s datacenter and automation technologies to solve specific problems. Gabriel’s attention to detail and relentless pursuit of perfection may one day make his brain explode from information overload. Hopefully they will have a cure before then.

Model Technology

Let us help you get your end point and data center strategy on cruise control!  Ask about our Calibration Assessment.

CONTACT US

  • 12125 Woodcrest Executive Drive, Ste. 204 Creve Coeur, MO 63141
  • (314) 254-4138
  • sales@model-technology.com

RECENT TWEETS