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
- Open Management studio and log into Analysis Services for the cluster instance
- Right click on the server and click properties
- Edit the following path's to reflect the new locations
BackupDir
DataDir
LogDir
- Log out of Management studio
- Stop Analysis Service
- Copy the OLAP folder to the new path
- 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 - 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>
- 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.
- 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.
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.