Karan Gulati (SSAS Maestro)

Title – All Possible Scenarios of Changing SSAS Data Directories

Scenario 1:

Recreating a New Data Folder in New Drive / Same Drive

Creating a New Data Folder and changing the Data File Location in configuration files as shown below –

1. Connect to your Analysis Server in SSMS with a user that is a member of the server role but NOT the user that installed the software

2. Right-click on the server in Object Explorer. Select ‘Properties…’

3. On the general tab, change the value of the DataDir property to a different folder (it doesn’t matter what)

4. Click ‘OK’ on “Analysis Server properties” dialog. You will get a message telling you that the server needs to be restarted

5. Restart your Analysis Server in Services panel.

Caveat –

If you change the DataDir property of Analysis Server (i.e. the place that, by default, Analysis Services stores all its data) then you will lose valuable metadata about your installation – namely that you will lose all members of the fixed admin Server Role. The only user who can then connect to Analysis Server and carry out administrative functions is the user that installed the software.

These Screen Shots demos same –

change to c:\temp

request to restart.

Then on Security tab you missed the Server Administrators ….

Simple workaround –

For getting users of Admin Roles you can follow below mentioned steps:

Before applying steps mentioned above take the Script of SSAS Server Admin Roles –

Connect to SSAS

Right-click on the server in Object Explorer. Select ‘Properties…’

Select Page – Security ->On drop down Script ->

Drop Down Script and select Script to a file, now follow below mentioned steps.

1. On the general page, change the value of the DataDir property to a different folder (it doesn’t matter what)

2. Click ‘OK’ on “Analysis Server properties” dialog. You will get a message telling you that the server needs to be restarted

3. Restart your Analysis Server in Services panel.

4. Now log onto the server as the user that originally installed Analysis Services (for this is now the only user that will have server role privelages)

5. Open Script File which we had saved earlier -> Script file will open in XMLA query window.

6. Execute Script and after that you will find all existing users in place.

Scenario 2:

Moving Data Directory of Analysis Services Refer this blog post

Scenario 3:

In this Scenario, Default Data Folder is V:\Data but while creating partitions developer changed the drive of Partition Directory. In such a case you can use Scenario 2 for Moving Data directory but in this Scenario I will show how to move the Partitions Directory (in case Partition directory got some issues and you need to relocate Partition Files)

Default data folder – V:\Data

Partition 2006 for Sales reside in C:\Partitions

New Location F:\Partitions

First Option:

OPEN Database in BIDS – > Go to Partitions -> In Storage Location Chang the path to new one.

Current-

New –

Before saving ensure that in F drive you have directory with same name, else you will get error stating folder doesn’t exist.

As soon as you hit save you will get this message – so you need to process partition to get it done.

Once you process – you are good, you will get data in new drive-folder.

Second Option:

Yes we can use the database backup and during restore change the drive location for specific Partition but if the Partition file size >4gb and AS is 2005, then you will be in trouble J because there is a limitation that a specific file shouldn’t be >4GB (yes fixed in SQL 2008) J