Microsoft SQL Server Analysis Services
- DxEnterprise for Windows
- Microsoft SQL Server
How to deploy an instance of Microsoft SQL Server Analysis Services within a DxEnterprise cluster.
Here are the Microsoft recommendations on how to deploy Analysis Services in a cluster environment:
Installation steps and considerations for deploying within a DxEnterprise cluster:
- Install a stand-alone instance of Microsoft SQL Server Analysis Services on all nodes where it will be hosted using a domain-based service account. For more information on Microsoft Windows service accounts and permissions, please see the following article: https://msdn.microsoft.com/en-us/library/ms143504.aspx
- Add the Analysis Services service to a VHost (right-click on the VHost and select “Add Service”). Do not select the “Assume VHost Name” option. Whether you will be adding Analysis Services to an existing VHost or adding it to a new VHost, make sure you have a managed volume assigned to the disk group for the VHost and mounted. This will be used to store the Analysis Services data files.
- Modify the %ProgramFiles%\Microsoft SQL Server\MSAS<version#>.\OLAP\Config\msmdsrv.ini file on each node to point the data, log and backup directories at the managed volume assigned to the VHost:
<DataDir><Managed_Volume_Path></DataDir> <LogDir><Managed_Volume_Path></LogDir> <BackupDir><Managed_Volume_Path></BackupDir>
- Test rehost to ensure that the data, log and backup paths are applied successfully to the Analysis Services instance on each node. After each successful rehost, connect SQL Server Management Studio to the <Vhost>\<SSAS_Instance> and verify the instance properties (per node).
Because Analysis Services is a stand-alone service and is not managed by DxEnterprise, any modification to the instance properties, msmdsrv.ini file or service registry will need to be completed on each node where that particular instance of Analysis Services is installed. Otherwise the changes will not persist on rehost or failover.
NOTE: It is possible to configure each instance of Analysis Services to read the same shared msmdsrv.ini file. To do this, copy msmdsrv.ini to the same volume where the instance data files are stored and modify the registry on each node to point to that location.
- Open Regedit and browse to the following key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSOLAP$
- Edit “ImagePath”. Leave the path to msmdsrv.exe at the default location, and modify the path after the “-s” to the new path of the msmdsrv.ini file.
The caveat to this is that the SSAS instance service will only be able to start on a single node at a time, which can complicate maintenance tasks.