Click here to Skip to main content
967 members (448 online)
Click here to Skip to main content
Add your own
alternative version

Stats

8.6K views
Posted 12 Oct 2011

Moving Analysis Service on a SQL 2008R2 Failover Cluster

, 12 Oct 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
It’s not possible to add or remove features on a SQL cluster under 2008 R2. Here's a workaround.

The issue we had was our F drive on the SQL cluster needs to go and is being replaced by  G. F had a hardware failure and must go away. Unfortunately Analysis Services is installed on F.  It’s not possible to add or remove features on a SQL Server cluster under 2008 R2. So removing it an reinstalling it is not an option. The Microsoft documented solution is to uninstall the nodes and re-install them. Sorry Microsoft, not good enough me.


Here is how my step by step of how I hacked out the solution.


Moving Analysis Service on a SQL 2008R2 Failover Cluster


From the Active Node



  1. Open Management studio and log into Analysis Services for the cluster instance


  2. Right click on the server and click properties



  3. Edit the following path's to reflect the new locations

    BackupDir
    DataDir
    LogDir


  4. Log out of Management studio


  5. Stop Analysis Service


  6. Copy the OLAP folder to the new path


  7. In the registry go to
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQLServerOLAPService
    . Edit the Image path of F:\OLAP\Config to reflect its new path G:\OLAP\Config


  8. Open the OLAP\Config\msmdsrv.ini file and edit the paths to reflect their new locations
     <ConfigurationSettings>
        <DataDir>G:\OLAP\Data</DataDir>
        <LogDir>G:\OLAP\Log</LogDir>
        <BackupDir>G:\OLAP\Backup</BackupDir>
         <AllowedBrowsingFolders>G:\OLAP\Backup\|G:\OLAP\Log\</AllowedBrowsingFolders>
        <TempDir>G:\OLAP\Temp</TempDir>

  9. Repeat step 7 on the other SQL clustered nodes (6 is omitted as their files don't exist on the other nodes until they become active nodes.


  10. Start Analysis Services on the active node


Now with analysis services running you should be able to rename the original folder to something like f::\OLAP-BAK. If you can you have succeeded. If not you missed something or Microsoft changed something since I wrote this article.


You should also perform a failover to ensure this all worked.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Chris Maunder
Founder CodeProject
Canada Canada
Chris is the Co-founder, Administrator, Architect, Chief Editor and Shameless Hack who wrote and runs The Code Project. He's been programming since 1988 while pretending to be, in various guises, an astrophysicist, mathematician, physicist, hydrologist, geomorphologist, defence intelligence researcher and then, when all that got a bit rough on the nerves, a web developer. He is a Microsoft Visual C++ MVP both globally and for Canada locally.

His programming experience includes C/C++, C#, SQL, MFC, ASP, ASP.NET, and far, far too much FORTRAN. He has worked on PocketPCs, AIX mainframes, Sun workstations, and a CRAY YMP C90 behemoth but finds notebooks take up less desk space.

He dodges, he weaves, and he never gets enough sleep. He is kind to small animals.

Chris was born and bred in Australia but splits his time between Toronto and Melbourne, depending on the weather. For relaxation he is into road cycling, snowboarding, rock climbing, and storm chasing.

You may also be interested in...

Comments and Discussions

 
GeneralThank you! Pin
Cary Davis30-Apr-13 11:37
memberCary Davis30-Apr-13 11:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
RA-Web1 | 2.8.170424.1 | Last Updated 12 Oct 2011
Article Copyright 2011 by Chris Maunder
Everything else Copyright © RootAdmin, 1999-2017
Layout: fixed | fluid