May 18, 2012 at 8:03 am
Hi all
I've had a bit of a nightmare configuring this server, as though I've done a lot of SQL "internal" admin and DBA (security, backup policy etc), the network side is something I'm not very strong on.
Anyway, this named instance SQL 2008 installation was set up to listen on a dynamic TCP port. The way this was set up in SQL Configuration (Protocols, TCP/IP) was:
- Dynamic TCP ports and TCP Port both set to Blank for IP1-IP[n].
- For IPAll, Dynamic set to an explicit value (49944), TCP Port blank.
I've had some "exciting adventures" making sure that UDP 1434 was opened in the Firewall so that SQL Browser can provide this info to clients. (AND that TCP 49944 was opened).
(a GOTCHA: for some reason, TCPView shows sqlbrowser.exe using UDP port 1434: but doesn't show it as LISTENING. This is misleading. It clearly is listening: I sent a request from a client using PortQry.exe and Browser correctly returned the server machine name, instance name, version and port number, as it should).
What I'd really like to know is:
1. What is the point of listening on a dynamic TCP port, rather than on 1433? Especially when the named instance is in fact the only instance on the box? (I'd love to rename the instance to default, but I gather that's a whole world of pain, a re-install and restore would be easier)
2. What happens if the SQL Server service restarts and TCP 49944 is in use? I'm guessing that Browser would correctly report the new port number [X] now in use to clients - but then someone would have to open up port [X] in Firewall, which would be a PITA.
thanks for any input!
seb
May 18, 2012 at 8:21 am
Do you have the SQL Browser service running? If no, then dynamic ports are going to be an issue. The SQL Browser service listens on port 1434 and is used to allow clients to access SQL Server on dynamic ports without having to know what port.
May 18, 2012 at 8:52 am
Yep, Browser has always been running. Problems were initially caused by UDP1434 being blocked by the firewall. Opened that, and it works fine.
What I'm wondering (as in 1 and 2 above) is what the point of dynamic TCP port usage is (especially in this situation where there's only one instance of SQL on the box); and whether the actual port used by the SQL service might change, making nothing work until the new port is unblocked on the firewall.
May 18, 2012 at 9:03 am
First, you can't rename an instance. If you want to use the default instance, you have to install another instance. Then you have all the issues of moving databases, users, logins, jobs, etc.
You can fix the post that the named instance uses, and then configure the clients to access that specific port.
I have run multiple instances, and I haven't had a problem with having to open ports for clients to access.
May 18, 2012 at 9:40 am
It's just a default setting. SQL Server puts the default port for the "default" instance at 1433. For any named instance, regardless of whether it's the first or second installation on the host OS, it is set for dynamic ports. That's just convention. I suppose setup could determine if there is an instance installed and then decide, but I assure you that a percentage of people would be upset to find their default instance on a dynamic port. If you didn't perform the install, you would not be aware of the order, and the un-named instance being on a dynamic port would be very annoying.
I would recommend that you set a specific port and then configure alias on your various clients that contain the port. Limit the firewall to that port.
http://msdn.microsoft.com/en-us/library/ms177440.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply