During installation of Analysis Services, most of the properties are configured with a default value. These properties can be reconfigured any time after installation from SQL Server Management Studio. For most properties the default values works just fine. However, there are few properties that are best reconfigured immediately after installation to prevent down-time in production environment.
At a minimum, these four properties MUST be reconfigured BEFORE any cubes are deployed. Failure to reconfigure these values could result in cube processing failure due to insufficient disk space over time.
|DataDir||Folder where the cube files are created.||\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data||Any Storage Folder|
|LogDir||Folder where the log files are created.||\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log||Any Storage Folder|
|BackupDir||Folder where the cube backup is created.||\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Backup||Any Storage Folder|
|TempDir||Folder where the temp files during cube processing is created.||Empty. If Empty, same as DataDir.||Any Storage Folder|
The account running Analysis Services must be granted same permission to the target folder as the default folders.
Setting Property Values from SQL Server Management Studio
1. From the SQL Server Management Studio, connect to the instance of Analysis Services. Right-click properties to bring up the Properties Page.
2. On the Properties page, tick the Show Advanced (All) Properties. Key-in new values for the DataDir, BackupDir, LogDir and TempDir properties. Notice that these changes will be effective only after Analysis Service is restarted.
3. Restart Analysis Services from SQL Server Configuration Manager.
What-if I Need to Reconfigure in an Operational Environment?
It is Ideal to reconfigure these properties immediately after Analysis Service is installed and before any cube has been deployed. If however, there is an existing operational environment which still has default values for these parameters, suggest following these steps to reconfigure the parameters.
1. First Modify the BackupDir and TempDir. Restart Analysis Services. At this stage leave the DataDir and LogDir untouched. Take note of the existing values for all the four properties.
2. Backup all the Analysis Service Database(s). This will create the backup in the new BackupDir.
3. Now change the DataDir and Log Dir. Restart Analysis Services. You will notice that the existing Analysis Service Database(s) no longer appears in Management Studio. This is because now the DataDir has changed.
4. Restore the Analysis Services Database(s) from the backup. This will create the cube files in the new DataDir.
5. Verify that the restores are successful by browsing the cube. Delete the default physical folders. If the restore is not successful, hit the Restore Default button on the properties page and start all-over again.
2 thoughts on “Minimal Post-Installation Steps for Analysis Services”
That post was written with SQL Server 2005 in mind. In SQL Server 2008 – Data Dir, Log File Dir, Temp dir and Backup dir can now be specified as a step BEFORE installation of Analysis Services – http://wp.me/pxNuz-ep