Background: A customer called recently saying that they were seeing some strange things happening on a server with two SSAS 2012 tabular instances (Default and TEST). When they connected to either instance through SSMS they found dropping cubes in one instance also dropped the cube in the other. Furthermore, a cube deployed to the Default instance showed up in TEST. Checking the configuration of the instances under SSMS showed that both pointed to TEST.
Investigation: The following was double checked to ensure there weren’t any incorrect configurations:
- Registry entries (HKLM\Software\Microsoft\Microsoft SQL Server\MSAS12.MSSQLSERVER\Setup and HKLM\Software\Microsoft\Microsoft SQL Server\MSAS12.TEST\Setup)
- What you’re looking for here is to make sure the DataDir, SQLBinRoot, SQLPath etc., are different in each instance.
- SSAS Services (start > run > services.msc)
- Check to make sure the “Path to executable” for each instance is different and each service is pointing to the correct config path.
- msmdsrv.ini file found in C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config and C:\Program Files\Microsoft SQL Server\MSAS12.TEST\OLAP\Config
- Check the DataDir, etc., are set correctly.
- msmdredir.ini file found in C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig
- Make sure under <Instances> you have the TEST instance defined, e.g. in my case we had:
Wait a second! It turns out msmdredir.ini had the TEST port set to 2383 – this is the default instance port. With some further digging, I found the default instance was configured to listen on 2384. To test if this was causing our issue, I reset both SSAS instance port configurations through SSMS to 0 – dynamic – and restarted both instances. Bingo – this was it.
Solution: Implement either of the below, both will require you to restart the instances:
- Let SQL manage it by setting both port configurations to 0 (through SSMS or updating msmdsrv.ini directly). To do it through SSMS, you’ll need to force the connection to the instances by using the port syntax in the connect dialog box, for example: 127.0.0.1:2383 and 127.0.0.1:2834. Then right-click the instance > properties and under general, update the “port” configuration.
- Set the port manually as below for each instance:
- MSSQLSERVER (Default), Port: 2383
- TEST, Port: 2384
Double check msmdredir.ini and you should find the <Instances> section updated with the new port after the restart. Don’t forget to double check your applications and update any connection strings.
I was also able to reproduce this again on SQL Server 2016 instances so be careful when defining static SSAS instance ports, especially when configuring a named instance to use the default port and later on to adding a default instance.