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!
SQLAndy 
I”m not sure the value of hardcoding port numbers. In the spirit of your suggestion, I recommend app-specific DNS names. If there is ever a need to distribute load to multiple servers, you need only update DNS/cname record and applications move onto the new server. Possibly the only more-flexible solution would be to have applications connect to a DB to query connection strings providing application name/role, IP address, etc. and serve the follow-up connection information as the result – but that will certainly be work to setup/maintain (and explain to others)
LikeLike
In almost every place I have worked, there has been a dedicated database server hosting one database that serves multiple clients running the same app, or it serves one web server which in turn serves web applications. If this is the most common model, and it seems that it is, then what purpose does using dedicated ports serve?
LikeLike
Hakim, I think you have to go back and look at the post again – there”s no direct/immediate gain, it”s a monitoring tactic, and a hedge against a rainy day. It”s mostly about bandwidth usage/limiting. I wish I had a stronger case to make!
LikeLike
I”ve implemented a port change once but the scenario made the process easy to manage and only I had to worry about configuration. I had a web based application hosted in a customer”s DMZ for outside access and as part of the “lock down” security model we elected to set the SQL Server on a non-standard port. The firewall team then locked down the firewall so that the only access to that port through the firewall was from the DMZ web server.
I”ve dabbled with port changes in other application scenarios but never got to production with them.
LikeLike
Rob, non-default ports are worth doing. I”m surprised at the number of vendors that only support the default port. My guess is they haven”t tried it and don”t want to invest any effort, easier to say no! Doing something like this definitely takes buy in from everyone involved. It”s not hard, it”s just a matter of doing it.
LikeLike
I like both ideas for different things. Using multiple ports for bandwidth throttling is a great idea.
Using multiple DNS names is great when your server supports multiple databases and applications. I prefer using DNS aliases for this. When you decide to split those databases out to another server, you just detach the databases, attach them to the new server, point the alias to the new IP address.
Abstraction is a wonderful thing.
LikeLike
Chuck, I”m a huge fan of DNS aliases for apps and/or databases, depending on how you view the world. Abstraction IS a wonderful thing!
LikeLike