Click here to Skip to main content
1,837 members
Articles / Analysis-Services
Tip/Trick

Moving Analysis Service on a SQL 2008R2 Failover Cluster

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
12 Oct 2011CPOL 19.2K   1
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
    XML
     <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)


Written By
Founder CodeProject
Canada Canada
Chris Maunder is the co-founder of CodeProject and ContentLab.com, and has been a prominent figure in the software development community for nearly 30 years. Hailing from Australia, Chris has a background in Mathematics, Astrophysics, Environmental Engineering and Defence Research. His programming endeavours span everything from FORTRAN on Super Computers, C++/MFC on Windows, through to to high-load .NET web applications and Python AI applications on everything from macOS to a Raspberry Pi. Chris is a full-stack developer who is as comfortable with SQL as he is with CSS.

In the late 1990s, he and his business partner David Cunningham recognized the need for a platform that would facilitate knowledge-sharing among developers, leading to the establishment of CodeProject.com in 1999. Chris's expertise in programming and his passion for fostering a collaborative environment have played a pivotal role in the success of CodeProject.com. Over the years, the website has grown into a vibrant community where programmers worldwide can connect, exchange ideas, and find solutions to coding challenges. Chris is a prolific contributor to the developer community through his articles and tutorials, and his latest passion project, CodeProject.AI.

In addition to his work with CodeProject.com, Chris co-founded ContentLab and DeveloperMedia, two projects focussed on helping companies make their Software Projects a success. Chris's roles included Product Development, Content Creation, Client Satisfaction and Systems Automation.

Comments and Discussions

 
GeneralThank you! Pin
Cary Davis30-Apr-13 10:37
Cary Davis30-Apr-13 10: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.