June 7, 2017 by Kenneth Fisher
If you’ve worked much with named instances you’ve probably had to deal with the question “What port is that instance on?”. You probably are aware that the default instance uses port 1433. You might also be aware that the browser uses port 1434. What’s the browser I hear you ask?
The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer
Basically, when you reference an instance by name the browser is called and returns back the port information so you can create the connection.
Now what you may not know is that while SQL Server instances use TCP ports, the browser uses a UDP port. So what is UDP? For that matter what is TCP? Well, I’m going to tell you straight up I’m a layman here so I’m not going into any detail but they are both communication protocols.
- UDP User Datagram Protocol
- TCP Transmission Control Protocol
Here is the main difference as best I can tell, and again I’m a layman so if you know better please feel free to comment. TCP sends numbered packets across the wire. The receiver, upon getting them, checks and makes sure it got what it was supposed to. Ie the packets are all there, there are no errors in the packets (I assume there is some form of checksum?) etc. If everything is ok then it sends back a message saying all good. If not then the sender resends the packets. This can obviously be somewhat slow (depending on the reliability of your network) but it avoids errors as much as possible. UDP on the other hand, goes for speed over reliability. There is none of the error checking and if the datagram (UDP’s version of a packet) sent is lost or incorrect, oh well. But without all of the error checking and need to send data back and forth it’s faster.
This is a SQL Server blog so why do we care? Because knowledge is power! Right? That’s what they keep telling me anyway. Well, ok, no, this might not make that much of a difference for you (it might though, who knows?) but if you happen to see a SQL Server instance (that watches on a TCP port) using port 1434 and you happen to know that the browser uses port 1434 (UDP port) you won’t get confused when everything works! Or run down this particular rabbit hole for no reason if it isn’t working.
So what parts of SQL use UDP? Well, the browser as we’ve already talked about. Kerberos uses UDP. It’s not exactly SQL Server but it’s certainly important to it. Replication and log shipping use UDP ports 137 and 138 for file sharing. IPsec (another related security thing) uses UDP ports 4500 and 500. That’s all I’ve been able to find so far. If you know more feel free to put it in the comments.