Analysis Cubes, Clustered Servers, and Named Instances

Mark was kind enough to let me borrow his corner of the blogoshpere to pass along some fun information about Analysis Cubes.

Yesterday, I was working at a client (a public, very well known company running Dynamics GP as a financial package) that has a very complex internal environment on which they wished to run Analysis Cubes for Excel (ACE). We had:

1. A GP transactional Server running in a remote data center
2. A SQL Cluster with two physical machines and two virtual nodes setup in overlapping active / passive cluster (A is active, B is passive but B is also active with A as passive). The SQL Cluster would serve as the second tier in the ACE installation running both Analysis Services and storing the Data Warehouse. This means SSIS would also run on this machine.
3. Both were 64 bit OS
4. Several TSE machines, all 32 bit.
5. No domain priviledges, no sa user access, but my doman user id was granted access as sysadmin to the GP transactional server and to the SQL Cluster.
6. And, lastly, the SQL Cluster Virtual Machince (node) which would run the data warehouse, SSAS and SSIS was a named instance rather than a default instance.

One thing: I am not a MCSE, nor do I have tremendous amounts of hardware/infrastructure/high performance clustering background, so forgive me if some details around the clustering don't make sense to a more educated reader.

As you know, you cannot install ACE on a 64 bit machine. To compound matters, ACE, by default, won't install into a cluster because SSIS is not cluster aware. This causes a problem with the ACE install when it tries to create the SSIS packages - you get a "login timeout" error. You can replicate this by going into Management Studio and trying to expand the MSDB folder under the SSIS service on the virtual server. The error is the same.

To solve the first problem is pretty easy: Just install the server configuration utility onto a 32 bit workstation or TSE from which you can make a connection to the SQL and Analysis Servers. In this case, we installed the Server Configuration utility onto a TSE running Windows 2003.

To solve the second problem is tricky - you basically have two options:

1. Make SSIS cluster aware: This allows you to have SSIS run as a cluster service and manage the SSIS packages from Management Studion on any cluster node. The best source for understanding how to do this is here: http://msdn.microsoft.com/en-us/library/ms345193(SQL.90).aspx. However, YOU MUST read the correction entitled "Corrections for registry keys and file system paths" at the bottom of the page - if you don't it won't work.

There's one with pictures at: http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

Honestly, we couldn't get this to work yesterday. I think because we didn't apply the registry entry from the correction and because we had a named instance, we had some problems. Also, DCOM security settings seemed to get in the way. So, we dropped this in favor of option 2.

2. Make SSIS Manageable from the Cluster Node but Not Cluster Aware:
You do this by updating the MsDtsSrvr.ini.xml file on each physical node of the cluster to point to the various virtual nodes in the cluster. The best link for this is here: http://support.microsoft.com/kb/942176.

Basically, you edit the MsDtsSrvr.ini.xml file found in %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn. Under the Folder node of the xml file, you'll find an entry for MSDB (that's the name child node) and "." (that's the Server Name child node). Change the value of the server name child node to the fully qualified instance name of the virtual server on which you will run the SSIS packages. For example, SERVERNAME\InstanceName.

By making this change, you are telling SSIS that it is running on that particular virtual node and instance, so you can then manage SSIS from Management Studio from that node.

Personally, I like this last option better as it is less work and I don't see where ACE really needs to be part of a high availability fail over scheme for most companies. If the failover occurs, you would probably need to reinstall the package files manually to get the system back up and running.

By they way, if you ARE using a named instance of SQL Server and NOT using a cluster, the same technique works to get ACE to install on the named instance.

The only problem we had was as follows: we have two virtual machines in the cluster and so created two entries in the MsDtsSrvr.ini.xml file, one for each machine, both with the name MSDB (see the above link and you'll get a sense for what this would look like). The odd thing that happened inside Management Studio was that I could connect to the SSIS service under virtual server A and see two folders for MSDB, both showing the same package list from A. If you refreshed the service, I then saw the packages on B. Refresh again, and I saw A. Basically, it toggled back and forth. I think that's because I named them both MSDB, but haven't finished the testing around that.

Once the above is done, you can now run the ACE Server Configuration utility. However, DEFINITELY use the SP2 version of ACE available on partnersource or you may get an error around the creation of the linked server entry on the GP transaction server. Use the cluster virtual server node fully qualified name (SERVERNAME\Instance) as the Analysis Cubes and Data Warehouse server and you should be good to go.

I'd love to hear comments, feedback or your experience with this.