uninstall of clustered SSAS instance may disable “olap redirection service”

 

“OLAP redirection service” is the part of SQL browser service that supports SSAS connectivity. If you stick to our recommendation to only use “virtual server name” for connecting to clustered SSAS instances (see previous post: https://blogs.msdn.com/b/as_emea/archive/2014/01/08/connecting-to-clustered-ssas-instances.aspx), then you won’t need the “olap redirection service”.

However, we are aware that there are some situations where customers have to use instance name addressing (<virtual server name> \ <instance name> ) due to client application requirements. As stated in my previous blog post this type of addressing is not supported by Microsoft, but it does usually work.

Using <virtual server name> \ <instance name> in your connection string will lead to an attempt to connect to SQL Browser service in order to receive the port number for the desired SSAS instance. And here we will run into trouble if “olap redirection service” is disabled.

In the described situation your clients will be able to connect to the clustered SSAS instance using “virtual server name” or “<virtual server name>:<port number>”, but using <virtual server name>\<instance name> will give you the following type of error message:

“A connection cannot be made to the redirector. Ensure that ‘SQL Browser’ service is running.
No connection could be made because the target machine actively refused it <IP-address>:2382 (System)”

The reference to port 2382 in the error message indicates that we were really trying to connect to the sql browser port for “olap redirection services”.

This type of error message could potentially be triggered by other issues as well.
F.i. firewall issues (port 2382 blocked) or permission issues (sql browser service account has no access to its configuration file  “msmdredir.ini”).

Further symptoms/characteristics of a disabled “OLAP redirection service” are:

1. Using “netstat –ab” command or “tcpview” tool from sysinternals you will see that “sqlbrowser.exe” is running, but not listening on port tcp 2382.

2. When starting “sqlbrowser.exe” as a console application from command prompt you won’t see the start up message for “OLAP redirection service” marked in yellow.

C:\Users\xyz>"C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" -c
SQLBrowser: starting up in console mode
SQLBrowser: starting up SSRP redirection service
SQLBrowser is successfully listening on ::[1434]
SQLBrowser is successfully listening on 0.0.0.0[1434]
[6896]: Received request...
[6896]: Waiting for next request...
[6896]: Received request...
SQLBrowser: starting up OLAP redirection service
[6896]: Waiting for next request...
...

According to our setup logic for stand-alone SSAS instances the “olap redirection service” should only be disabled when the last named SSAS instance is being removed. Which makes good sense.

For clustered SSAS instances the logic is altered and the “olap redirection service” will be disabled when you have two SSAS instances on the cluster node, and you are removing one of them. Setup will incorrectly disable “olap redirection service” and the remaining SSAS instance cannot be reached via sql browser service anymore.

When saying “incorrectly” I should probably remind you that we do not support usage of “sql browser”/”olap redirection services” for clustered SSAS instances.

In the sql setup logs for the last uninstall of a clustered SSAS instance you will find the following entry :

2013-10-17 18:21:33 AS: Data : (Utility) KeyPath=SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\SQL Browser,KeyName=AnalysisServiceListener,KeyValue=0

This is the root cause. We effectively disabled AnalysisServiceListener = “olap redirection service”.

The described problem will only appear in a situation where you have two SSAS instances on the cluster node, and you are removing one clustered SSAS instance. The remaining SSAS instance will have no “olap redirection service” available anymore.

For restoring “OLAP redirection service” functionality you will have three options:

a) add a new clustered SSAS instance (verified)

b) run setup repair function (may help)

c) manually set the AnalysisServiceListener key from “0” to “1”.

The issue will be fixed with the next release of SQL Server Analysis Services.