Problem
I have a stand-alone instances of SQL Server Analysis Service (SSAS) and SQL Server Reporting Service (SSRS) running in my environment. How can I find what the version and edition of each package as well as the Service Pack installed? Check out this tip to learn more.
Solution
A few months back one of my colleagues asked me to investigate a particular server. The machine was not actually hosting any databases, but running a single, stand-alone instance of SQL Server Analysis Service (SSAS), much like the figure shown below.
Her question was simple enough: what Service Pack is running on this instance? Was it the latest version? If not, were we running any of the Cumulative Update (CU) packages?
Now typically most installations of SQL Server Analysis Service (SSAS) or SQL Server Reporting Service (SSRS) would see the service running along a default or named instance of the relational engine. That’s because the DBAs had chosen the database engine as primary component and selected SSAS or SSRS as additional components during installation. It’s easy to check what version and edition of the relational engine is running by simply connecting to the database engine from SQL Server Management Studio and bringing up the Properties window (or querying the @@VERSION global variable). Finding the Service Pack level is also fairly easy: just go to the SQL Server Configuration Manager application, bring up the Properties window of the database service and check the Advanced tab. It would be fair to assume the Service Pack applied to the database engine would also be applied to SSAS or SSRS.
Not in this case though. I could connect to the SSAS instance from Management Studio, but there was no entry in the Properties window that could show me the Service Pack. I knew the machine was running 2008 R2 Analysis Service, but had no idea about the Service Pack. You would face the same problem when connecting to Reporting Service from Management Studio.
Windows Registry Node for SSRS and SSAS Version and Edition
As I found later, the best place to look for the version and edition information for SSAS and SSRS is actually the Windows registry. The registry keeps all sorts of information about SQL Server components installed and this is where I found what I was looking for.
To start, here is where Windows keeps all SQL Server related information:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server
So the first step is to actually identify this node. All the other relevant nodes, keys and values are nested underneath it. We can find a few things if we navigate further down from here:
- The major release version of the product (2005, 2008, 2008 R2 or 2012?)
- The edition (Standard, Enterprise, Developer, etc.)
- The current version installed (without any Service Pack, CU or hotfix, this will be the same as the released version)
- The Service Pack
- Indication of any patches or hotfixes installed on top of Service Pack
To get this information, we expand to the following path:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\ComponentNameVersion.InstanceName
Note the ComponentNameVersion.InstanceName bit. There are few things to be mindful of:
- In SQL 2005, the component part will be called MSSQL.n for SSAS or SSRS. n will be a number like 2 or 3. Typically when both SSAS and SSRS are installed with the database engine, OLAP service is designated MSSQL.2 and Reporting Service MSSQL.3 and so on.
- In SQL 2008, 2008R2 or 2012, the component will be called MSAS for Analysis Service and MSRS for Reporting Service
- The version will be 11 for SQL 2012, 10_50 for SQL 2008 R2 and 10 for SQL 2008
- Instance name would either be the actual name of the instance or MSSQLSERVER for a default instance
So if we are considering a SQL 2008 R2 system with an Analysis Service instance called OLAP, the registry node where we need to start navigating down will be:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS10_50.OLAP
For a default instance of SQL Server 2008 Reporting Service, the node will be:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS10.MSSQLSERVER
Now that we have identified the starting point, let’s explore further down.
Finding the Current Version of SSAS or SSRS in the Windows Registry
This registry key shows the actual major release of the component installed (e.g. SQL Server 2008 + SP3 or SQL Server 2008 R2 + SP1 etc.). Note that this entry also reflects the Service Pack installed with the product. To get this information, navigate to the following:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\ComponentNameVersion.InstanceName\MSSQLServer\CurrentVersion
In the following picture, we can see the CurrentVersion of a SQL Server 2005 Analysis Service system. It’s listed as 9:00.5000.0 which is SQL Server 2005 + SP4:
Similarly, the CurrentVersion of the Reporting Service of another 2005-based system is shown below. This one has a CurrentVersion of 9.00.3042.00 which is SQL 2005 + SP2:
The figures below show the CurrentVersions of SQL Server 2008 and 2008 R2 Analysis Services and a SQL 2008 R2 Reporting Service:
So how do you make sense of this arcane number to a meaningful value? Well, that’s pretty obvious: if it is something starting with 9, it’s SQL 2005. 10.0 would be SQL 2008. 10.50 would be SQL 2008 R2 and 11 would be SQL 2012 (this is the latest version of SQL at the time of this writing). Service Pack? That’s what we will see next.
Find the SQL Server Edition, Patch Level and Service Pack
The next registry node that contains relevant information is:
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\ComponentNameVersion.InstanceName\Setup
Here, you can find the editions of the SQL Server components installed. There will be two registry keys: Edition and EditionType, both of these show the same information.
For Service Pack, look no further than the key called SP.
Finally, two other keys worth checking would be Version and Patch Level. When a Service Pack has been installed, the Version key’s value will not be the same as that of CurrentVersion. If no Cumulative Updates or Hotfixes were applied after the Service Pack, the Patch Level and the Version keys will show the same value. Otherwise the PatchLevel key will always have a higher value than the Version key.
Just so that it does not sound too confusing, let’s consider the following figures:
In the first one, this is a SQL Server 2005 x64 bit Enterprise Edition Analysis Service installation. The Service Pack applied was SP 4. Also, no CU or Hotfixes have been applied after the SP.
Similar information for a SQL 2005 Reporting Service installation is shown below. Only in this case, we know a patch update has been applied after SP2 was rolled out:
In the following figure, a SQL Server 2008 Analysis Service is shown. Here again, a CU was applied after the SP (Patch Level different than the Version key). If you search for the Patch Level, this is actually the Cumulative Update 2 released after SP1.
The following figure shows a named instance of Analysis Service. The instance is called “OLAP”. The Patch Level is different than the Version, indicating a minor patch update:
A default instance of SQL 2008 R2 Analysis Service looks like this:
Finally, the same type of information about a SQL 2008 R2 Reporting Service:
Final Words
Hopefully Microsoft will keep this structure of version, edition and service pack info within Windows registry in future releases as well. The fact the info is available from the Registry means it can also be queried programmatically.
Next Steps
- Learn more about the SQL Server @@VERSION function
- Investigate the registry entries of some of your Analysis Service and Reporting Service systems.
- Find out more about SQL Server Hotfixes, Cumulative Updates, Feature Packs and Service Packs here.
- Understand about Microsoft SQL Server Software Update Package naming schema and convention here.
- Read this article by Shashank Pawar about finding Analysis Service version info programmatically.
