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?
Best Answer
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.