While installing a two node SQL Server 2008 Cluster on Windows 2008, I came across a problem with the syssubsystems table, PowerShell and the location of the SQLPS executable.
We have two local drives (C and D) on each node. Two folders are created on the D drive on Node 1:
D:\Program Files\Microsoft SQL Server D:\Program Files (x86)\Microsoft SQL Server
The shared components are installed here.
One folder is created on the Node 2 D: drive:
D:\Program Files\Microsoft SQL Server
And, on the C: drive:
C:\Program Files (x86)\Microsoft SQL Server
The PowerShell executable is located in the Program Files (x86)\Microsoft SQL Server folder.
If you query the syssubsytem table while Node 1 is active the agent_exe will point to the PowerShell exe on the D drive.
After moving the SQL Server resource to Node 2, the syssubsystems results will continue to point to the PowerShell exe on the D drive. The problem is that the path does not even exist on Node 2. The path to the exe is on the C drive.
Any process that requires PowerShell will now fail, as long as Node 2 is the active node of the cluster.
Has anyone else experienced this? I’ve tried to create the cluster using the Integrated Install, the Advanced/Enterprise Install and a command line install with always the same results.
Any additional information on this problem will be appreciated.
Answer by Matt 1 ·
Here's the correct answer to this.
There was a prior install of SQL Server 2008 on the server where the cluster was to be installed on. This prior install installed a version of SQL Server Compact edition. This version was still in the add/remove programs and when you attempted to install clustered SQL; the default path was to the c drive. This was based on where the Compact Edition was installed.
Once you completely uninstall any outstanding prior install of SQL Server, the cluster install will allow you to use any drive that is available. It will not default to the path from the earlier install.
Bottom line is that before you attempt to install SQL Server, make sure that there are not any fragments of a prior install of SQL Server on the server.
Answer by Glanzer ·
If you install the SQL shared tools in a nonstandard directory on the 1st node in your cluster, when you add the 2nd node to your cluster it puts the shared tools in the DEFAULT directory not the one you specified on your first node. This means that the Powershell subsystem will only work on one of the nodes not the other. I called Microsoft support about this issue and after working with me they acknowledged that it's a bug that will be fixed in SQL Server 2008 SP2. They offered 2 workarounds. Below is Microsoft's response with the 2 workarounds (we are leaning toward option 2):
It was my pleasure to work with You during your Microsoft SQL Server issue. As per our conversation, since this is a known issue with the SQL 2008 cluster, we will go ahead and Refund this case. Also, here are the workarounds we talked about.
(1) This is pretty similar to - http://support.microsoft.com/kb/903205, except the scenario is different. Run the below commands and this will correct the subsystem paths - this will break again once SQL goes back to the other node. It’s possible that someone might consider to have a procedure do this and have the proc execute at the startup.
delete from msdb.dbo.syssubsystemsexec
(2) Other and slightly more time consuming option is to - Remove the node [where the shared components are NOT in C:\program files] and just add it back again. Just remember you need to cleanup SQL from that node in entirety.
Note: Just FYI - This is scheduled to be fixed in SP2.
SQL Server Support Engineer
Answer by Matt Whitfield ·
Have you considered, as a work-around, creating a junction (hard-link / symbolic-link) so that there is a folder on the D: drive which points to the folder on the C: drive on Node 2? The SysInternals Junction tool can help you create that.