Using SQL Server Aliases to Facilitate a Server Name (and Instance Name) Change

While researching SQL Server clustering, I ran across a requirement that should have been obvious to me, but I overlooked until recently regarding naming conventions.

Problem
In our current environment we have 4 physical servers, each running a single instance of SQL server.  Each instance is named the same on all servers, we'll say INS.  So we have an environment something like this:

It's been slightly simplified, but you get the idea.  The red arrows indicate the same instance name on each server.

Now we are going to set up SQL server clustering using an active/active architecture.  This means that each server will still only host one active instance, but will also host one or more passive instances of SQL server, as below:

This being the case, each instance name must be unique.  This meant renaming our SQL instances.  We are re-installing SQL server anyway, so the actual renaming of the instances is going to be easy.  The problem lies on the development side of the aisle.

We did anticipate the possible need to rename servers/instances when we set up our custom SQL load balancing architecture (more on that later) and created a table in a central SQL server that we use to look up the server name to use to get the data we're looking for.  So, in a perfect world, we'd be set.

However, because of a combination of old code, lazy developers, and code hacks, I'm anticipating only about a 50% chance of all code still working after the switch over.  This, obviously, isn't acceptable to the higher ups so we needed a different solution.

Solution:
The first suggestion was to preemptively re-install SQL server on the existing servers with the new instance names before implementing clustering.  Aside from the issues surrounding client-downtime, this would provide no fail-safe for code that's non-compliant and we's still down until that's fixed - there's no reverting back to the old names.

So, I decided to use SQL Server aliases to set up the new names to point to the existing instances now to facilitate testing before going live with the new names.  This also provides the ability to map the old instance names to the new ones in the event that testing missed something after the switch-over.

First, I confirmed through testing that aliases will work with named instances.  There is little information out there talking about that, so a quick test was in order.

SQL Server Configuration Manager
The most talked about method to setup aliases is through the SQL Server Configuration Manager.  It's pretty straight forward, as shown in the screenshot below.

This maps the instance SERVER1\INS1 to the instance SERVER1\INS.

This also allows you to set the protocol you want the alias to use to connect as well as any properties related to the specific protocol.  For TCP/IP, leaving the Port No blank will use dynamic ports if that's how your SQL instance is set up.  Note that you must have SQL Server Browser running on the server to use dynamic ports.

Also note that this is setup under the 32 bit node of the Native Client Configuration.  This is because for testing, I'm using SSMS, which is a 32 bit app.  In production, it is wise to setup the aliases for both 32 and 64 bit clients.

The problem with using SQL Server Configuration Manager is that it is only installed as part of a SQL server installation, which doesn't exist on our application servers.  Installing all of the SQL configuration tools is a bit excessive just to setup aliases.

SQL Server Client Network Utility
It turns out there is a tool that comes with Windows called the SQL Server Client Network Utility that allows you to setup aliases in much the same way.  It's located in the Windows/System32 and Windows/SysWOW64 directories for bot the 64 bit and 32 bit versions, respectively.  As with above, it's recommended to configure your aliases in both.

Screenshot of the alias configuration window is below.

It's very similar to the other configuration tool with the same options, just laid out slightly differently.

So, all I needed to do was go to each machine, setup 4 aliases per machine in both the 32 and 64 bit versions of the this utility and I was set.  We have a fair number of application servers, including web servers, processing servers, and workstations, so I was prepared to send a team around to setup the aliases on each machine individually.

However, I noticed when I opened the SQL Server Client Network Utility that comes with windows that it was pre-populated with the alias I setup in the other utility.

Windows Registry
As it turns out, both of these utilities simply add entries in the registry.  A quick search for "SERVER1\INS1" using regedit found these two keys:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
and
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

In there, there is simply a string value per alias name.

The value differs based on the protocol chosen, but you can use the utility to create one then copy the format for the rest.

One more note about server names.  You should use the FQDN name for all server names, but again, in case you have some rogue developers, you should add aliases for both the FQDN and just the machine name.

Once you have the registry entries, you can export those two keys and either import them on each application machine or, if you want to, use AD Group Policy to push them out automatically.  That's beyond the scope of this article, but if you're running a Windows Server 2008 environment, check out Using GPOs to Set Custom Registry Entries or if you're in an older environment, you can head over to Deploying Custom Registry Changes through Group Policy.