Dynamic SQL Computer Group with Health Service Watcher Objects

The SQL 2014 Management Packs have fundamentally changed the way that SQL Server classes are scoped. This has created challenges for anyone who has scoped rules and monitors at the SQLServer.DBEngine class as that does not include SQL Server 2014 DB Engines. Likewise SqlServer.ServerRole does not include SQL Server 2014 roles.

The Microsoft.SQLServer.Library MP defines:

<ClassType ID="Microsoft.SQLServer.ServerRole" Accessibility="Public" Base="Windows!Microsoft.Windows.ComputerRole" Hosted="true" Abstract="true" Singleton="false">

   <ClassType ID="Microsoft.SQLServer.DBEngine" Accessibility="Public" Base="Microsoft.SQLServer.ServerRole" Hosted="true" Abstract="false" Singleton="false">

 

The Microsoft.SQLServer.2012.Discovery MP defines:

   <ClassType ID="Microsoft.SQLServer.2012.DBEngine" Accessibility="Public" Base=" Microsoft.SQLServer.Library!Microsoft.SQLServer.DBEngine" Hosted="true" Abstract="false" />

 

The Microsoft.SQLServer.2014.Discovery MP then starts off anew:

<ClassType ID="Microsoft.SQLServer.2014.ServerRole" Accessibility="Public" Base="Windows!Microsoft.Windows.ComputerRole" Hosted="true" Abstract="true" Singleton="false">

  <ClassType ID="Microsoft.SQLServer.2014.DBEngine" Accessibility="Public" Base="Microsoft.SQLServer.2014.ServerRole" Hosted="true" Abstract="false">

Along with the changes, there is also a new Management Pack - Microsoft.SQLServer.Generic.Presentation - that defines a group of SQL Server Computers which does include all versions of SQL Server.

A question has come up recently about how to create a group of Health Service Watchers for all SQL Servers and also to create a group that contains the Computer Objects and Health Service Watcher objects for all SQL Servers (including SQL 2014).

Group of Health Service Watchers for all SQL Servers

This one is fairly straight forward and follows the process that Kevin Holman explains here. All that you need to do is update the Group Name to Microsoft.SQLServer.Generic.Presentation.ComputersGroup.

 

An example Management Pack for this can be downloaded here.

Group of Computer Objects and Health Service Watcher objects for all SQL Servers

This is a little more complicated as I haven't been able to find a role that covers both SQL 2014 and versions of SQL earlier than SQL 2014. To get around this I have created a dynamic group with 2 membership rules - once scoped to Microsoft.SQLServer.ServerRole (which covers SQL 2005 \ 2008 \ 2012) and one scoped to Microsoft.SQLServer.2014.ServerRole (which covers SQL 2014).

It basically builds on Tyang's excellent post here.

 

An example Management Pack for this can be downloaded here.

Disclaimer:
All information on this blog is provided on an as-is basis with no warranties and for informational purposes only. Use at your own risk.

The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of my employer.