SQL Server 2012 DB Engine Group Problem

♦ September 12, 2013

I was checking out some reports on the SQL Server MP as I had installed the new 6.4.0.0 version in a test system but still had 6.3.173.1 in production. When looking at group membership I noticed that there were a few SQL servers missing from the SQL Server 2012 DB Engine Group but that they were all shown as members of the group SQL Server 2012 Computers. But this was consistent for both versions of the MP.

The SQL 2012 DB Engine Group dynamic membership rule is

( Object is SQL Server 2012 DB Engine AND ( Version Matches wildcard 11.0.* ) AND True )

But all the SQL 2012 SP1 Servers were 11.1.3000. So this would never work. Initially I thought it was a bug in the MP but looking at version numbers according to Microsoft the SP1 version is 11.0.3000.00 which would mean that the group membership would work.

http://www.microsoft.com/en-gb/download/details.aspx?id=35575#

Versions shown on http://social.technet.microsoft.com/wiki/contents/articles/783.sql-server-versions.aspx

Even CU5 for SP1 is 11.0.3373.0.

http://support.microsoft.com/kb/2861107

This was puzzling as this did not match the version numbers of the customer. Searching on the web I saw a few more mentions of 11.1.3000.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/78268cc9-5a3a-440c-b950-e2e7edf817d2/msiexecexe-processes-keep-running-after-installation-of-sql-server-2012-sp1

But nothing to say why there were these two version numbers for the same product. The customer had Enterprise, Standard and Developer and all versions were 11.1.3000.0 if SP1 was installed.

 

On the SQL Server when I do

select @@version

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)

                Oct 19 2012 13:38:57

                Copyright (c) Microsoft Corporation

                Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Then I get 11.0.3000.0

But from SCOM Discovered Inventory

Display Name SCOMOPERATIONS

Full Path Name server.local\SCOMOPERATIONS

Instance Name SCOMOPERATIONS

Version 11.1.3000.0

Edition Standard Edition

I get 11.1.3000.0

 

In Registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SQLServer2012\CurrentVersion

It shows 11.1.3000.0

I tried looking at the SCOM discovery script for SQL Server 2012 DB engine but it goes on for pages and pages and pages and uses WMI. I am presuming if I found this registry key wrong then the script will find the wrong registry key wherever it is looking which it must do as it returns that value. So it looks like it is a SQL problem putting the wrong value into the registry and the MP developers were going by the documentation.

A search for 11.1.3000 finds that it is in these registry keys (and a lot more for SQL)

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup\Client_Components_Full
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup\Client_Components_Full\1033
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup\SQL_SSMS_Adv
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\Tools\Setup\SQL_SSMS_Adv\1033
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS11.SCSM\Setup
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS11.SCSM\Setup\Analysis_Server_Full
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS11.SCOMOPERATIONS\Setup
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS11.SCOMOPERATIONS\Setup\RS_Server_Adv
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS11.SCSM\Setup
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS11.SCSM\Setup\RS_Server_Adv
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SCOMDW\Setup\SQL_Engine_Core_Inst
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SCOMDW\Setup\SQL_Engine_Core_Inst\1033
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SCOMDW\Setup\SQL_FullText_Adv
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SCOMOPERATIONS\Setup
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SCOMOPERATIONS\Setup\SQL_Engine_Core_Inst
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SqlDom\CurrentVersion
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\sqlls\CurrentVersion
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLNCLI11\CurrentVersion
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SqlWriter\CurrentVersion
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SQLNCLI11
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products22C2487A9E6A5D4EA07E03128178F38\InstallProperties
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\KB2674319
  • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server 2012 Redist\SQL Server System CLR Types\1033\CurrentVersion_64

A search for 11.0.3000 only finds a number of names under this one key
HKEY_CLASSES_ROOT\Installer\Assemblies\Global
Name = Microsoft.AnalysisServices,fileVersion=”11.0.3000.0″,version=”11.0.0.0000″,culture=”neutral”,publicKeyToken=”89845DCD8080CC91″,processorArchitecture=”MSIL”

 

All very confusing but if you have SQL 2012 SP1 then be aware that the group SQL 2012 DB Engine Group will not pick up any 2012 SP1 SQL DB engines.

If you need to have a group targeted at all SQL 2012 DB Engines then either use the SQL 2012 Computer group or create your own group with the formula:

( Object is SQL Server 2012 DB Engine AND ( Version Matches wildcard 11.* ) AND True )

The SQL team need to get their installation or documentation fixed and/or the MP team need to update the SQL MP.

Comments are closed.