Monday 13 February 2017

SQL Server :: Moving SSAS Database to a new drive on same server

As per business requirement, We wanted to move SSAS Cubes or databases to new location.

Here are steps.


1. Take backup of cubes 

2. Detach Cubes

3. Run set directory script -- Just replace values with actual values before running 

----------------------------------------------------------------------------
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object />
  <ObjectDefinition>
    <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
      <ID>S123\##INSTANCE_NAME##</ID>
      <Name>S123\##INSTANCE_NAME##</Name>
      <ServerProperties>
        <ServerProperty>
          <Name>DataDir</Name>
          <Value>F:\mpdbs001\olapdb_##INSTANCE_NAME##\</Value>
        </ServerProperty>
        <ServerProperty>
          <Name>LogDir</Name>
          <Value>F:\mplog001\olaplog_##INSTANCE_NAME##\</Value>
        </ServerProperty>
        <ServerProperty>
          <Name>TempDir</Name>
          <Value>G:\mptmp001\olaptmp_##INSTANCE_NAME##\</Value>
        </ServerProperty>
      </ServerProperties>
    </Server>
  </ObjectDefinition>
</Alter>
------------
4. Run browse script 
---------------
<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object />
  <ObjectDefinition>
    <Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
      <ID>S123\##INSTANCE_NAME##</ID>
      <Name>S123\##INSTANCE_NAME##</Name>
      <ServerProperties>
        <ServerProperty>
          <Name>AllowedBrowsingFolders</Name>
          <Value>G:\olapbackup_##INSTANCE_NAME##\|F:\olaplog_##INSTANCE_NAME##\|F:\mpdbs001\olapdb_##INSTANCE_NAME##\|F:\olaplog_##INSTANCE_NAME##_Encrypted\|F:\mpdbs001\olapdb_##INSTANCE_NAME##_Encrypted|G:\olaptmp_##INSTANCE_NAME##_Encrypted</Value>
        </ServerProperty>
      </ServerProperties>
    </Server>
  </ObjectDefinition>
</Alter>

--------------

5. Copy files to new location

6. Attach cubes

7. Verify all cubes are online

Now, Task completed and all cubes are moved to new location. Hope it will help.

Thanks