How to set PreAllocate parameter in SQL Server Analysis Services and something more

I suppose you think that changing a parameter in SQL Server Analysis Services engine is quite easy stuff. And probably you are right, because I also thought so before I had to face some reconfiguration request. So straight to the point – changing this parameter is possible ONLY by manually editing the msmdsrv.ini file (the SSAS configuration file in the Config folder). After opening the file for editing just browse to the <Memory> section and you will find it on the bottom of it:

<PreAllocate>0</PreAllocate>

If you set a value between 0 and 100 then this will be converted to percentage of the Total Physical Memory that SSAS is configured to use. If the number is higher than 100 then it will be treated as absolute value in bytes. This setting is actually serving two purposes – warming up the SSAS memory and tacking an issue with memory allocation for SSAS on Windows Server 2003 (several reference articles can be found here and here).

So far so good. But this setting was quite new to me so before reaching the point of changing it I had to go through several different articles and whitepapers. Such are:

SQL Server 2008 White Paper: Analysis Services Performance Guide

SQL Server 2005 Analysis Services (SSAS) Server Properties

Based on the entire task for setting this and some other parameters I learned quite a few things for SSAS configuration. Some of them are:

1. There are two types of settings on SSAS instance level – basic and advanced. Advanced, just as the ones in SQL Server Engine, are hidden by default. In order to display and edit them you should check the box “Show Advanced (All) Properties” on the Analysis Services Properties window.

image

2. You can set absolute values for memory parameters – values between 0 and 100 are treated as percentage of the total physical memory available to SSAS (not the server itself). If the value is greater than 100 – then it is treated as an absolute value in bytes.

Minimum requirements for starting SQL Server 2008 analysis services

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.

1. Registry

As it comes to registry, SSAS instance information is kept in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<SSAS10.instance name>

Important sub-hives are:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\CPE

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\Setup

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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\CPE\ErrorDumpDir
  • Data folder – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS10.MSSQLSERVER\Setup\DataDir

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:

    1. Extract all folders from the registry and check their availability – if something is missing try to restore it from backup
    2. Check the Config folder if exists and it has necessary permissions set
    3. Check the msmdsrv.ini file if exists and its contents. Delete the contents if necessary.
    4. Check if something is using default SSAS TCP/IP port 2083 (you can use netstat /b in the command prompt)
    5. 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.