The Port Change Issue
On a project where the SQL team has a policy of changing the SQL port away from the default of 1433?
This does not pose a huge problem for your intrepid Skype (or Lync) deployment engineer. If you are needing to know what to do, and maybe you have, oh, 30 or so front ends to modify, then maybe I can help you out a tad.
The issue is modifying the registry to tell your host server where to go to access the requisite port on the target SQL server. As it turns out, I had to remember this, as it has been a bit since I had to last do this task.
The Simple Fix to the Simple Issue
Luckily for you and me, it seems that every copy of a Windows operating system I looked at for this post (Win7, Win8, Win10, Server 2008+) have a utility in \windows\system32 called cliconfg.exe. You can read up on that utility here.
A wonderful tool. Here is it in Windows 10 form. Which looks the same as Win7, so I think they will all pretty much appear to be the same. Actually, the Win7 version has a different set of window frames, so the appearance is more rounded instead of the ugly-ass Win10 metro crap. But I digress.
What we need to do is select the Alias tab…the select Add.
For the purposes of this exercise, I need my system to talk to my SQL server (FQDN = sqlalwayson-a.tsoorad.net) on port 49001. So, you set it up like this and then say OK.
Follow up that OK with an APPLY and your newly modified operating system will for thereafter talk to SQL server sqlalwayson-A.tsoorad.net on port 49001 vice 1433. Simple. Easy. Works well. Less filling. Man, I am thirsty!
But Wait! What if…
…you have like four user pools, and they all need to talk to the same monitoring server, but different archive targets per pool? And what if there are like 30 front ends that need this modification, and every time you type this stuff in there is the possibility of spelling errors that mean system failure. Now, I am sure there is some folks out there in techie land that are starting to chant “PowerShell! PowerShell” - but in this case, I am going to ignore them, and simply export a registry key, and then incorporate that into my server build process – which can be PowerShell-ized if you wish.
Here is the registry key to export. HKLM\software\microsoft\mssqlserver\client\connectto
In my project, we had four SQL AG clusters, each with two nodes, a cluster name, and the AG name; all that needed to resolve by DNS. So, our registry key looked somewhat like this: 16 entries with AG, cluster, node1, and node2 per supporting SQL cluster. We then simply imported that into each server at build time.
The SQL mavens might well change ports on you. If they do, there is an answer in form of cliconfg.exe. If the scale is a tad larger than manual typing will cover, you can regedit your way to success.