How to setup a SQL Server alias and when is a connection alias useful for developers?

UPDATE: 5th January 2012

If the client machine does not have SQL Server Configuration Manager, then head over to this MSDN article,

   http://msdn.microsoft.com/en-us/library/ms190445.aspx

Aamir Qureshi has written an excellent comment for how to use the command line utility, cliconfg.exe, on a client machine which lacks SQL Server Configuration Manager.


Isn’t this old hat?

In a distant SqlBits, far, far away (actually the very first SqlBits) I gave two grok talks; the first on developers using SQL Server aliases to simplify development, the second on encrypting SQL databases using NTFS encryption.  As usual I promised to write it all up on this blog. 

Two years later, at the recent SQL Bits V, I mentioned aliases to a fellow developer. He followed up and asked for a link to my blog.  That’s when I discovered that although I wrote up the use of NTFS file encryption I didn’t write up the use of aliases. I can only apologise and have now finally get round to writing this up. As a bonus it now includes 64-bit goodness and a much, much faster (if dirty) way of configuring SQL Server Aliases.

Although a topic as old as SQL Server 2000, I still find it surprising how many developers are unaware of how useful a SQL alias can be so this may be useful to those new to this area of SQL Server.

So what is a SQL Server alias?

A SQL Server alias is simply a friendly name, configured on the client computer that points at a SQL Server instance.  This instance can either be installed locally or on a different machine on the network.  Think of an alias as an entry in a hosts file, a sort of hard coded DNS lookup a SQL Server instance.

Why would you want to use them?

A SQL Server Alias can be useful for several reasons

The first time I used them was in a small development team where most developers had installed SQL Server as default instances, (local), but some of the more anally retentive developers (myself included) had used named instances, i.e. <MyMachine>\SQL2005Std.  By using the same alias on all developer machines, any project could be checked out without having to edit the .config files to modify the database instance, reducing check in ‘noise’ within the version control system.

A second use is for simplifying the configuration of continuous integration (CI) servers. Similar to a shared development team, you create aliases on your CI server to perform integration testing on multiple variants of database servers, so you don’t have to change application configuration settings. Also, a developer can configure an alias on their local machine, run unit tests against that alias, and the CI server can use the very same alias pointing at a local SQL instance when the tests are run in an automated mode.

A third use allows a developer to quickly switch between different local database instances (i.e. SQL 2000, SQL 2005, SQL 2008, SQL Express), or between local instances and network instances such as staging or build servers to verify consistent operation of an application.  Again, all without editing (possibly) multiple application configuration files, or registry entries across numerous applications.

Pre-requisites for SQL Server Alias usage
 

  1. You must be willing for your clients to connect to the instance via a network protocol. It is not possible to use shared memory connections that are available when the client and instance are on the same system. I use TCP/IP connections for my aliases.
  2. On the system hosting the SQL Server instance you must have the SQL Server Browser service running (this is normally has Start Mode set to Automatic).
  3.  On a 64-bit system, if you have both 32-bit and 64-bit clients, you will need to create an alias for BOTH 32-bit and 64-bit clients.


Configuration of the SQL Server Instance (to enable a network protocol)

Microsoft now quite rightly ships products locked down by default, and SQL Server is no exception, so you will need to enable your chosen network protocol, in this case TCP/IP.

Run the SQL Server Configuration Manager, Programs -> Microsoft SQL Server 2005 (or 2008) -> Configuration Tools

Open the SQL Server Network Configuration tree branch (on my machine it is listed as 32-bit for SQLExpress, 64-bit for 2005 Dev, 2008 Std), and highlight the protocols option for your SQL Server Instance, you can see the protocols listed on the right hand pane, including TCP/IP

SQL Configuration Manager - Protocols

Double click the protocol you wish to open the protocol dialog, so you can modify the settings for that protocol,

SQL Configuration Manager - Protocols Dialog

While you are in the SQL Server Configuration Manager, it might be worth ensuring that the SQL Server Browser service is running and set to Automatic.  You can view the status of the services in the SQL Server Services tree branch.  The various services are listed on the right hand pane,

SQL Configuration Manager - Services

Double click on the SQL Server Browser service to modify it’s properties, and ensure the Start Mode is set to Automatic.

Client configuration - how to create a SQL Server Alias (easy, official way)

Run the SQL Server Configuration Manager, Programs -> Microsoft SQL Server 2005 (or 2008) -> Configuration Tools.

Open the SQL Native Client 10.0 Configuration tree branch (for 64-bit systems you will have a 32-bit specific branch and a default, 64-bit branch), and highlight the Aliases option.

SQL Configuration Manager - Alias

All current aliases are displayed in the right hand pane. To create an alias, right click and select New Alias. To modify an existing alias double click the alias name.  You edit the details in the alias dialog,

SQL Configuration Manager - Alias Dialog

In this dialog the alias is called NUnitAlias, the protocol has been set to TCP/IP, and the SQL Server instance is set to 6910p-WIN7BETA-SQL2005dev. When a client connects using the instance name NUnitAlias it will connect to 6910p-WIN7BETA-SQL2005dev using the TCP/IP protocol.

Client configuration - how to create a SQL Server Alias (quick, dirty, unofficial way)

You may have noticed that to configure an alias we were using the SQL Server Configuration Manager, which is part of the SQL Server Client Tools set. I decided to prove that was all you needed (i.e. you don’t need to install an actual SQL Server instance) by downloading these tools. Except you can’t, well, not as a standalone installation.

I grabbed my nearest MSDN SQL 2008 R2 DVD, and although the SQL Server Native Client has a separate installation the client tools did not.  So I installed the SQL Server Express, selecting a custom option, installing only Management Tools – Basic and SQL Client Connectivity Pack SDK. 

That worked fine, but I was uncomfortable with that amount of effort and a Bing or two later I found this useful SQL blog article from 2007,
 
   http://blogs.msdn.com/sql_protocols/archive/2007/01/07/connection-alias.aspx

So it’s all in the registry, it’s that easy. I tested this theory on a clean Windows 7 Ultimate installation running as a virtual machine under VirtualBox. I added the ConnectTo registry key, and a value for an alias, and used a simple UDL file to test a connection via Microsoft OLE DB for SQL Server using the alias I entered in the registry,

Registry Editor - Default Key

It occurred to me that on 64-bit operating system, if there were only one registry key, why would we need to create two aliases.  The registry settings shown in the image above are for the default for the operating system; for a 32-bit operating system this is for 32-bit clients, for a 64-bit operating system this is for 64-bit clients.

For a 64-bit operating system the 32-bit client alias values are stored in a separate Wow6432Node as shown here,

Registry Editor - 32-bit client on a 64-bit operating system

Summary

So that’s it really, aliases for 32-bit and 64-bit systems, including a very handy new discovery of setting the alias values directly via the registry.  With the registry solution you can include alias configurations in version control, and use simple .reg files to configure aliases on the fly (useful in a CI server script) so you no longer have to maintain a myriad of application configuration settings for each server instance you might want to use.
 

Print | posted on Tuesday, December 8, 2009 10:46 AM

Feedback

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Thomas G Mayfield at 12/10/2009 8:57 AM Gravatar
Is there a similar facility under HKCU? I've an inherited setup that has staging and production both running on the same server with different paths and databases (but could easily be shifted to different MSSQL instances).

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Liam Westley at 12/15/2009 8:16 PM Gravatar
Thomas,

My understanding is that SQL aliases are created on a system basis, and cannot be created beneath HKEY_CURRENT_USER. This makes sense as some IIS application pools or services are likely to be running under a specific user account, and each would have to have separate aliases required.

Thanks

Liam

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Cynthia at 9/8/2010 3:49 AM Gravatar
If I created a new alias name for the default instance, will all the applications that already use the default instance need to change the connection strings?

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Liam Westley at 9/12/2010 7:44 PM Gravatar
@Cynthia creating an alias does not remove the local instance or any other instance name, it is just a means to give a server additional names for access.

Although I have worked on systems where all applications used local instances in connection strings, but my developer laptop only had named instances. So I actually had to create aliases for a default local instance; '(local)', 'localhost' and '.'

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by petdoubt at 7/23/2011 11:45 AM Gravatar
Unfortunately this does not work. Firstly aliases are probably the last thing you would use to connect to a sql server. TRUST Me I have spent the last 5 hours trying to do what you are doing & IT SIMPLY does not work! Alaises sometimes work & sometimes they don't. Sql browser also has a terrible reputation for not updating its aliases list. SP4 for sql 2000 still not address properly the issues with regard to sql browser not updating the aliases list. So unless I am missing something..this is only going to give people headaches. Its tough enough as it is having to deal with sql 2000, 2005 & soon 2008 without having to deal with the inconsistencies between the various versions of sql server & os when it comes to aliases and various other connectivity issues.

Like you I also thought this was a nice I idea but definately would give this a big thumbs down. Feature should actually be removed from future versions of sql server; its just a total hassle in my view.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Liam Westley at 8/3/2011 7:46 PM Gravatar
Personally I've never had a problem with it and neither have some of my colleagues. Admittedly we are developers using it to solve issues with server instances on our local machines and on build servers.

I've used it for SQL Server 2005, 2008 and 2008 R2 and had not problems. Only my experience though.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Umair Rafiq at 10/5/2011 5:37 PM Gravatar
Worked like a charm! Thanks

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by SP at 10/14/2011 2:39 PM Gravatar
worked for me. thanks.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Dforeman at 11/4/2011 3:55 AM Gravatar
I have an issue where I'm setting up a new SP farm. Ive installed Power-pivot and MS has hard coded a instance name into the installation. However I'm already using another instance for SharePoint production which is where I want to move the new farm configuration to. I'm thinking that I will have to create an alias for Which will call powerpiviot and move all the data bases to that server. then rerun a tool to re connect all the databases to the application. I don't think i can alias one instance to another instance.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by KW at 11/7/2011 2:15 AM Gravatar
If your SQL Server instance is on a cluster (two nodes) and is a clustered instance, what value must go in the 'Server Name' box (this is using the Client Network Utility).

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Liam Westley at 11/7/2011 4:38 AM Gravatar
@KW

I've not aliased a SQL server, but having played with SQL clusters I always used the cluster name rather than the actual name of either of the cluster servers when using SQL Server Management Studio. This was to ensure I always connected to the active node.

I would assume that any SQL Server alias would use the same logic of accessing the cluster name rather than individual servers.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Nahum at 11/26/2011 4:00 PM Gravatar
Thanks!!
It works!!

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by calin at 2/1/2012 1:23 AM Gravatar
cool trick - exactly what i was looking for.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Punith at 2/20/2012 5:31 AM Gravatar
I want to create an alias at server level to a Named Instance in SQL Server 2008 R2, so that i can just provide alias name to my developers to include in Connection strings. The idea behind is we do not want to edit the codes if we make any changes to DB such as moving from one instance to another. Any help would be greatly appreciated. Thanks in advance!!

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Oleksandr at 4/5/2012 3:33 AM Gravatar
Thank you a lot - very useful and helpful, other sources do not mention SQL Browser service and duplicating records for 32 and 64 platform, and I was struggling.

Anyway I discovered, if you want to establish alias for named instance you should specify all variants used in legacy connection strings - in my case "localhost\SQLExpress" and ".\SQLExpress" was used, so I end up creating 2 aliases both targeting the same instance.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by kbrown at 4/18/2012 4:32 AM Gravatar
I have a server running two instances of SQL Server (2005 standard and 2008 R2 Standard). I have several applications running on the same server that are configured to connect to the 2005 instance.

The 2005 instance uses the default instance name (just the server name), while SQL server 2008 uses an instance name such as server\SQL2008R2.

I'd like to migrate all databases attached to the 2005 instance over to the 2008 R2 instance. To avoid changing the configuration strings (some require recompile), I'd like to use an alias to redirect connections to the default instance name (2005), to the 2008 instance.

Is this possible? What should the alias name be so that it evaluates to the default instance name on a given server?

Thanks in advance.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Oliver at 6/14/2012 3:37 AM Gravatar
Is it possible to setup an alias on a clustered instance? If so how is this done?

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by nano at 7/30/2012 9:00 AM Gravatar
On which side these aliases must be created ? Server side or client side ? How can a client resolve an server side alias ? I mean an alias which has been created on the server machine with SSCM.
Thanks.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Bright at 1/6/2013 12:40 PM Gravatar
Does every client machine that connects to remote sql instance has to have that registry entries (alias) in order to be redirected to new sql server?
What is the role of sql browser there?
I hope I only have to configure alias on ANY sql instance in my local network, and sql browser will push that alias info to the network and all the clients could connect to sql using that alias name to the sql instance that alias points to.

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Finbarr at 10/17/2013 3:33 AM Gravatar
Can I get clarity, does the alias have to be setup on every client, or can I set it up on the server only.

I went ahead and set it up on the server and it works fine if I connect on the server but it doen't work when I try to connect from any client.

How can this new alias get pushed out to all the clients

# re: How to setup a SQL Server alias and when is a connection alias useful for developers?

left by Liam Westley at 10/17/2013 3:57 AM Gravatar
@Finbarr The Alias does only work on the machine on which it is configured, so every client requires that alias. If you want a mass rollout, then configure it on a single machine, find the registry keys, export them and push those to all the clients.
Post A Comment
Title:
Name:
Email:
Comment:
Verification: