All right with you?
In this post, I will show you how to move/migrate Analysis Services cubes to another drive or directory. This is often used when you need to move the “DataDir”, “BackupDir” or “LogDir” directories to another location or another unit, a very common day-to-day need that I've had to do a few times with clients here at Power Tuning, usually in cases where the disk where the cubes are stored (DataDir), which by default is the C: disk, ends up getting full.
To move Analysis Services cubes to another drive or directory, open SQL Server Management Studio (ssms.exe).
In the Object Explorer screen, click on “Connect” and then on “Analysis Services”
In the Object Explorer screen, click on the Analysis Services instance, press the right mouse button and select the “Properties” option
In this properties screen, which just appeared to you, perform the following actions:
- In the left menu, click on the “General” tab
- Check the “Show Advanced (All) Properties” option at the bottom of this screen
- Memorize the current path where the Analysis Services data files (DataDir) are located or copy it to a notepad so you don't forget it.
The default path is “C:\Program Files\Microsoft SQL Server\MSAS.\OLAP\Data\”
Example: C:\Program Files\Microsoft SQL Server\MSAS16.SQL2022\OLAP\Data
- In the “DataDir” property, change the current path to the new path where you want to move the files (don't move the files yet)
- You now need to change the value of the “AllowedBrowsingFolders” property.
It is formed by the values of the “BackupDir”, “LogDir” and “DataDir” properties, in that order, concatenated using “|” as separator.
You can add the new path to the end by including the “|” separator and the new file path or replace the previous value with the new value, taking care not to change the wrong path.
The SSAS service user must have read and write permissions on all directories listed in this property's values.
Observation: This step is not mandatory, because if you automate all your Analysis Services maintenance via the command line, this change does not even need to be made, as it will not affect its use in any way.
After changing the values of the “DataDir” and “AllowedBrowsingFolders” fields, click on the “OK” button to confirm the changes.
Open the “SQL Server Configuration Manager”
Click on the “SQL Server Services” menu, in the left corner, select the SQL Server Analysis Services service that is running the instance where you will move the cubes, right-click and select the “Stop” option to stop the service.
Now that the service is stopped, move the files from the old directory to the new directory.
After moving the files, restart the Analysis Services service.
Now go back to the Analysis Services properties to confirm that the “DataDir” property value is pointing to the new path and verify that the cubes are working correctly.
Migration of cubes to the new disk or directory completed successfully.
I hope you enjoyed this tip and until next time!