Setting up Analysis Services on an existing SQL cluster

  • Article

Another infrastructure issue that I ran into while deploying my BAM solution out into the "real" world was that in our post-dev environments SQL is clustered and we would be installing Analysis Services to a cluster which already has SQL Server installed, so I decided to "practice" installing Analysis Services to a SQL cluster in our dev environment to validate the steps required to do it for real.

My initial research led me to this KB https://support.microsoft.com/default.aspx/kb/910230 about installing Analysis Services on a failover cluster.  Inside the KB however, it stated that if I was installing to an existing SQL Server 2005 cluster, to reference "How to: Install SQL Server 2005 from the Command Prompt" instead.  Within THAT article I found a suggested script for installing analysis services to an existing cluster.  Accompanied with the script was a note:  "You cannot install Analysis Services to the same cluster group as the Database Engine. You must install Analysis Services to its own group and then, after installation, you can move Analysis Services to the same group as SQL Server."  That's odd:  The other article said I could and referred me here...

At any rate, I attempted to run the suggested script and it kept failing.  I'll spare the gory details of all the different combinations which I tried, but in the end, and with the help of some friends, and friends of friends, and friends of friends of friends, I finally found a script that worked:

start /wait d:\setup.exe /qn VS=SQLVSName INSTALLVS=Analysis_Server INSTANCENAME=BAMAnalysis ADDLOCAL=Analysis_Server IP="10.100.100.10,NETWORK1" IP="10.100.100.101,NETWORK1" ADDNODE="NODE1, NODE2" GROUP="SQL" ASCLUSTERGROUP=domain\clustergroup1 ADMINPASSWORD=<password of account installing this script> INSTALLSQLDIR="C:\Program Files\Microsoft SQL Server\" INSTALLASDATADIR="E:\Program Files\Microsoft SQL Server\OLAP\" SQLACCOUNT=domain\sqlserviceacct SQLPASSWORD=<SQL Service Acct Password> AGTACCOUNT=domain\agtserviceaccount AGTPASSWORD=<AGT Service Acct Password> SQLBROWSERACCOUNT=domain\sqlbrowserservice SQLBROWSERPASSWORD=<SQL Browser Service Acct password> ASACCOUNT=domain\asserviceacct ASPASSWORD=<AS Service Acct Password>

 

In case it isn't clear what these parameters are, I'll explain the less obvious ones:

  • VS - the virtual server name for your SQL server instance (not the cluster group name)
  • INSTALLVS - specifies the SQL feature being installed (this value is static)
  • INSTANCENAME - specifies the instance name for the Analysis Services instance you are installing
  • IP - the IP and network name (not NIC adapter name, but the name of the Network as specified in the cluster administrator).  One for each node specified in the ADDNODE parameter
  • ADDNODE - the names of the cluster nodes where AS will be installed
  • GROUP - the name of the cluster group containing the SQL instance
  • INSTALLSQLDIR - the path to the SQL binaries
  • INSTALLASDATADIR - any path visible to all nodes where you would like the actual Analysis Services data to be stored

Another handy trick is to change the /qn switch to /qb.  This still does a scripted installation, but pops up the dialog boxes so if the script fails you have an idea where it is failing.  But the most valuable information is in the log files which are written to Microsoft SQL Server\90\Setup Bootstrap\LOG\Files.  The "Core" log files seemed to have the pertinent data relating to why the script may be failing.

UPDATE!!!  About 2 hours after I posted this (here's to the efficiency of the modern search engine) I got a ping from an engineer who was attempting to help a customer perform the same trick with their cluster and had just picked up this blog post.  Filed under the heading "It's a small world" it turned out to be a local customer who had contacted me a few months ago with some questions about bringing in BizTalk.  They'd decided to move ahead and are getting their first solution going with the product and were building out their environment to support BAM as well.

I jumped on the phone with the customer and I helped them verify their environment-specific parameters in the script, then they attempted to execute.  However, the script failed with error "SQL Server Setup is unable to run.  Either the owning node or all nodes that are currently part of the virtual server are not available.  Review any SCC failures and validate that all nodes are online.”  In the SQL Core(Patched) log on NODE1, there was a statement in the setup steps for NODE2 which read: 

Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\sqlsval.dll Version:2005.90.4035.0

Error: Action "InvokeSqlSetupDllAction" threw an exception during execution.

Return Code: 70045

After a bit of troubleshooting, we figured out that for whatever reason, when they executed the script on NODE1, the remote install action was unable to install on NODE2 (we were getting security audit errors for wmiprvse.exe trying to access various objects, no SQL log files were being written on NODE2, and there was nothing being written to the scheduled task log which we'd expect).  There were some other errors in the SQL logs from NODE1, but nothing definitive.

After awhile, the customer simply removed NODE2 from the ADDNODE switch and voila, it started installing correctly on BOTH nodes.  I'm not sure what twist to the environment (I suspected something about DCOM launch permissions, but could never verify that) made that necessary, but if you get this error, try removing the remote node(s) from your script.