How to Perform an Unplanned Database Migration

In my last blog entry, I discussed using SQL aliases to configure a SharePoint farm for easy scalability. By using a SQL alias to point towards the SQL Server where the SharePoint config database resides, you create an easy and unobtrusive method if the need ever arises to move the config database to another SQL Server. As we learn time and time again, proper planning in the beginning always makes the future easier.

So what do you do if you didn’t create a SQL alias when initially configuring SharePoint and you need to move the config database?

Why Even Move the Config Database?

Some of you may wonder why you should move the config database at all. Their reasoning is simple: if you did indeed use proper planning when configuring SharePoint, there’d be no need to move the config database.

Reasons why you may need to move a config database:

  1. You inherited a SharePoint farm and want to change the topology.
  2. Your SQL Server virtual machine is dying a slow but inevitable death and weird things happen.
  3. You created a test/development environment on a single server instance and love it so much, you want to topology (ex. Fpweb.net duo plan) without having to change anything.
  4. You don’t believe anything I say and need to try this yourself.

Try what? Okay… let’s get to it. So — for whatever reason — you have a SharePoint that resembles an Fpweb.net Enterprise plan and need to move the SharePoint configuration database to another server.

Traditional method to move SharePoint configuration database to another server:

  1. Reset the farm password to something you will remember. Write it down! (Do this! If you don’t, you could be in big trouble!).
  2. Run the SharePoint Configuration Wizard and detach the server from the farm.
  3. In SQL Server Management Studio, detach the configuration and admin databases.
  4. Copy the databases to the new server.
  5. Attach the databases in SQL Server Management Studio.
  6. Run the SharePoint Configuration Wizard and attach the server to an existing farm. (Aren’t you glad you reset the farm password?).
  7. Shrug and pray…

Of course, after you do this, you’ll have to move the content databases from the old server to the new SQL Server if you want to see the content. But really for this blog post, we’re just focusing on moving the configuration database. (Moving content databases is really easy because you can use Central Administration’s UI to remove and attach content databases to a SharePoint farm).

Sometimes this operation is successful and there’s little work left from an admin perspective. But sometimes this operation does not work… and when it fails, it often fails miserably, resulting in long nights and much cursing and hair pulling.

What I’m hear to tell you, my friends, is there’s a better way. There is. I promise.

CLICONFG.EXE (SQL Client Configuration Utility)

This is what you want to use. *This will save you headache and heartache*. This tool allows you to move all your databases to a new SQL Server and not do ANYTHING in SharePoint. And from where I stand, the less administrative work that must be performed in SharePoint, the better everyone is for it.

So, how does this thing work?

Okay… although I’ve been rather firm with claiming this blog post dealt almost exclusively with the configuration database, this tool is so easy to use, I’m going to show you how to move ALL your SharePoint databases.

How to Move All Your SharePoint Databases:

  1. Detach all SharePoint databases.
  2. Copy the databases to the new server.
  3. Attach the databases in SQL Server Management Studio.
  4. Use CLICONFG.exe on the old server.

It’s worth noting again that there is no mention of SharePoint configuration in the above steps.

After all the databases are moved, you are now ready to run CLICONFG.EXE. Remember, this is run on the old server.

To use this:

  1. In a command window, navigate to c:\windows\system32
  2. Open cliconfg
  3. Click the Alias tab
  4. Click Add
  5. Choose TCP/IP
  6. In the Server alias box, type the name of the SQL Server that you entered when initially running the SharePoint Configuration wizard.
  7. In the Server name box, type the IP or hostname to the new SQL Server
  8. If you are not using the standard SQL Port, you’ll have to change that

Basically, you should have something that looks like this:

Add Network Library Configuration

Now, whenever this server looks for SharePointConfigDB, it will route to NewServer.

Upon creating the alias, you should now be able to open Central Administration and navigate to a SharePoint web application without issue. And you didn’t have to change anything in SharePoint.

Precautionary Tales of a SharePoint Database Migration 

This should really only be used as a last resort. Planning ahead and creating a SQL alias when first configuring the SharePoint farm is ideal. Sometimes that is impossible; and in those instances, this CLICONFG tool will help you.

But don’t rely on this tool too much! It is a leftover MS feature from earlier versions of SharePoint that remained in the product in order to support backwards compatibility. At some point, I would anticipate it being deprecated.

Make sure you have proper documentation. The only way I know to determine if an alias created with this tool exists is to open the tool and check out the configuration. If your SharePoint farm uses an alias like this, be sure to document it so other administrators (or your successor) will know what’s going on. Nothing is more frustrating than knowing a database exists, but not being sure where it’s at.

Aside from these few precautionary bullet points, this tool is an easy way to move all SQL databases for a SharePoint farm with no impact to the SharePoint configuration. And, like I said, little configuration is the best configuration in SharePoint.

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

About Matt Milsark

At Fpweb.net, Matt builds many of our clients' environments as well as supports and maintains them. These range from a simple dedicated single server to large multi-server SharePoint farms. Because of the sheer number of clients we have and the myriad of ways they use SharePoint, Matt has become intimately familiar with many aspects of SharePoint. Matt warns, "SharePoint is a beast. You may think you’re an expert at SharePoint, only to find out later you’re an expert at merely a small aspect of the beast's capabilities." Matt is also passionate about vinyl records. Check out his music site at lplives.com.
This entry was posted in SQL Server and tagged , , , , , , , , , , . Bookmark the permalink.

2 Responses to How to Perform an Unplanned Database Migration

  1. This is very informative and technical blog about how to do certain data base configuration with the SharePoint server.

  2. grobo says:

    i have to say, good post but the topic is a little misleading as in an unplanned situation where your sql server box just dies, this would not be applicable as you would have to run the client config util on a dead server box…did you know of a way that uses restored backups of config db on a new sql server box and then configure sharepoint to use them?

Leave a Reply

Your email address will not be published. Required fields are marked *

Let's make sure you're human first: *