Tuesday, September 24, 2013

SSAS - cannot start service after specifying incorrect path in Server Properties

I accidentally specified invalid path (strictly speaking, it is actually a valid path but not correct path for Analysis Services) in Analysis Server Properties dialog from SSMS. This caused the Analysis services to be unable to restart.

Here is what I did.
1) Run SSMS.exe (SQL 2008) and connect to Analysis Service.
2) Rightclick on Analysis Service server from Object Explorer and choose [Properties].
3) Add a path (C:\Temp) in DataDir. (Please note that separator is "|" between multiple paths.)



4) Click OK. This will bring up the following message box.



5) So rightclick on Analysis Server (in Object Explorer) and restart service. And then, the following error occurred.



HOW TO RESOLVE

Here is what I did to resolve this issue.

1) Goto SSAS OLAP config folder (In my case, C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini )

2) Edit msmdsrv.ini and remove invalid path in DataDir element.

<ConfigurationSettings>
 <DataDir>C:\Temp|C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data</DataDir>
 <LogDir>C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Log</LogDir>
 <BackupDir>C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup</BackupDir>
 <AllowedBrowsingFolders>D:\OLAPBackup|C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\|C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Log\</AllowedBrowsingFolders>
 <CollationName>Latin1_General_CI_AS</CollationName>
 <Language>1033</Language>

3) Save and restart SSAS service.

After this simple fix, the service start worked fine :-)