Sunday, 21 October 2012

Move/Change SharePoint 2010 Database Server

This, according to me, is a very possible and real -world scenario:

Your client (or your organization, for that matter) is planning to upgrade the databases, or the database architecture/topology and have come up with an entire new set of hardware which will host the databases and you are assigned the task of moving only the SharePoint databases to the new server.

Ordinarily, if this were a .NET application you would simply ask your DBA to backup/restore your databases and make changes to your connection strings in the config file and that's the end of that story. (Please note that I do not claim to be a .NET specialist and I apologize for hurting the sentiments of .NET developers if I have over-simplified the complexity of their task)

But, as I have said time and again "things are not that simple in SharePoint World"!

So, here's what Microsoft have to say about the method of achieving the database move from one server to another for SharePoint: SharePoint 2010 - Move all databases

In simple words, you need to apply an SQL alias to all your SharePoint servers in the farm, so that whenever a request for the "OLD" SharePoint Database server is sent from any SharePoint server it is redirected to the "NEW" Database server. Easier said than done!

To quote one of my colleagues - this is a very "weak" solution because -
  1. The reference of the old database server is never removed
  2. All logs and statistics mention  the old database server name.
To a person who has been newly introduced to the SharePoint farm and is debugging certain issue it is very confusing to figure out that the database server mentioned in the log files is just an alias and the actual database server is different. It could mess up his life and could potentially cause serious mental strain!

The other method is to re-create your farm and re-configure all services with the new database. Ofcourse, you would migrate the content of your Web Applications, residing in Content Databases, to the new server through backup/restores. Here is the link that sheds more light on this method. This is a possible solution if you are dealing with a small farm but if you have a multiple server farm with huge amounts of data residing in SharePoint, which has been crawled by the Search Services and User Profiles running into thousands then re-configuring all services might give you a nightmare.

I am going to concentrate on the former approach of applying aliases in this post. The steps to apply the alias and, therefore, effectively replace your database server are:
  1. Restore databases to the new database server
  2. Remove the SharePoint server from your farm
  3. Apply the SQL alias using cliconfg.exe
  4. Reconnect the SharePoint server to the same farm.
Below these steps are explained in detail, but before you do anything else, please make sure you have the passphrase for your SharePoint farm. The passphrase is required to re-connect the servers to the farm and if (like me) you had not initially setup your farm, there is very high possibility that you do NOT have the passphrase. Refer this post to set/change your farm's passphrase

Step 1 - Restore databases to the new database server -
  • Stop all SharePoint related services on all servers (except the database server) in your farm. Also, stop the IIS service on your WFEs. This is to ensure that no changes will be made to the databases while backup is in progress
  • Backup the SharePoint related databases
  • Restore to the new server
  • Start all the services

Step 2 - Remove SharePoint server from the farm -
  • Logon to your server
  • Run the SharePoint 2010 Products Configuration Wizard
  • Disconnect the server from the farm (please know the passphrase before doing this)
  • Let the wizard complete
Step 3 - Apply the SQL alias to your server -
  • Open command prompt and type cliconfg.exe and the return key
  • An SQL Network Utility will appear that will allow you to enter details of your alias
  • Click on the Alias tab and click Add
  • In the Network Libraries select TCP/IP
  • Write the old database server name in Server Alias and new database server name in Server Name text box.
  • Let the Dynamically determine port check box be selected (and select it if it is not already selected)
  • Click OK
  • Then click Apply and OK
Step 4 - Reconnect the SharePoint Server to the same farm -
  • Again start the SharePoint 2010 Products Configuration Wizard
  • Connect the server to an existing farm
  • And in the database server name provide the name of the OLD database server and select the configuration database name from the dropdown.
  • Enter your passphrase and continue through the next steps of your wizard to reconnect the server to the farm
Note that you will have to repeat steps 2 - 4 for each server in your farm.

Here is what happens next: If there are any configurational issues with any of your SharePoint services which have uptil now been suppressed, they will start surfacing after you have moved the database server. E.g. Search Topology, User Profile Synchronization etc. So, unless you claim to be a perfectionist at maintaining your farm, you will have to do quite alot of cleanup activity after your actual task is over. So, make sure you have taken a sufficient amount of downtime before you start this activity.


  1. What if you are just moving the SharePoint databases but the database server is staying put. Is it possible to use the config wizard and point to the new alias?

  2. I cannot really understand where the databases are being moved?
    Is it that you are shifting the physical/virtual database server along with all the databases?

  3. How does this process change the database name to reflect the new database name? Also, what I'm looking to do is have SharePoint use an alias instead of the hostname (was done prior to me coming onboard).

    1. Hi Rick,

      The process of moving the database server should ideally not change the database names at all. i.e. only the location of the databases have been changed not their names (or any other property for that matter).

      If, however, your database names are changed then your task goes outside the scope of the database move activity and you need to manually attach the databases (whose names have been changed) to their respective applications or services.

      As for the second part are you talking about an alias for your database? That's what this entire process is meant to do. Please explain in more detail.

      Huzefa Mala.