Moving Your MOSS Databases to a new SQL Server

If you manage a single-server SharePoint 2007 farm, which is a very common configuration, there's a good chance that at some point you will want to move to a separate SQL Server. Maybe for performance, maybe to improve your infrstructure, maybe to help meet corporate database guidelines. There are lots of reasons you might want to do this. So what is involved? Is it a big deal?

The short answer is that it takes a SharePoint admin to do this (instead of or in addition to a SQL admin) and I generally allow about a day to do it if it is a machine I know well. If it is client machine rather than one of mine, and there are unknowns, I allow an additional day to troubleshoot the things that will inevitably break after the move.

So you shouldn't fear this move, but don't take it for granted either. It isn't usually trivial and sometimes can be a little painful. The obvious question in your mind will be: "what in SharePoint is directly dependent on the database server, isn't there a single setting somewhere that tells SharePoint which database server to use?" Unfortunately, it isn't quite that simple. You can't just go into Central Administration or someplace in the registry and tell SharePoint to use a different database server. You will effectively need to back up your farm, disband it, recreate the farm and restore the original content to the new farm. Then you will need to go through and check for various little things that might have directly specified database connections and so will need to be modified. Of course if you know about or can find that sort of thing ahead of time, that would be preferable.

1. The first step is preparation.

---> a. Make sure things are currently working. You don't want to find something broken after the move and be unsure of whether it was working before the move. That makes troubleshooting a lot tougher. If practical, fix broken things before the move, or at least make a note of them. Take snapshots of the most critical pages, so you can look back fondly on them as a pleasant memory if they never come back. Well, also to help troubleshoot them if they don't look quite right at the end. Yes, I've actually had to use this.

---> b. Gather the required information: the file location of the SQL database files on the old and new servers, the name and password for the SQL admin accounts, the account names and passwords of the SharePoint service and administration accounts, the old and new database names. I use a template I call a "configuration sheet" which captures all the server, web application, site collection, database, and IIS information I would need to recreate the farm topology. I recommend using something like that to be sure you have all of the informatiom you need before diving in, to avoid any panicky last second calls.

---> c. Go through Central Admin in SharePoint and make a note of the critical settings that you care about (because you will probably lose these in the move): SMTP server address for incoming and outgoing mail, alternate access mappings, accounts and permissions, yes just about everything! The fact that you have to do this tedious step is the biggest reason why the move is not as simple as you might have assumed. There are some tools that can help with this, but they cost. You wouldn't need my instructions here if had that kind of scratch. You should also get an SPSREPORT (this is a free tool used by Microsoft to capture configuration information and logs).

2. The second step is to get a good multi-technology backup (you should already be doing this routinely):

---> a. Backup your IIS metabase - not strictly neccessary but definitely recommended just in case. Sometimes you need to recreate web sites, and this will save your butt if there were IIS settings you didn't know about.

---> b. Get SQL backups of your content dbs -- hopefully you won't need them, but a good thing to have. Don't bother backing up the configuration db or admin db, you can't do much with them anyway.

---> c. Backup your web server extensions and inetpub (IIS) folders on the web server. This is not an optional step, this is essential. Most of SharePoint's content is in the database, but some things you need to properly render and use pages are in the front end web server files.

---> d. Perform site collection backups from STSADM for the site collections that are most critical to you. This will give you a way to quickly and easily recover them elsewhere to keep your business going in case of a complete FUBAR.

---> e. Perform "catastropic" backup from STSADM if you are familiar with this. If not, don't worry about it. It's just another way to recover in case of an unexpected problem.

---> f. Lastly, and most importantly, perform a full backup of the farm from the Central Administration GUI interface. This (plus the web server file backup) is going to be your primary tool for SharePoint recovery in most cases). This is a kind of backup that can't be scheduled, it must be performed interactively by an administrator, and it isn't very robust for restoration, but it has some unique advantages for convenient recovery. You should use the Central Admin backup just before any configuration change to your farm.

3. Take a deep breath. You're about to dive in. Run the technologies wizard on the SharePoint server and use it to disconnect the web server from the farm.

4. Use the technologies wizard to create a new farm on the NEW SQL server (this is the key step that recreates the configuration database, which cannot be moved due to dynamic links with other things, thus the root cause of the complications). This is where all that preparation starts to pay off, you should know the right accounts to use for everything. Using the right accounts in the right places in MOSS is critical. If you don't know the rules for this, you should not be recreating the farm, go back and do the homework. No kidding. I promise that you (or someone that curses your name) will be sorry later if you take the account assignments for granted or just use the same admin account for everything.

5. Configure the Office SharePoint Search Service from Central Administration. This may allow you to restore search indexes later if neccessary. It is optional, since you could always recreate the indexes if neccessary.

6. Using SharePoint Central Admin, perform farm restore of the backup you took in step 2f. Did it work? Phew, you're mostly done. I feel your relief. [No? Well, that's why we grabbed all that other stuff before! You have everything you need for a complete disaster recovery. Good luck. Or just reconnect your original databases and try again when you figure out what went wrong.]

7. Tedious but easy: go through Central Admin and replace all of those settings you captured before. Oh, you didn't believe me that you would need them? Too busy to do all that work? Just hope there wasn't anything important in there that you can't figure out later.

8. Take the original databases offline and go through the portal and fix anything that is broken because it needs to be reconfigured or has connection back to the old databases. This is the part that may sometimes take the additional day on a farm with unknowns. Don't forget to go through and change any database backup and maintenance procedures to use the new server if neccessary.

If you can't use the SharePoint Farm Backup and Restore from Central Administration for some reason, the process gets more complicated but it can still be done.

For example, you can sometimes use the "catastrophic" restore to restore the GUI backup using STSADM. You have to get the ID of the appropriate backup and figure out all of the parameters for accounts and locations and so on, but it may work even when the Central Admin restore does not (mainly because it doesn't rely on the timer service).

If you end up having to restore from a site collection backup for some reason, notice that your sites may not work properly. That's because there are some components in the file system for IIS and SharePoint that may have been modified. That's why you took those backups of INETPUB and WEB SERVER EXTENSIONS before. First try replacing the WEBCONFIG and GLOBAL files in your web site virtual folder with the original ones. That often does the trick. If not, you may need to do some more work to track down what is different. That's another reason I plan for a second day for these database migrations.

Best of Luck to you!