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.

3 thoughts on “UDP vs TCP

  1. bad Oedipus says:

    Given that Knowledge is Power and Time is Money and Power is Work over Time.
    We can assume Knowledge = Work / Money and furthermore Money = Work / Knowledge.

    So to get rich one needs to work much harder than they are smart or be stupid as all get out.

    …this explains so much about upper management at my company.

  2. TCP cares about your packet, UDP doesn’t.care about your darn, dirty packet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: