In sql server the default instance has a listener which listens on the fixed port which is TCP port 1433. However for the named instance the port on which the sql server listens is random and is dynamically selected when the named instance of the sql server starts.
So when we connect to the default instance of the sql server the connections goes to the TCP port 1433 while when we connect to the named instance of the sql server the connections goes to the sql browser service which listens on the UDP port 1434. The sql browser service replies to the client with port no. on which the named instance of the sql server listens at that point of time and then the client is redirected and connects to the port no. provided by sql server service.
You may require to change the dynamic port of the named instance to the static port no. so to implement Firewall in your organization so that clients connect to the sql server through the firewall.
For Standalone instance of the sql server we can change the dynamic port of the named instance to the static port by using sql server configuration manager in sql 2005. In order to change the port we need to the sql server configuration manager.
Start->Program Files-> Microsoft sql server 2005->Configuration Tools->sql server configuration manager.
In configuration manager console, on the left hand panel, expand the sql server 2005 Network Configuration.
Click on the”Protocols for <Named-Instance-Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.
Click on the Ip Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.
After performing these activities we need to restart the sql server services for the changes to take affects.
After restarting the service you can confirm that whether changes has taken affect by checking the following registry key
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server<Instance Name> MSSQLServerSuperSocketNetLibTcp
This key should contain the port no. defined by you.
However in case of the cluster, many a times (In particular when sql server 2005 32 bit is installed on the 64 bit system) it is observed that when we change the port no. of the named instance to the static port using the method described above the port no. again changes back to the dynamic port after you restart the services.
This is behavior can be explained as follows
When we changed the dynamic port of the sql server to the static port and stopped the sql server service the local copy of the registry which contains the dynamic port no. gets checkpointed to the quorum.
Now when the service is started the Checkpointed value from quorum are copied back to the registry and hence in spite of changing the value to static port. The value was replaced by the dynamic port from quorum.
So in order to change the dynamic port of the server to the static port in the clustered named instance of the sql server 2005 we need to follow the following steps
1) Take the sqlserver service offline from cluster administrator
2) Disable the checkpointing to the quorum using the following command
Cluster res “SQL Server (TEST2005)” /removecheck: “SoftwareMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLSERVER”
3) Change the Dynamic port of the sql server to static port on all the nodes using the method described above.
4) Enable the checkpointing to the quorum using the following command
cluster res ” SQL Server (TEST2005)” /addcheck: “SoftwareMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLSERVER”
5) Bring the sql server service online
In the above example “TEST2005” is the name of my sql server instance
MSSQL.2 is since it is second sql server 2005 instance installed on the system
You will observe that the dynamic port is changed to the static port.
SQL Server 2005 Clustered Named instance does not allow static IP Address – so how can this woerk?
It saved my last bit of hair from certain oblivion 😉