I don’t do too much with SQL, but this one got me for a while, so here’s my story on SQL Server Configuration Manager Aliases.

I had a particular server that couldn’t connect to a specific instance on SQL on another box. Other servers appeared to be fine, but each time SQL Server Management Studio was run and attempted to connect to servername\instancename, it would instead connect to the default instance.

It didn’t matter who logged onto the server either. It would never connect to that secondary instance and I couldn’t work out why.

After much digging and testing, I resorted to reading through forum threads on Google searches, hoping for an idea. What I eventually found was the existence of SQL Client Aliases. These are like hosts file records – hard coded results for connecting to a specific server:

In Sql Server Configuration Manager, you can define an alias for what you’re connecting to. Servername\instancename could map to serverb\instancename or servername\instance2 – this is great when doing testing and wanting to point a server at a different SQL database or instance without changing a bunch of settings.

However, the other catch is the port specified. In the above example, the default SQL port 1433 is used. Makes sense, but each instance uses it’s own port, or uses a dynamic port. I soon discovered that if you try to connect to a SQL instance and have a port defined, the SQL instance you actually connect to is whatever is listening on that defined port.

An easy thing to find if you know where to look for it, and now I do. Hopefully this helps others who come across a similar scenario!