When you first create your SharePoint farm, you are asked for the name of a SQL Server instance and the name of your configuration database. This applies to every version and edition of SharePoint, including SharePoint 2013.

SharePoint Configuration Wizard - Specify Configuration Database Settings

The server name you enter here is forever associated and required by the SharePoint farm. This name is hardcoded into the Registry of every server in the farm, current and future. And it can never be changed using any supported method, short of creating a new farm. It's unfortunate that this is the way Microsoft implemented this, but this is how it is. Most people expect to be able to change the name of their database server and when they hear this for the first time they think it's a cruel joke (SharePoint is a .NET application after all). The Microsoft forums are filled with such questions and shocked responses.

But wait. Why would you ever need to change the name of your SQL Server? Well, it happens and here are just a few reasons:

  • Performance - your farm has outgrown the instance or hardware it's on and you can no longer add in more RAM, processors, or disk. It's time for a new server.
  • Product upgrades - You'll find that installing the latest SQL Server edition and Service Pack keeps your SQL instance in a supported state. Usually the new versions have an increased hardware requirement and it makes more sense to spin up a new SQL Server machine rather than upgrade your existing server.
  • Retire and decommission old hardware - Maybe you've implemented a new company hardware standard or your hardware warranties and support agreements have expired and now it's time to rebuild any servers running on the old hardware.
  • Hardware failure - This happens more often than you'd care to admit and let's face it, who hasn't rebuilt a server because four disks in the RAID decided it was time to grind to a halt simultaneously?
  • Other server failures - I won't cite any sources, but Windows updates have been known to be the cause of killing a server or two, not to mention viruses, malware, or just plain broken drivers or software that causes a blue screen whenever there's a full moon. When a server is far beyond repair it's usually easier (and cost effective) to build a new server.
  • Clean up - It's not uncommon to see the SQL instance used by SharePoint to be shared with other applications. Sometimes it just makes sense to move the databases to a new instance (don't forget about the SQL Server licensing considerations to doing this).
  • Server renaming initiative - Ever had a new VP of IT or had your company bought by a larger company with their own way of doing things? I have a rule that every company goes through a server renaming scheme every 3-5 years. Comic book heroes, element names, or the names of peppers only work as long as you don't run out of names and as long as you can remember what's running on "CHIPOTLE".
  • Accidents - Your sysadmins are human and will break the servers (by accident of course, I would never suggest that an administrator would have a malicious reason to break your servers).

You can mitigate the risk for these issues with redundancies, backups, solid procedures, being proactive with vendor relationships, and in some cases even virtualization. But there's always room for improvement, especially when your SharePoint farm is tied with its hands behind its back to a specific SQL Server instance.

So let me introduce the SQL Server client alias. Actually, re-introduce, as we've written a post here before about the power of the alias. Habanero recommends using a SQL Server client alias for all new SharePoint 2007 (WSS 3.0, MOSS 2007), SharePoint 2010, and SharePoint 2013 farms (Microsoft recommends it too). So what is it, and how does it work?

The SQL alias consists of a name that you give your SQL-using application and a pointer to your actual SQL instance. You can name your alias anything you want, I suggest a name that makes it clear that is an alias so any future troubleshooters can recognize that your server name is actually an alias and then locate the actual instance easily. The word ALIAS in the name is a pretty clear indicator. The clever bit about the alias is that you can change where it points anytime you want, though when you do change it you had better have the databases SharePoint needs in the new location or you'll see a few exceptions and error events in the Application Log.

An Alias is an abstract concept and can be confusing, so let's look at a scenario I just made up.

Figure 1 - Fabrikam's medium farm

Here is Fabrikam's medium SharePoint farm. It hosts their intranet portal, a collaboration portal (team sites), a Records Center, and a custom support ticketing application. The farm has four SharePoint servers: two load balanced web front end servers (WFE1, WFE2) and two Application servers (APP1, APP2). The farm has the following services: Search, User Profiles, Managed Metadata, Web Analytics, and Office Web Apps. There is one database server: SQL1 with only the default instance and is shared by other applications in the enterprise.

Fabrikam created their intranet farm a couple of years ago. SharePoint is supported to run in a virtual environment so they created four virtual servers for the SharePoint servers, but back then they didn't quite yet trust virtualizing SQL Server. And to be honest, their virtual infrastructure wouldn't have been able to support the performance needs of their SQL Server. They bought a beefy server (4 processors, 8 GB of memory, and 200 GB of direct attached storage on 10K SCSI disks – blazingly fast at the time!) to host the databases for their intranet farm and other databases. This is where all the databases live to this day.

Since then, Fabrikam has made several big investments in virtualization. They have several blade servers as virtual hosts and have a couple of SANs with lots of fast SAS drives using RAID. Today, it's policy to create all servers in this environment; for Fabrikam, physical servers are a thing of the past. And now they have decided to migrate their SQL Server (SQL1) into the virtual infrastructure. This time though, SharePoint will get its own SQL Server (SQL2) instead sharing a server with other applications.

If they were to move the SharePoint databases to a new SQL instance, they would have to:

  1. Make a backup of the farm
  2. Remove all the servers from the farm (WFE1, WFE2, APP1, APP2)
  3. Create a new farm using the new instance (SQL2)
  4. Join all the servers to the farm
  5. Deploy all solutions
  6. Restore the backup of farm into this new farm

This is a labourous set of tasks and will require Fabrikam's administrators to work over the course of a weekend. The worst part is that it doesn't include the time that the admins are going to spend troubleshooting the issues users will experience on Monday morning when they receive exceptions on their custom ticketing application. It turns out there were some manual additions to the web.config files that someone forgot to document. Good luck remembering what those were! I hope no one gets fired for that one.

It doesn't have to be that sad. Let's rewind our clock back a couple of years to when they originally created the farm. Instead of specifying the SQL instance, the admins used a SQL Server client alias, "SQLAlias". It took a few minutes to add the alias on all four SharePoint servers but once configured there was nothing more that was needed. And remember, they added both 32-bit and 64-bit aliases. At the time we hadn't published our script to create an SQL alias: it would have saved a good three minutes of their time!

Now their farm looks like this in the eyes of SharePoint:

Figure 2 - Fabrikam's medium farm using an SQL Server client alias

This is the same topology as the version of the farm where they specified SQL1 directly, only the name of the SQL Server instance is different. The SQL Server client alias has abstracted away the specific instance name. Without this abstraction, it physically looks like this:

Figure 3 - How the SQL Server client alias translates the name

This isn't really complex. The SharePoint servers are still using SQL1 for storing the databases, but they think the database server name is "SQLAlias" and the alias itself handles the name translation.

This time, instead of a full farm backup and recreating the farm, the process for moving the database to the new instance is:

  1. Shut down SharePoint servers. We do this so the databases aren't in use during the move. You could alternatively stop all SharePoint services on the farm servers, but shutting them down is more direct and you don't need to remember which services to start back up on each server. Also, in the case of SharePoint 2007's Office Search Service (osearch), shutting down the service resets the index which is undesirable under most circumstances.
  2. Transfer all farm databases from SQL1 to SQL2. This can be a SQL backup and restore, a detach and copy of the data and log files, or whatever other method the admins are most comfortable with.
  3. Stop the SQL1 instance (simply stop the database engine service for this instance). This will prevent the farm from accessing the instance while we are updating the alias. Of course, when this happens the other applications will not be able to access their databases of the instance, but let's assume for this scenario that this is not an issue because the sysadmins are moving all the databases at the same time. Shutting down the instance is optional, it's done to show that the databases on the instance are not being accessed by SharePoint.
  4. Turn on SharePoint servers and update the aliases on all servers (remember both 32-bit and 64-bit aliases) to point to SQL2. Note that there will be events in the application log and the sites will be unavailable until this switch is completed as the alias will still be pointing at the stopped instance on SQL1.
  5. Open SharePoint. The sites should load as expected.

Figure 5 - Moving the databases to a new SQL instance and re-targeting the alias

Obviously there are more details around the configuration of SQL Server that are beyond this discussion of the alias, for example, the admins will need to make sure SQL2 allows incoming connections and has been set up to meet their performance needs. This same procedure can also be used if SQL1 fails for some reason. Restore the SQL database backups (because these exist, are current, and aren't corrupted) to SQL2 and update the alias.

This process takes significantly less time than the full backup and restore. Fabrikam's admins could do this during a maintenance window one evening while watching TV or surfing the Internet and be confident there will be no issues with the farm (hopefully they've sized the new virtual instance appropriately). Also, there is a rollback option — the old instance still exists and is untouched. Simply ensure the alias points to it and you should be back in business. For the farm backup and restore, once you delete your old farm there's no turning back!

But what if you created the farm and didn't use an alias? This is something we notice in our infrastructure assessments, is a common question asked by our clients about their existing farms, and as I first mentioned is regularly asked in the Microsoft forums. If we look at Fabrikam's original scenario, they did just this and used SQL1 as the database server name. This is extremely common and chances are you have a farm in your organization that is configured in this way. As I said, you can't change the name once you pick it so what else could Fabrikam (or your organization) do if they want to move their databases but not rebuild the farm? The alias can help in this way as well.

The nice thing about a SQL Alias is that your application will use the alias first before it goes searching for a server with the name. This means you can create a SQL Alias that has the same name as the original server (SQL1) but points to a different server (SQL2):

Figure 5 - Creating an alias with your original server name and targeting it at a different instance

And technically this will work fine. SharePoint accesses the databases stored on SQL2 and the farm sees the server name as SQL1. But it can be confusing for humans.

I mentioned earlier that you should name your alias in a way that suggests it is actually an alias, but in this case we can't meet this goal. We are able to move the databases but anyone who looks at the farm in the future will not realize the database server name SQL1 is really an alias. In fact, SQL1 may be a real server name running actual databases and in Fabrikam's case this could be possible if they don't decommission SQL1 (or perform a physical to virtual conversion of the server) and continue to use it for other databases. When you look at the databases on SQL1 the SharePoint databases may not exist or could exist but be old copies from the move (you should probably clean these up). If you go the route of creating an alias name that is the same as the original server name, it's important that you document this behaviour very well or you could end up with some very confused troubleshooters.

A SQL alias is an insurance policy on your SharePoint farm's SQL instance. At some point in the future you will have a requirement to move the instance. And it will be a serious, resume-generating requirement should you choose to ignore it. The simplest approach to moving your databases is to plan before you create your farm and use a SQL Server client alias.