First published on MSDN on Jan 07, 2007

SQL Server client stack has a facility to let user define connection aliases. Connection Alias can usually help user make connection eaiser, faster and with more convenience. However, if it's not used properly, it sometimes results in connectivity issues which might be difficult to isolate.

User can take advantage of connection alias in both MDAC and SNAC. The information is saved in registry. There are two tools can be used to define alias. One is SQL Server Connection Manager shipped with SQL Server 2005. The other one is SQL Server Client Network Utility shipped with Windows and can be found
at C:WINDOWSsystem32cliconfg.exe .

All connection alias information can be found under the following registry:

Basically, alias can be used to:
1) define a easy-to-remember (or use) name of a real SQL Server name.
2) redirect SQL connection with SQL Server to another SQL Server instance on any machine.
3) make connection to SQL Server using a specific protocol (e.g. TCP, Named Pipe, etc) with specific parameter (e.g. TCP port, pipe name).

Case 3) is probably the most frequent usage. Because an alias always has a pre-defined protocol, it can speed up your connection. When making new connections, SQL server client try various protocols (Shared Memory, TCP, Named Pipe, VIA, etc) in a sequence. There is a pre-defined default protocols sequence, but user can change the sequence using the tools mentioned above. SQL Server Connection Manager is for SNAC and SQL Server Client Network Utility is for MDAC. If a connection fails with one protocol, we may try connection with another protocols if there are still time for login. So, if a user knows which protocol the server is listening on and they want to used it, he can define alias to use that protocol directly, saving the
time to try and fail with other protocols. Also, he can chose a protocol which has better performance on his system.

Two use cases:
a) A user can make connection to SQL Server named instance without having to specify the instance name in applications. For example, you have a named SQL Server instance NewInst on machine MyHost. You can define MyHostAlias to use Named Pipe protocol with the following Pipe name \MyHostpipeMSSQL$NewInstsqlquery ,

b) A user wants to use TCP protocol and has already configured the SQL Server to listen a specific port (e.g. 3456) rather than the default port 1433. Then, he can define a alias to do so. User can also pecify IP address directly in alias, thus saving some time on DNS query.

In the above cases, since the TCP port and/or Pipe Name is known and have been saved with alias, user does not have to rely on SQL Browser to discovery the server, which saves time and exposes less security risk as well.

As you can see, alias has a lot of benefits. However, if not used properly, you may see issues hard to trouble-shoot. For example, you defined an alias called MyHost and let it connect to a SQL Server on MyHost using TCP. Later, your DBA decides to turn off TCP and let the server only listen on NP or change TCP port number, you may not be able to connect to MyHost unless you remember an alias is defined and delete/redefine the alias. It's usually very hard for a user to realize the alias is the root cause of the issue.

So, as a suggestion, you'd better always put some special words in your alias so that you know it's an alias at anytime.

Xinwei Hong, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights