In this article I will try to explain what are the minimum requirements to start SQL server analysis services instance in case of failure. Analysis Services is very sensitive on folder structure and file access but still basically depends on two components – registry and part of its original folder and file structure. I will start with the registry settings and their specifics (what you should pay attention to) and then proceed with the folder structure and what are the minimum required files and their permissions in order the service to start. So let’s begin.
As it comes to registry, SSAS instance information is kept in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<SSAS10.instance name>
Important sub-hives are:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERCPE
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERSetup
In the CPE you should pay attention to ErrorDumpDir registry key – this is the first important folder path, valuable for the instance to start
In the Setup part you should note SQLPath and SQLBinRoot paths as they are pointing towards binaries of the SQL Server installation and the context of which the instance is working. So far we have three registry keys important: ErrorDumpDir,SQLPath and SQLBinRoot. Those keys are one of the first read when the service is started. They also contain folders that should be accessible by the SSAS service account at any times (this means that the account should have at least read permissions on them in order to start)
2. Folder and file structure
Folder-wise SSAS needs the following:
- Config folder – it is always <SQLPath>Config
- Log folder – HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERCPEErrorDumpDir
- Data folder – HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSAS10.MSSQLSERVERSetupDataDir
Now SSAS does need the Config folder and the msmdsrv.ini file in it. The important thing here is that there is no need the file contain the actual settings – it can be copied from another (working) instance. It can be even a balnk file. If you leave the file blank, then when the service starts it will fill it with default settings.
Interesting behavior is noticed with the Log and Data folders – if the log folder is not accessible it is not crucial for service startup. Further errors probably will be raised but the service will be working. As for the data folder – if it not accessible or missing, then the instance will create a new one with defaults contents. Of course if the AS account has permissions to do so.
Having this said, when you start troubleshooting SSAS service instance startup failure (Error IDs 1069 and 1042 for example), you should follow these steps:
- Extract all folders from the registry and check their availability – if something is missing try to restore it from backup
- Check the Config folder if exists and it has necessary permissions set
- Check the msmdsrv.ini file if exists and its contents. Delete the contents if necessary.
- Check if something is using default SSAS TCP/IP port 2083 (you can use netstat /b in the command prompt)
- These four steps should cover most of the cases of SSAS startup failures.
In general there is not much documentation from Microsoft on the matter and the errors when the service fails to start are quite general. It is difficult to troubleshoot because there is also no logging started before the actual service startup. But having the service started once, even with issues it would be easier to troubleshoot as you will have error logging and access to configuration values from SSMS.