none
Changing the Data Directory - SSAS 2005

    Question

  • I need to change the SSAS data dir in order to move my SSAS databases to a new drive with adequate free space.  However, after I change the data dir property to point to the new drive and location and restart the SSAS service, the property reverts back to the default location.

    I made the unfortunate mistake of going ahead and moving all my data files to the new directory yesterday assuming the directory path would not revert.  This caused all sorts of problems and forced me to re-deploy the database from the solution in order to get the cubes back up.

    Here is the process I followed in case it will help.

    1.  Added a new browsing path to the AllowedBrowsingFolders property so I could access the drive and folder I wanted to move the data to.
    2.  Changed the Data Dir property to use the new path.
    3.  Stopped the SSAS Service.
    4.  Moved the data files to the new location.
    5.  Restart the SSAS Service.
    6.  Discovered the data dir path had reverted back to default location.

    Does anyone have any ideas why the data dir property is reverted back to the default location?

    thanks in advance for your help,

    Thomas
    trieke
    Tuesday, January 20, 2009 3:25 PM

All replies

  • Try changing the path in the msmdsrv.ini
    You can find this under C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Config\ or wherever you have the installed the SQL server on machine.

    Edit  the <DataDir></DataDir>

    Is your sql server properly service packed??? I have seen this issue on servers without SP2
    Tuesday, January 20, 2009 3:34 PM
    Answerer
  • I was hoping it was a service pack issue, but it is running SP2.

    Now here is what I found that is even more weird.  I decided to make sure that changes made in the GUI were being written to the msmdsrv.ini file, so I made a change to the AllowedBrowsingFolder and then went to check the ini file.

    The ini file did not reflect the change and the modified date of the file did not change as well and then I noticed that the msmdsrv.bak file actaully had a current datetime stamp.  I opened it up and my change was refelected in the bak file. 

    Could somehow it be writing to the wrong file?  This doesn't make any sense.
    trieke
    Tuesday, January 20, 2009 4:06 PM
  • I am Clueless... Install the SP3 and check if it persists.
    Tuesday, January 20, 2009 4:51 PM
    Answerer
  • Thank you for your assistance, it was very appreciated.

    I have not been able to figure out why I was unable to update the file via the GUI, however I was able to manually edit the file and update the DataDir element.  However, upon moving the files from the data directory to the new location, then the service would not restart.  I then removed all the files I copied into the new location and then the service would start. So I just redeployed from the solution and now my SSAS database is where I want it to be.

    Not the ideal solution, but it worked.

    thanks again!!
    trieke
    Tuesday, January 20, 2009 6:42 PM