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
- Hard code a connection (yuk! – certainly not to a machine name.)
- Use a DNS aliases.
- 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.