in Development

SQL Server Aliases

When you develop applications that use databases you usually need to work with several database servers. i.e. Your local computer and a test server. If you are the only developer and have only one application that’s not a big problem, but if you are in a team  is quite annoying when someone messes up the connection strings to point to different servers depending on the task he wants to accomplish.

A common approach I’ve seen to deal with this issue is to modify the host file, so you can point to the right server without having to change all your connection strings. Unfortunately this does not work when the server you want to target is a SQL Express version or any other SQL Server with a named instance.

A better approach is to use SQL Server Aliases, with them you can indicate the name that is going to be used to access a SQL Server, no matter whether this server is your localhost or a remote server. To do that you only need to open the SQL Server Configuration Manager, expand the node of the SQL Native Client Configuration and go to the alias section as shown in the image below.

SQL Server Configuration Manager

Right click on the node, select New Alias and introduce the name of the alias and the connection details of the target server. The image below shows how we have configured our local version of SQL Express to be accessed with the name “TestDBServer”.

SQL Server Alias

When you introduce the connection details, be sure to verify they match with what is set in the protocols configuration of the target server.

That’s all, the new alias is set at machine level, so all the applications residing in the computer where the alias is configured can make use of it.

I hope this helps.