Flexible connections and an old friend


Just lately I’ve found myself involved with an app that needed to run on a server and nearly always (although not quite always) connect to the same SQL Instance.

And as the app progressed through the various environments I needed the flexibility to change that connection easily.

I had three suggestions from my team

  1. Hard code a connection (yuk! – certainly not to a machine name.)
  2. Use a DNS aliases.
  3. Use a SQL Alias.

Now, the DNS alias initially sounded the best – I see more and more enterprises insisting that this is the standard method, and I’m fully in agreement most of the time.

The first thing I though of was that last time I used a DNS aliases it wasn’t possible to configure the port – that may have changed, but for us there was a bigger issue.

Changing the DNS alias would take a service desk request to the IT team and that could potentially take a long time – even in development and test.

Yes, modern DevOps teams should make changes like this simply – but not all enterprises are at this level of maturity. So another solution was needed.

Enter SQL Aliases. We simply made a SQL Alias on the machine – gave it a standard name eg – SQL_APP_ALIAS, configured the port and away we went.

We created 2 of these a 32 bit alias and a 64 bit alias – just so we didn’t get caught out!

Now, I created the alias using the standard SQL Configuration manager.

However, while researching a way to create the alias problematically (think PowerShell!), I found an old friend from past – The SQL Server Client Network Utility - cliconfg.exe.

As far as I can remember, I’ve not used – or seen – that since SQL Server 2000 days.

The useful thing about that is apparently it ships with windows and will allow the creation of aliases on a vanilla machine – interesting.
Anyway, to cut a long story short, I now simply use the name of the alias to get a connection to the server and I don’t have to rely on the IT department (and a service desk request – that could take some time) to get my alias changed.

Maybe i’d just forgotten about that – but it’s good to get re-antiquated with old friends every so often!

Hopefully – one day in the not too distant future – we’ll see teams working closer together – kind of like that DevOps thing!

anyway, have a great day.







About Martin Catherall

Martin Catherall is Senior SQL consultant at SQL Down Under, based in Melbourne, Australia. Martin is also a Microsoft Data Platform MVP and Regional Mentor (RM) for the Professional Association for SQL Server. Prior to relocating to Australia he was extremely active in the Christchurch, New Zealand data community - founding the local SQL Server user group and organizing SQL Saturday Christchurch - which later became SQL Saturday South Island. He likes learning interesting stuff about processing and storing data and passing on his knowledge. In his spare time he likes to learn guitar and hang out with his two young sons Callum Glen and Robert Michael.