Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Analysis Services - How to move cubes and databases to another disk or directory

Post Views 206 views
Reading time 4 minutes

Hey guys!
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”

Connect to the Analysis Services instance you want to move data to.

IMPORTANT: Remember that a server can have more than one instance of Analysis Services, so you can connect using just the server name or the format “server_name\instance_name”. This is configured during SQL Server/Analysis Services installation.

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.

NOTE: The “AllowedBrowsingFolders” property is used to specify which folders you can browse when saving files, opening files, and browsing files in the Synchronization, Backup, and Restore screens of Analysis Services.

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”

IMPORTANT: If you are not finding the “SQL Server Configuration Manager” application, read the article SQL Server Configuration Manager gone? Learn how to retrieve the shortcut.

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!