Using a Server Alias to connect to SQL Server Instance(s)

When connecting to SQL Server from your PC or from your application/web servers, you can choose to create aliases for connectivity.  Before we start getting into the nitty-gritty details of aliases, let us see how you can set one up.  You can use SQL Server Connection Manager in SQL Server 2005 to set it up and in case the tools are not installed, then you can also use cliconfg.exe (note – there is no i in confg) which is located under C:\Windows\System32 folder.  If you use the connection manager, you will get a screen like this one:

You can then create a new alias as shown in the next image:

If you are using cliconfg.exe (the same is called in SQL Server 2000 when you use the SQL Server Client Network Utility), you will get a dialog box like the one shown below (showing the alias tab):

As you can see from above, the same alias that we had created using the SQL Server Connection Manager is automatically visible using the cliconfg.exe as well – that is because both of them read this entry from the registry.  The place where these connection aliases can be found in the registry is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

So, now that we have seen how to go about creating an alias and where it is stored, what exactly is the use of this feature?  There are a couple of benefits of using aliases:

1) An alias can be used to define a user-defined name for connecting to a server -even if the IP address changes later on, there are no changes to the connection string – you just need to update the alias and the application will keep on working as before.  You will not need to specify any instance name in the application connection string(s).  And using an IP address directly in the alias definition can also save you some time in doing the DNS look-up.

2) You can make connection to SQL Server using different protocols: TCP/IP, Named Pipes etc. and specify specific parameters like the TCP port or the pipe name etc.

3) Aliases are also good for performance reasons.  Since an alias has a pre-defined protocol, it can help you speed up the connection.  Think about what happens when a connection is made to SQL Server from a client application.  It has to try various protocols in the order that they are defined in your SQL Server Connection Manager (SQL 2005) or the SQL Server Client Network Utility (SQL 2000).  If you already know which protocol and port your server is listening on, by configuring an alias you can by-pass the discovery phase that SQL Browser service goes through.  In addition, if you know and have benchmarked your application using a specific protocol, you can standardize the alias technique across the board.