SSAS: I will add you to the existing SQL cluster


You have an existing SQL Server 2005 Failover Cluster which has Database Engine and Full-text Search as the clustered components. Now you suddenly decide that this SQL Server cluster group requires a clustered Analysis Services instance. This will then lead you down a rabbit hole trying to figure out what to do to achieve this unless and until you know where to look.

There is a note in the Books Online content stating the following:

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.

However the above information is incorrect!!

You CANNOT add a failover cluster instance of Analysis Services to an existing SQL Server failover cluster instance using the Setup GUI!

Adding an Analysis Services failover cluster instance to an existing SQL Server cluster group has probably been a point of consternation for a person attempting SQL setup if you ran into the above mentioned scenario.

Now to the interesting part… achieving the objective using setup parameters and command line setup!! Sounds like fun, eh? The setup command would need to use the following parameters:

start /wait <CD or DVD Drive>\setup.exe
/qb VS=<VSName> – Virtual Server name should be the same as the existing database engine virtual server name
INSTALLVS=
Analysis_Server
INSTANCENAME=<InstanceName> – Instance name should also be the same as the instance name of the database engine. For default instances, use MSSQLSERVER.
ADDLOCAL=Analysis_Server
ADDNODE=<NodeName1, NodeName2,… NodeNameN>
GROUP=<SQL Diskgroup>
IP=<IP,Networkname> – Network name here is the cluster network name. See Gotcha#2 below.
ADMINPASSWORD=<StrongPassword>
SAPWD=<StrongPassword
INSTALLSQLDIR=<InstallationPath>
INSTALLASDATADIR=<Sharedrivepath>
SQLACCOUNT=<domain\user>
SQLPASSWORD=<DomainUserPassword>
AGTACCOUNT=<domain\user>
AGTPASSWORD=<DomainUserPassword>
ASACCOUNT=<domain\user>
ASPASSWORD=<DomainUserPassword>
SQLBROWSERACCOUNT=<domain\user>
SQLBROWSERPASSWORD=<DomainUserPassword>
SQLREPORTING=1
ASCLUSTERGROUP=<YourDomain \ YourDomainGroupName>

There are few gotchas here.

1. If you specify a Virtual Server name other than your existing SQL Server virtual server name for the VS parameter, then you will get a failure while trying to create a new IP resource:

Error Code: -2147019839
Windows Error Text: The cluster IP address is already in use

2. If you specify incorrect parameters for the IP parameter, you could encounter a"network name not found" error. The network name value in the IP parameter is the name of the cluster network that shows up in the Failover Cluster Manager snap-in or Cluster Administrator snap-in. The network name is NOT the name of the Windows Network Interface.

3. You need an additional shared disk. If your Database Engine is using G: drive for the existing instance, you cannot use the G: drive to install the Analysis Services instance even though you are installing into the same cluster group.

References

SQL Server 2005 Setup Parameters
http://technet.microsoft.com/en-us/library/ms144259(SQL.90).aspx

Setup – RPC, please be nice to me


Recently, I had looked into a SQL Server 2008 cluster setup failure where the following information was noted in the Detail.txt file:

2011-02-21 11:58:37 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2011-02-21 11:58:37 Slp: The RPC server is unavailable
2011-02-21 11:58:37 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
2011-02-21 11:58:37 Slp: System.ComponentModel.Win32Exception: The RPC server is unavailable
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.Cluster.ClusterResource.UpgradeResourceDLL(String nodeName, String dllPathName)
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SQLEngineClusterFeature.UpgradeResourceDLL(SQLServiceResource sqlResource)
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SQLEngineClusterFeature.ConfigureSQLEngineResourceType()
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch_ConfigRC(EffectiveProperties properties)
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SqlFeatureConfigBase.Execute(ConfigActionScenario scenario, ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcbCurrent)
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
2011-02-21 11:58:37 Slp:    at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
2011-02-21 11:58:37 Slp: Exception: System.ComponentModel.Win32Exception.
2011-02-21 11:58:37 Slp: Source: Microsoft.SqlServer.Configuration.Cluster.
2011-02-21 11:58:37 Slp: Message: The RPC server is unavailable.

The RPC Server is unavailable is a pretty generic error and can happen due to a lot of reasons. One of the common reasons that we have seen in CSS while troubleshooting setup issues is with incorrect DNS entries. This can happen if DNS entry for the cluster network name wasn’t configured properly and is pointing to an incorrect IP Address.

Another point to keep in mind is Windows Firewall. Make sure that the Firewall configuration allows this RPC call through.

I worked with Shahryar (Twitter) on a similar issue last week and it was identified that a PING request to the cluster name or IP returned no response.

If all else fails, then network tracing would help in figuring out what is failing and where.

References:

Error message when you connect to a cluster virtual server by using the named pipes protocol: "The machines selected for remote communication is not available at this time."
http://support.microsoft.com/kb/306985/

Quick facts about applying updates on a SQL Cluster


When a SQL Server 2005 Service Pack is applied on a clustered SQL instance (SQL 2005 and above), the Service Pack/Cumulative Update/Hotfix will upgrade all the member nodes, if a cluster-aware component is selected on the component selection screen. There is no need to run the SP installation after failing over the SQL resources to the passive nodes and re-applying the SPs. The passive nodes are upgraded using a Task Scheduler job created by the SQL setup.

As for the non-cluster aware components like SSIS, Tools etc., if you select them along with the clustered components, then the patches will be applied for the non-cluster aware components as well on the member nodes. In case, you do NOT select the non-cluster aware components while running the update for the cluster aware components, then you will manually have to patch these components.

When applying patches for non-cluster aware components, you do not need to failover the resources.

Setup takes the SQL resource OFFLINE/ONLINE on the active node. The resource is taken offline to patch the binaries and then it is brought online by SQL Setup to apply the configuration scripts. There are no failovers involved during SQL 2005 patching.

Cluster Aware components: Database Services, Analysis Services

Non-cluster Aware components: Integration Services, Reporting Services, Tools