Most applications connect on whatever the default port is, typically 1433 for SQL, though it’s common to change that to something non-standard as part of security efforts – making it just a different port that all SQL traffic uses.

From a pure SQL perspective that’s fine. We can usually identify traffic using Profiler via application and/or hostname, or even other attributes in harder cases. Where it gets interesting is when you have a congested pipe from a client site. Imagine that you have a site that has been fine, now everyone is complaining of slow network performance. The network team looks at the traffic and sees that a large portion of it is SQL traffic, for our purposes running on 1433. At that point we can determine source and destination, so we can probably track it down, but that doesn’t mean we can fix it.

Why can’t we fix it? Well, maybe it’s a report that runs on an ad-hoc basis, one that would take a lot of time to tweak and test before deploying. Or, maybe it’s a third party app that you truly cannot change. Or you’re in a code freeze due to a promotion, audit,whatever.

To be fair,a lot of times you can fix it. You find the query that doesn’t have a where clause and add it, or you move some of the data out of the table for the short term to reduce the pain.

But if you need to fix it now, wouldn’t it be nice to have an option?

This is where if you can get out of the SQL box, you do have options – rate limiting at the switch or router. You set a max amount of bandwidth that the traffic of that type/port can use, and presto, every other app gets some bandwidth to use. The problem is that you’ve lumped all the SQL traffic into one bucket. Wouldn’t it be nice to have a way to manage just the one app? That’s where the dedicated port is a nice thing to fall back on.

There is also an interesting security benefit, instead of opening up 1433 between all your servers, you can only open up the routes you need. Or you could call it more work, more firewall requests to submit!

If you build your apps in a way that you can easily change the connection string (and by implication, the port), you could always change it when you hit this particular problem. You’d have a minor outage, but you could do it. If you have 39 apps across the business, do you want to manage 39 ports? It’s not hard, it’s just one more thing.

It’s an interesting idea. Is it worth doing? I’m fond of abstraction points, shim points, places where I can easily change behavior if I need to, and this fits that. I can see it being hard to get adopted because it’s different.

So is it worth doing? Do you do it already? Curious to hear what you think!