In SQL server the default instance has a listener which listens on the fixed port which is TCP port 1433. This is the default behavior. 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. This is of course the default behavior when the ports are not defined for the named instance manually.
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 be required 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 go 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 <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_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp
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 SQL Server service offline from cluster administrator
2) Disable the checkpointing to the quorum using the following command
Cluster res "SQL Server (TEST2005)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER"
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 commandcluster res " SQL Server (TEST2005)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”
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.
You can refer to the below KB article which discusses regarding the cluster checkpoint,
KB 912397 ; The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid - http://support.microsoft.com/kb/912397SQL Connectivity Troubleshooting - http://blogs.msdn.com/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx
SE, Microsoft SQL Server
Any way to do this via scripting? i need to do this change in 300 machines!!! I see the key in the registry for the port, but not the one to disable the dynamic behavior. Thanks.
You rock thanks for the info this saved my A$$. I changed my port by leaving the dynamic port and also adding the same static port. The error I got which I should have expected was duplicate ports port in use and the instance would fail. I came accross this article and you saved the day. Thank you so much. Feel free to contact me at email@example.com.
u r giving good valuble information regarding the changing the port number from dynamic to static in sequel server
this article is very good
Is this behavior apply to SQL Server failover clustering 2008? This is a wonderful article. Thanks for your help.
Which is a good number to select as new port?
I am surprised to see such a raw article.
"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."
Are you suggesting that checkpointing misbehaves when you change the port number? why would checkpoint push a stale value. Did you look at the ckpt files to see what got checkpointed.. amy be cluster logs to see if the ckpt ever happened?
I seriously think Microsoft needs to validate the blog posts before putting it out on the public.