SQL Server 2008 Cluster Issue

Question

question

Matt 1 asked Oct 22, '09

SQL Server 2008 Cluster Issue

All,

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.

Thanks.

sql-server-2008 cluster powershell

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-01T09:43:25Z

Matt 1 answered Dec 1, '09

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.

Thanks!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-22T15:28:20Z

mrdenny answered Oct 22, '09

When installing the second nodes, there's no way to specify the path to store everything in. I'd open a connect item. Seams to be a bug in the installer.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-22T15:57:44Z

Matt Whitfield answered Oct 22, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2010-03-15T10:48:24Z

Glanzer answered Mar 15, '10

Hi everyone,

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.

use msdb

go

delete from msdb.dbo.syssubsystemsexec

msdb.dbo.sp_verify_subsystems 1

(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.

Regards

[...]

SQL Server Support Engineer

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question