Systems Engineering and RDBMS

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

Posted by decipherinfosys on November 26, 2007

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:

alias-1.jpg

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

alias-2.jpg

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):

alias-3.jpg

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.

7 Responses to “Using a Server Alias to connect to SQL Server Instance(s)”

  1. […] 3) Using a server alias for making the connection – here. […]

  2. […] found an article that outlined the three benefits of using a SQL Server alias with your application. In short they […]

  3. […] Using a Server Alias to connect to SQL Server Instance(s) « Systems Engineering and RDBMS (tags: sql administration) […]

  4. […] SQL Server Alias https://decipherinfosys.wordpress.com/2007/11/26/using-a-server-alias-to-connect-to-sql-server-instan… […]

  5. […] so you get better performance for your clients ! Refer to this great post about CliConfg.exe: Click Here by Or Biran תגים:SharePoint 2010, SharePoint 2007, […]

  6. […] future: implement SQL connection aliases. An article describing how to do this can be found here: https://decipherinfosys.wordpress.com/2007/11/26/using-a-server-alias-to-connect-to-sql-server-instan…. Aliases are also particularly helpful from a disaster recovery perspective, as they introduce a […]

  7. […] future: implement SQL connection aliases. An article describing how to do this can be found here: https://decipherinfosys.wordpress.com/2007/11/26/using-a-server-alias-to-connect-to-sql-server-instan…. Aliases are also particularly helpful from a disaster recovery perspective, as they introduce a […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: