次の方法で共有

How to connect to clustered Analysis Services instances

  • [アーティクル]

While clustered SSAS instances have been around for a while, we noted that there is still some confusion about the recommended way for connecting to clustered instances and our supportability policy.

Let’s start with the hopefully well known behavior for non-clustered SSAS instances.

Stand-alone Instances

All default SSAS instances will listen on port tcp 2383. Thus you will simply connect using the “host name” of the box where the SSAS instance is installed and the SSAS provider understands that the SSAS default port 2383 should be addressed.

For named instances the situation becomes a bit more complex. Named instances usually use dynamic port allocation and you cannot predict with confidence which port is going to be used after a restart of the service. Therefore we have an intermediate service called “sql browser service” which does the mapping between instance names and port numbers. The resource file in use by sql browser is “msmdredir.ini” and it usually resides under the following path:

C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig

In the msmdredir.ini file you will find the <instances> section which shows a mapping of instance names to port numbers.
If you have IPv4 and IPv6 protocols enabled, then there will be two port entries per instance.

f.i.

<Instances>
    <Instance>
            <Name>TABULAR</Name>
            <Port>50732</Port>
            <PortIPv6>50732</PortIPv6>
        </Instance>
    <Instance>
            <Name>MDTEST</Name>
            <Port>50731</Port>
            <PortIPv6>50731</PortIPv6>
        </Instance>  
</Instances>  

When you connect to a named SSAS instance using the instance name (“Hostname\InstanceName”), the the provider will contact the SQL Browser Service under port tcp 2382. SQL browser will then search for the instance name being queried and return the port information that was registered in “msmdredir.ini” to the provider. Thereafter the provider can establish a connection to the named SSAS instance using the port number it received from sql browser.

The SQL Browser behavior is pretty similar to what you may know from relational SQL Server instances. The difference being that for relational SQL Server instances the SQL browser Service listens on port UDP 1434 and it sources its port info from the registry. For SSAS instance connection requests the sql browser service will listen on port TCP 2382. And the requested port information is read from “msmdredir.ini” file.

The SQL browser module that is used for SSAS connectivity is also referred to as “OLAP redirector service”.

Clustered instances

Now lets go to the subject of clustered SSAS instances. Clustered SSAS instances behave differently from what you may know of clustered SQL Server instances. All clustered SSAS instances, no matter whether they were originally installed as default or named instance, behave like default instances and listen on port 2383. That’s assuming you started the SSAS instance properly via cluster administrator or cluster service. If you start a named instance on a cluster node outside of cluster service then it is a stand-alone instance and will use dynamic port allocation as discussed earlier.

The only supported way to connect to a clustered SSAS instance is to use the “virtual server name” (also called network name). Usage of the instance name like in  “VirtualServerName\InstanceName” is not supported! One reason being that all clustered instances will always run on port 2383 and thus we don’t need to specify the instance name. The other reason being that the SSAS part of SQL Browser service functionality (OLAP redirector service) is not cluster-aware and we cannot guarantee that the content of “msmdredir.ini” is always updated correctly.

In this context “not supported” does not mean that connectivity via instance name does not work. In fact it usually does work. But we cannot guarantee that it will always work in a stable fashion. Therefore our strong recommendation is to always use “VirtualServerName” (=Network name) for establishing connections to a clustered SSAS instance.

Comments

  • Anonymous
    April 16, 2014
    Update:  On of the more common question that reach me seems to be how we can have several clustered SSAS instances listening on the same default port number 2383 on a given cluster node.The answer being that all clustered SSAS instances (virtual servers) listen of different network names and IP addresses. Thus it's the same port, but a different IP address.
  • Anonymous
    August 28, 2014
    i recently built out a sql 2012 cluster instance with analsysis services clustered as well and i came across the below errorA connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.Xmla) - No connection could be made because the target machine actively refused iti was making the same mistake of accessing the sql analysis server via sql studio with virtual server nameinstance name instead of just the virtual server name.Luckily i came across this blog, thanks it helped me resolve the error.