5

The 2-node SQL Server 2008 R2 cluster works fine until one of the resources got removed. I successfully added it back. However, the cluster is not able to online the SQL Service unless it is on 1433 and not the port I was using before. Below is the application error log:

[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 274d; message = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

The service status becomes Pending Online and after a while, it becomes offline. Using the SQL Configuration Manager, I revert to 1433 and it works fine again.

I have tried disabling checkpointing of the quorum (How to Change the Dynamic Port of the SQL Server Named Instance to an Static Port in a SQL Server 2005 Cluster) but nothing works unless I use an alias with the old port number.

Am I missing something here?

5
  • 3
    It sounds like something on that server already has ownership of the port you were using. Can you try a netstat and see if that is the case? Sep 20 '14 at 2:41
  • If that was the case, then how would you resolve the conflict. I did a netstat -a and found there was another ip address listened on that port while the cluster resource status was pending online. But i'm not sure if that was a normal behavior or not. Thanks.
    – dba
    Sep 20 '14 at 11:45
  • 3
    I would find the pid form netstat and figure out what application/item it was and if I couldn't change the port in that application/item then I would change the SQL Server port to a different one. You could also do a windows port reservation but I don't like doing that in a cluster. Sep 20 '14 at 15:29
  • I have identified the pid # as an IIS worker process from another reporting server. Would changing the port for the IIS worker affect the app as a whole? Thanks.
    – dba
    Sep 22 '14 at 1:59
  • I don't see why it should if it's just a worker. Sep 22 '14 at 11:54
2

A default instance install (instance name of MSSQLSERVER and on port 1433) will disable the SQL Browser service because 1433 is assumed. On a clustered SQL install the cluster service will try to connect tot he instance and run a "select @@servername" in order to show that the server is up, but because it's a default instance it tries to use 1433 by default. I've tried to re-enable the SQL Browser service after changing the port so that the mapping of instance:port happens but haven't had any luck.

What you can try is to set up a client side alias (using SQL Server Configuration Manager) on each node with the virtual SQL name of your default install (the network name) and then put the port number in the alias so that SQLVIRTUAL points to SQLVIRTUAL,3876 or whatever port you are trying to change to. This way when the cluster service tries to poll the instance to see if it's up on 1433 it will be redirected to the actual port by the alias. You will not be able to use a dynamic port for this.

0
2

Thank you all of you guys for helping with this issue. The problem was the remappipenames in the registry was set to 0 instead of 1. After I changed the value back to 1, SQL server resource was able to get online using a non-default port. I also don't have to use an alias.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.