December 3, 2010 at 9:43 am
Hello, everyone! I am experiencing issues with SQL Server 2005 grabbing hold of random TCP/IP ports and am looking for a way to restrict the ports it can use to a specified range.
Here's some background... I am an interface engineer at a hospital in South Texas, and our interface engine utilizes SQL Server 2005 to temporarily store information about some of the HL7 transactions that we process. The interface engine connects to different applications at specific static TCP/IP ports. Every now and then we have noticed that SQL Server will grab one of those port that we want to connect over, which then prevents us from connecting to the external application. I believe this to be the issue because when this problem occurs I will run a NETSTAT command from the command line and I will see entries like the following:
[font="Courier New"] Proto Local Address Foreign Address State
TCP E5PBLI01:1419 E5PBLI01.valleybaptist.net:ms-sql-s ESTABLISHED
TCP E5PBLI01:1421 E5PBLI01.valleybaptist.net:ms-sql-s ESTABLISHED
TCP E5PBLI01:1431 E5PBLI01.valleybaptist.net:ms-sql-s ESTABLISHED
TCP E5PBLI01:ms-sql-s E5PBLI01.valleybaptist.net:1419 ESTABLISHED
TCP E5PBLI01:ms-sql-s E5PBLI01.valleybaptist.net:1421 ESTABLISHED
TCP E5PBLI01:ms-sql-s E5PBLI01.valleybaptist.net:1431 ESTABLISHED
[/font]
I will see as many as 100 ports listed in NETSTAT as being in use by SQL. The port numbers that SQL Server grabs hold of varies, but it consistently seems to be port numbers below 5000. I know that the engine does not use any ports in the range between 1500 and 2000, and so I'd like to restrict SQL Server to ports in that range. How can I do that? I'm definitely not an expert in SQL Server configuration, and hadn't run across any information in the Help documentation that looked like it would address this.
Thanks in advance for any assistance.
Regards,
Jose'
Interface Engineer
Valley Baptist Health System
December 3, 2010 at 9:55 am
"I am experiencing issues with SQL Server 2005 grabbing hold of random TCP/IP ports and am looking for a way to restrict the ports it can use to a specified range."
Happens all the time !
You need to determine what ports each SQL Server instance will use. Registered Ports are those from 1024 through 49151 and Dynamic and/or Private Ports are those from 49152 through 65535. The registered ports number ranges should not be used for named SQL Server instances as a conflict will occur, as you are encountering. See http://www.iana.org/assignments/port-numbers
Once you decide the port numbers, you can prevent windows from assigning them as a dynamic port by the procedure "How to reserve a range of ephemeral ports on a computer that is running Windows Server 2003 or Windows 2000 Server" http://support.microsoft.com/kb/812873/
You should also reserve the ports used by the other software.
Lastly, see the procedure "Configuring SQL Server Named instances to use a fixed port" http://technet.microsoft.com/en-us/library/ms345327(SQL.90).aspx. This will require a SQL Server restart.
SQL = Scarcely Qualifies as a Language
December 3, 2010 at 12:33 pm
Thank you, Carl - those references look like they contain exactly what I need.
Thanks!
José
June 2, 2011 at 7:13 am
Carl,
I need to use your suggestions, for firewall reasons, but my environment is more current.
I have an active/active cluster for sql. The 2 servers in the cluster are win server 2008 R2 with sql server 2008 R2.
I read thru the ephemeral ports article. It would appear that the article applies to win server 2008 r2 also.
On both physical servers, the ReservedPorts registery entry already exists with the ports 1433-1434 added.
I presume this was added as part of the install of sql on the cluster.
I should be able to choose 2 ports above 49151, one for each instance of sql, and assign them accordingly.
Then add the ports to the regestry entry on both servers.
This would allow me to open a single port in the firewall to each instance instead of a large range.
Do I have this correct or am I assuming too much?
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply