SQL Server TCP Port vs TCP Dynamic Ports

Category : Tips Published : February 2, 2017 User Rating : 5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




What is the difference between TCP Port and TCP Dynamic Ports

The default instance of SQL Server will listen on port 1433 (unless you’ve changed it to something else). However, by default, named instances will use a dynamic port, and SQL Server Browser will determine which port has been allocated and direct traffic accordingly. This works well if you’re running multiple instances on your local machine, but is not so good in corporate and other environments where you need a fixed port number so you can open specific firewall ports. Fortunately it’s fairly easy to change to a fixed port (or to change the port number) once the instance has been installed.

Changing from a Dynamic to Fixed TCP Port

After you’ve created the new instance open up SQL Server Configuration Manager. Navigate to ‘SQL Server Network Configuration’ in the left hand pane and select the instance you’ve just created (you can see that I’ve created four instances for different versions of SQL). Right click on the TCP/IP item in the right hand pane and select Properties :

Configuration Manager TCP Ports
In the Properties window you can see what dynamic port has been allocated at the bottom of the window (in my case it’s 49243, but as this is dynamic this will change to another port if I restart the instance) :

TCP Dynamic Ports
To change this to a fixed port delete the ‘TCP Dynamic Ports’ setting and add a fixed ‘TCP Port’ port number (in my case I’ve chosen 1435) :

TCP Fixed Ports
Click on ‘OK’. You will need to restart the instance, but once that’s done the instance will be on the new fixed TCP port.

Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/SQLServerTCPPortvsTCPDynamicPorts.aspx

Keywords

SQL 2012, 2008R2, 2014, 2016, TCP


Comments
Post by GT on Fri 16 Mar 2018 11:50. Report Inappropriate Post

The dynamic port will only change if the port is unavailable. Otherwise SQL Server will try to use the same port.
Post by Ed on Wed 13 Nov 2019 03:13. Report Inappropriate Post

Thank you!
Post by ronald kirui on Fri 14 Jan 2022 08:22. Report Inappropriate Post

when do wechange the port if it does not picks the already edited one
Post by Code Cutter on Wed 13 Mar 2024 12:22. Report Inappropriate Post

Which is better?

Why even have dynamic ports?

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered