September 22, 2008 at 8:24 pm
When there are 8 or 16 instances on a server/cluster, how do you specify the port for each SQL instance? How do you guarantee that port can be used by that instance only? How do you prevent the other process/service from grabbing that port?
For SQL2000 and/or SQL2005.
September 23, 2008 at 1:44 am
In 2005 you set the port in SQL Server Configuration Manager - select Network Configuraton and change the port in the properties under the TCP/IP protocol. For 2000 you do a similar thing in the SQL Network tool.
September 24, 2008 at 4:46 pm
2000 is the Server network Utility.
September 24, 2008 at 8:46 pm
Find a KB mentioned about this topic:
How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port
http://support.microsoft.com/kb/823938
Then a further question: Use a static port or a dynamic port?
September 24, 2008 at 9:01 pm
You can go dynamic. The SQL Browser service is what determines which port it is if you address the instance by name. So if you have a server named SUIS01 and a named instance of "Sales", then when you connect to "SUIS01\Sales", the SQL Browser works with the client to connect to the correct port.
However if you have router/firewalls that need to be configured, you want a static port.
September 24, 2008 at 9:55 pm
My shop has had problems with dynamic port assignment and binding in Sql 2000. Port conflicts can happen but you'll still be able to connect using Query analyzer because it will use Named pipes if the port doesn't bind. Most apps will connect via TCP/ip and won't work. It is hard to troubleshoot because you can connect but the app can't so it is deceiving.
We assign static ports using the 15100 port range. 15000 to 15500 are unassigned by ISO (or whoeve decides that) by starting at 15100 we have a comfortable margin on both sides of the range. Whatever you consider your first instance on the server I would assign it 15101 and work up.
Hope that helps, it has worked well for me.
David
September 25, 2008 at 9:05 pm
David O (9/24/2008)
My shop has had problems with dynamic port assignment and binding in Sql 2000. Port conflicts can happen but you'll still be able to connect using Query analyzer because it will use Named pipes if the port doesn't bind. Most apps will connect via TCP/ip and won't work. It is hard to troubleshoot because you can connect but the app can't so it is deceiving.We assign static ports using the 15100 port range. 15000 to 15500 are unassigned by ISO (or whoeve decides that) by starting at 15100 we have a comfortable margin on both sides of the range. Whatever you consider your first instance on the server I would assign it 15101 and work up.
Hope that helps, it has worked well for me.
David
So you use static port. KB823938 seems prefer dynamic port to static port for the problem below. I doubt the saying though.
"Another program may already be using the specified TCP/IP port.
If another program is already using the specified TCP/IP port, the port is not available to the instance of SQL Server and SQL Server clients may not be able to connect to the instance of SQL Server.
This problem is specific to an instance of SQL Server that is configured to use a static TCP/IP port. This problem does not occur for an instance of SQL Server that is configured to use dynamic port allocation. ..."
How did you come up with using port range? Is there any guideline, recommendation or article to refer to?
September 26, 2008 at 9:21 am
No I didn't have a article or recommendation. The dyanmic port keep colliding with other apps (as described in the article you quote) and caused down time.
I selected that range because it is high up and was unassigned. I haven't checked the official TCP port assignment list for a couple of years though. My shop has being using this configure for 5 years and it is solid for us.
Good luck,
Daiv
September 26, 2008 at 9:49 am
Thank you David.
How about SQL2005 in your opinion? Should apply the same - static port with a high range?
September 26, 2008 at 9:55 am
My shop is skipping 2005 and going straight 2008 but I intend to keep the same configuration.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply