Pinal Dave
SQL Tips and Tricks
6 Comments
During my recent engagement ofComprehensive Database Performance Health Check, Sr. DBA asked if it is possible to run SQL Server on multiple TCP Ports. The answer is Yes, it is very much possible.
To configure the SQL Server Database Engine to listen on an additional TCP port first open SQL Server Configuration Manager. Once it is open expand SQL Server Network Configurationand then click Protocols for yourinstance and a further expand Protocols for and then select TCP/IP. After that, you can scroll down all the way and go to option IPAll and click on properties.
Now if you have a default installed you will see a default port 1433 listed there. If you want to add any additional port here, just add a new port with comma over there. For example, if you want to add port 1600 along with 1433, you can specify ports as 1433,1600.
Following image demonstrate how you can configure the same withSQL Server Configuration Manager.
Next, you must restart your SQL Server services to this new port to take effect. You can restart the SQL Server services fromSQL Server Configuration Manager as well.
Once you restart your SQL Server services, you can specify your port number in SQL Server Management Studio in the format IP address (Comma) Port Number.
Makes sure that your firewall allows the IP address and Port to connect to the server. If you notice, it is indeed simple to configure SQL Server to listen to multiple TCP Ports.
Reference:Pinal Dave (https://blog.SQLAuthority.com)
Computer Network, SQL Server, SQL Server Configuration, Starting SQL
Related Posts
SQL Authority News – Join me at SQLBits 2018 – 36 Scripts: Performance Tricks Jedi Master Wants You to Know
SQL SERVER – Set AUTO_CLOSE Database Option to OFF for Better Performance
SQL SERVER – Unable to Launch SSMS Error – Cannot Find One or More Components. Please Reinstall the Application
6 Comments. Leave new
Uziel Bueno Ramirez
July 25, 2018 8:51 pm
ReplyHi Pinal,
Why do you have to do it in the IPAII only?
Thanks!
Pinal Dave
August 16, 2018 10:50 pm
Not necessary. you can have different IP and Port combination as well.
Reply
raj
July 5, 2019 8:48 am
ReplyHi,
If we have 2 instances running oon same server is it possible to configure separate port for each instance? Please Clarify.Thanks..
Mustafa ELmasry
January 6, 2020 1:54 am
Hi Raj
Reply
what i know if i have two SQL Server instances on the same server we should configure for each one difference port , bot instance can not work with the same port
Mustafa ELmasry
January 6, 2020 1:54 am
Thanks for this information but can i know what is the benefits from this option or what is the case that we can use on it this option
ReplyVinothkumar Venugopal
June 27, 2020 9:59 am
Do we have query to find ports use in multiple instances in Sql ?
Reply
Leave a Reply
Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.
Pinal has authored 13 SQL Server database books and 77 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,700 database tech articles on his blog at https://blog.sqlauthority.com.
Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at [email protected].
Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).
Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
