SCCM : How to Force SQL Reporting Services Point to select a specific SRS Instance on a machine with multiple instances (an unsupported workaround)

As specified in the following TechNet article : How to Create a Reporting Services Point for SQL Reporting Services it is recommended that you install the SQL Reporting Services Point on a computer running only one instance of SQL Reporting Services. The reason is that the current version of SCCM uses the first instance of SRS returned by a WMI query, and it is not possible to force a different one.

When you install a SQL Reporting Services Point, Configuration Manager 2007 queries Windows Management Instrumentation (WMI) for installed instances of SQL Reporting Services. The report folder is installed on the first instance of SQL Reporting Services found by the WMI query. If you have installed multiple instances of SQL Reporting Services and are unsure about whether the instance on which you want to install the report folder is the first stored in WMI, it is recommended that you install the SQL Reporting Services Point on a computer running only one instance of SQL Reporting Services.

I faced with this limitation many times in my work experience, because it is common to find in my customers’ environments an SRS dedicated machine with multiple instances and the requirement to setup an additional machine with a single instance of SRS that need an additional SQL Server License made my customers not so happy.

When it happen in may Lab environment I started to investigate if there is a method to force SCCM to select the instance I want. Since the selection is based on the order in which instances are returned by the WMI query, I started trying to guess the WMI query used. I don’t know if it is somewhere documented but as a first try I looked inside the SCCM dll srsserver.dll to see if the query is contained in clear text, here the result :

 

  I guessed that the query is the one highlighted in yellow and the namespace is the one highlighted in blue. To verify this, I used wbemtest to connect to the WMI namespace highlighted in blue :

 

then I executed the query highlighted in yellow :

The instance selected by SCCM in that machine was the default instance (MSSQLSERVER), the result of the query gave the same result :

At that point I used process monitor to try to understand where WMI gets this information, here the result :

The list of SRS instances seems to be read from the Registry Key : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS. I noticed that the order of the results returned by WMI was the same as the order of the results returned by RegEnumValue API. As specified in the MSDN documentation and in the following Blog Post there is no a fixed rule on the order of the enumeration :

As discussed above, the function returns values in any order. This includes unnamed values. Therefore, do not assume that an unnamed value is enumerated first or last. If the function returns ERROR_SUCCESS, the enumeration is not finished and more values may be enumerated. The enumeration is finished only when the function returns ERROR_NO_MORE_ITEMS.

Raymond Chen

26 Dec 2003 7:59 AM

1. There is no guarantee that the order of RegEnumValue will be “in order of creation”. The registry code was tweaked for performance in Windows XP and I suspect it will be tweaked for performance in the future. One of these tweaks may change the order of enumeration, since that is unspecified.

I know that there is no guarantee that the order of RegEnumValue will be “in order of creation”, and that the order will be the same for the entire life of the server, but I tried to follow the the rule “last created, last returned” and on my server it seems to work.

I deleted the value HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS\MSSQLSERVER and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS\ACS then I recreated the value HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS\ACS followed by the value HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\MSSQLSERVER. This is the result of the previous change :

In this way I inverted the order of the instances returned by the WMI query and after that SQL Reporting Service Point used the instance “SCCM” instead of the instance MSSQLSERVER.

This workaround may not work in every environments and there is no guarantee that this order will be changed by a fix, or a service pack or simply for performance reasons in the life of the server, but for a lab environment it is acceptable.

– Fabrizio

This posting is provided “AS IS” with no warranties, and confers no rights.

About these ads