SQL MP – SQL 2012 DB Engine group does not contain all SQL servers


Ian wrote about this issue back in September:  http://ianblythmanagement.wordpress.com/2013/09/12/sql-server-2012-db-engine-group-problem/

Essentially – SCOM discovers the SQL Version from a registry key that SQL places.  The problem arises that the SQL version in that key uses a different SQL version that what is considered typical. 

SQL build versions are visible here:  http://sqlserverbuilds.blogspot.com/

When you apply SQL 2012 SP1 to SQL 2012, this updates the registry from 11.0.xxxx.x to 11.1.xxx.x as seen below in SCOM Discovered inventory:



The issue is that the group “SQL Server 2012 DB Engine Group” is hard coded to 11.0.* as seen below:



I wrote a quick MP that contains a new group population discovery set to “11.*” along with an override to disable the built in group.  The XML is visible below:


<ManagementPack ContentReadable="true" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <Manifest> <Identity> <ID>Microsoft.SQLServer.2012.Discovery.Addendum</ID> <Version></Version> </Identity> <Name>Microsoft.SQLServer.2012.Discovery.Addendum</Name> <References> <Reference Alias="SQL2012Disc"> <ID>Microsoft.SQLServer.2012.Discovery</ID> <Version></Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="SQL"> <ID>Microsoft.SQLServer.Library</ID> <Version></Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="SC"> <ID>Microsoft.SystemCenter.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="Windows"> <ID>Microsoft.Windows.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="Health"> <ID>System.Health.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="System"> <ID>System.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> </References> </Manifest> <Monitoring> <Discoveries> <Discovery ID="Microsoft.SQLServer.2012.Discovery.Addendum.PopulateSQL2012EngineGroup" Enabled="true" Target="SQL2012Disc!Microsoft.SQLServer.2012.InstanceGroup" ConfirmDelivery="true" Remotable="true" Priority="Normal"> <Category>Discovery</Category> <DiscoveryTypes> <DiscoveryClass TypeID="SQL2012Disc!Microsoft.SQLServer.2012.InstanceGroup" /> </DiscoveryTypes> <DataSource ID="DS" TypeID="SC!Microsoft.SystemCenter.GroupPopulator"> <RuleId>$MPElement$</RuleId> <GroupInstanceId>$Target/Id$</GroupInstanceId> <MembershipRules> <MembershipRule> <MonitoringClass>$MPElement[Name="SQL2012Disc!Microsoft.SQLServer.2012.DBEngine"]$</MonitoringClass> <RelationshipClass>$MPElement[Name="SQL2012Disc!Microsoft.SQLServer.2012.InstanceGroupContainsDBEngine"]$</RelationshipClass> <Expression> <RegExExpression> <ValueExpression> <Property>$MPElement[Name="SQL!Microsoft.SQLServer.DBEngine"]/Version$</Property> </ValueExpression> <Operator>MatchesWildcard</Operator> <Pattern>11.*</Pattern> </RegExExpression> </Expression> </MembershipRule> </MembershipRules> </DataSource> </Discovery> </Discoveries> <Overrides> <DiscoveryPropertyOverride ID="Microsoft.SQLServer.2012.Discovery.Addendum.DisableSQL2012GP" Context="SQL2012Disc!Microsoft.SQLServer.2012.InstanceGroup" Enforced="false" Discovery="SQL2012Disc!Microsoft.SQLServer.2012.PopulateSQLServersInstanceGroup" Property="Enabled"> <Value>false</Value> </DiscoveryPropertyOverride> </Overrides> </Monitoring> <LanguagePacks> <LanguagePack ID="ENU" IsDefault="true"> <DisplayStrings> <DisplayString ElementID="Microsoft.SQLServer.2012.Discovery.Addendum"> <Name>SQL Server 2012 (Discovery) Addendum</Name> <Description>This management pack addresses a specific issue in the SQL MP where the SQL 2012 DB engine instance group does not populate correctly due to a version mismatch. This MP ONLY applies to that specific version.</Description> </DisplayString> <DisplayString ElementID="Microsoft.SQLServer.2012.Discovery.Addendum.DisableSQL2012GP"> <Name>Disable default SQL 2012 Group Population </Name> <Description /> </DisplayString> <DisplayString ElementID="Microsoft.SQLServer.2012.Discovery.Addendum.PopulateSQL2012EngineGroup"> <Name>Populate Microsoft SQL Server 2012 Instance Group (fixed)</Name> <Description>This discovery fixes an issue in the group discover in SQL MP version</Description> </DisplayString> </DisplayStrings> </LanguagePack> </LanguagePacks> </ManagementPack>

I will also attach the MP as a zip file to this post below.

Now my groups populate as expected:




Comments (7)

  1. Kevin Holman says:

    I validated this – this only is impacted on SCOM 2007R2. SCOM 2012 and up this upgrades just fine in my testing. There is obviously some changes we made in upgrade compat, between the versions, and I doubt we test on SCOM 2007 anymore since that is no
    longer a supported version.

    Is anyone seeing upgrade issues on SCOM 2012?

  2. Charles Champion says:

    I confirmed this is fixed in version of the SQL Server 2012 management pack.

  3. Anonymous says:

    Pingback from Cross Post: SQL Server 2012 Monitoring: Two Issues To Reckon With | Windows Management User Group

  4. HF says:

    We are getting error when trying to update from –> (Only regarding SQL 2012, the others upgraded as they should).
    We get this error: Could not load ManagementPack [ID=Microsoft.SQLServer.2012.Discovery, Keytoken=31bf3856ad364e35, Version=]. ManagementPack not found in the store.Version mismatch

    Any ideas?

  5. Blake Mengotto says:

    @Charles, thanks for the verification. @HF, I ran into this in my labs and just now in production. You have to delete all the SQL 2012 MP’s and any dependant MP’s (like override or custom) that are dependancies on those SQL 2012 MPs (delete monitoring
    first, then discovery). After you delete them, you can import the new ones. Looking in the RMS opsmanager event log, there are some changes in the mp that made some of them not upgrade compatible with the older versions (at least that’s the error I was reading)

  6. charles says:

    I am running into problems with discovery on a SQL 2012 Cluster with a new SCOM 2012 R2 installation. It does not detect the instances that are present. I have run all the updates for SCOM and the SQL MP and read your blog and other solutions but i can
    not get the instances discovered. There are no errors in the event log and it seems that the same problem exists for some of the instances on a stand alone 2012 SQL server.

    Is there a way to force the discovery?

  7. Dominique says:


    Where could I find the SQL Server Management Pack which most likely concerns only SQL 2012 but prevent SQL Server MP to install 2012 if modules… if is missing