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
- 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.
- 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).
- 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
Double click the protocol you wish to open the protocol dialog, so you can modify the settings for that protocol,
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,
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.
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,
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,
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,
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.
