Troubleshooting SQL Server Cluster Installations

Troubleshooting SQL Server Cluster Installations

Rate This
  • Comments 5

On a Windows Cluster it is possible to install a SQL Server Cluster, which will be a separate entity with disctinct Virtual Server Name, IP address, and pipe name. The nodes of the SQL Cluster must be able to communicate with one another. This communication may fail if the TCP port on which SQL Server is listening is not excepted from firewalls on the machines. This port can be found by using the Configuration Manager utility by viewing the properties of the TCP protocol or by checking the SQL Server errorlog, typically located at "Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\LOG". Here MSSQL.x is the instance ID of the clustered instance in question.

If the Cluster instance being installed is a named instance, a service called SQL Browser is used in order to figure out the port or pipe SQL Server is listening on. For SQL Server Cluster installations, this service is running by default. If this service is not running, it will not be possible to communicate with the remote nodes. To manipulate this service, open services.msc, find SQL Browser, and make sure its state is Automatic and Running. SQL Browser listens on a UDP port, which must also be excepted from firewalls on the machines. Even if SQL Browser is running, in case any of the nodes has firewall ON, it is necessary to add exceptions for UDP port 1434.

These issues with SQL Browser (service not running or TCP and UDP port not excepted from firewall) can also block remote connectivity to non-clustered instances.

More about Windows Firewall impact on APIs for SQL Server enumeration can be read at http://blogs.msdn.com/sql_protocols/archive/2005/09/24/473502.aspx. More about Firewall impact on SQL Server Cluster can be found at http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx.

Stoyko Kostov, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
  • Hello,

    Could you please clarify whether we need to specify

    CNU (client network utility) alias parameters for client machine connecting to SQL cluster named instance?

    That is what I understand from below article

    http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

    Is this still true for SQL 2005 Ent Edition with SP2?

    Thank you.

  • You don't have to use alias. But, if you see the issue described on the link (my blog), you can use alias or specify port number/pipe names directly to alleviate the issue. The issue still exists for SQL 2005 (and any SPs), but we do have a fix (well partial) for SQL 2008. Please refer to the link for details. Thanks.

  • My Windows XP computer restarts suddenly without warning. When I check the Event Viewer, it shows an error that says:

    configuration of the AdminConnection\TCP protocol in the SQL instance

    MSSMLBIZ is not valid.

    What do I do to fix this?

  • Can you check if you have any thing for this registry?

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxxxxxxx\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp\TcpDynamicPorts

    It should be either a valid number or empty.

    (replace MSSQLxxxxx to whatever applies to your instance, i.e. MSSMLBIZ)

    Thanks,

    Xiwei

  • Is it possible to access Clustered SQL 2005 from active node using (local) or localhost thru SQL Server Management Studio

Page 1 of 1 (5 items)