SQL Server 2000 Clustering Tips

Tip: When administering a cluster using Cluster Administrator, don’t run Cluster Administrator through Terminal Server, as what you see displayed on the screen from Cluster Administrator may not be correct.

Explanation: Due to an apparent bug, when you try to run Cluster Administrator from within a Terminal Services session, Cluster Administrator may not correctly reflect the status of the various cluster resources. As you might expect, this can lead to confusion and many potential problems.

To be safe, only access Cluster Administrator from the cluster nodes themselves, or from a locally installed copy of Cluster Administrator on your desktop.

Version: 2000. May be a problem in 7.0, but I have not been able to verify this.

Date Added: 6-10-2002

*****

Tip: When you try to use a strong password for the cluster service account, Cluster Service may not be able to recognize it, causing the installing of Windows 2000 clustering to fail.

Explanation: Due to an apparent bug, Cluster Service, when it is being installed, may not accept strong passwords for the cluster service account. A strong password is a password with letters, numbers, upper and lower case, and special characters. When the installation process runs, it may not be able to correctly interpret the password, and the installation process fails. This may be true even given the fact that Active Directory has no problem accepting the strong password.

For now, if you run into this problem, the only work-around is to use a less strong password for the cluster service account. Contributed by Paul Lynch.

Version: 2000

Date Added: 6-10-2002

*****

Tip: If you need to change the account or password of the accounts used for the SQL Server service accounts, it is best to change them through Enterprise Manager, not from the Services option available from the operating system.

Explanation: Changing the service account or account password from Enterprise Manager automatically changes the account information on all nodes of your cluster in a single step. If you change account information using the Services option, then you must do this for each SQL Service account affected, on each node of the cluster, which could introduce potential problems if you make any mistakes.

Version: 2000

Date Added: 7-1-2002

*****

Tip: The drive letter used for a shared array by SQL Server clustering must be the same drive letter on all nodes of the cluster

Explanation: When you configure your nodes for SQL Server clustering, one of your chores is to create a shared array for your instance of SQL Server, and then assign it a drive letter. It is very important that each node use the exact same drive letter for the shared array. If you don’t, then SQL Server clustering will not be able to fail over from one node to another.

Version: 7.0, 2000

Date Added: 7-1-2002

*****

Tip: If you want to initiate a manual fail over of SQL Server from one SQL Server clustered node to another, you must do this through Cluster Administrator, not Enterprise Manager.

Explanation: SQL Server clustering is designed in such a way as to only allow the manual failover of one node to another by using the Cluster Administrator. If you try to do this through Enterprise Manager by stopping the SQL Server services, manual failover will not occur automatically. What will happen is that the cluster service will automatically restart the service on the same node. If you try stopping the SQL Server services more than four times in a row, SQL Server may failover, but it also may produce some unexpected results. So don’t do it.

Version: 7.0, 2000

Date Added: 7-1-2002

*****

Tip: Do not configure a clustered file share on the same array used for SQL Server data.

Explanation: Configuring a clustered file share on an array used by SQL Server is possible, but it presents two problems of concern. First, in increases the amount of time for SQL Server to failover from one node to another. Second, if the file share needs to failover, but SQL Server does not need to failover, failover will occur anyway, possibly causing problems for your users when the failover occurs.

Version: 2000

Date Added: 7-1-2002

*****

Tip: If your cluster uses Extended Stored Procedures, the xp’s must be installed on a shared array. If the xp’s use COM objects, these must be installed and registered on all modes of the cluster.

Explanation: If xp’s used in a clustered SQL Server are not located on the shared array, then should failover occur, the xp’s will fail to run. If COM objects used by an xp are not installed on all nodes and properly registered, then should failover occur, they will not function.

Version: 2000

Date Added: 7-1-2002

*****

Tip: Under some circumstances, the private network connection of a cluster can switch from “Internal cluster communications only (private network)” to “All communications (mixed network)”. This can cause the network connection for the cluster to fail, preventing users from accessing your cluster.

Explanation: Windows 2000 uses a technology called Media Sense, which is part of the Plug and Play features of the operating system. The default behavior of Media Sense is to destroy a network card’s TCP/IP connection when the network connection fails (for whatever reason). When that happens, the Cluster Service receives a notification of the event and removes this network connection from the available connections. In most cases, this is not a problem.

Unfortunately, if network connectivity to both network adapters in a cluster node fail at the same time, the network role for the cluster can be changed from “Internal cluster communications only (private network)” to “All communications (mixed network).” When this happens, it is possible for the cluster resources to be bound to the network adapter designed for the private network, not the public network, which means users cannot access the cluster. In effect, the cluster has failed and no clients can connect to it.

In most cases, this problem can be fixed manually, by changing the private network connection role back to “Internal cluster communications only (private network),” and then rebooting all of the nodes of the cluster and bringing it back up properly. But this is a temporary fix. If this problem repeats itself, you have no choice but to turn of the Media Sense feature of the operating system. See this Microsoft Article on how to hack the registry to turn off Media Sense.