Moving MOSS 2007 SQL Databases part 3

If you haven’t seen the earlier posts on this topic: Part 1 and Part 2

D-Day has arrived, it is time to actually move my SharePoint 2007 sql databases to a different SQL Server.  My previous posts touched on the research I did leading up to this event.  This post will cover the actual tasks and outcome.  I am writing this as I go through the process, so let’s all cross our fingers that it goes smooth.

My Plans

A summary of my plan is as follows:

1. Backup the SharePoint farm
2. Backup all SQL db’s
3. Move those backups off to a “safe” spot on the network.
4. Start the process defined in the TechNet article Move all databases (Office SharePoint Server 2007)
5. Backup the SSP’s
6. Remove SSP’s
7. Stop the Farm (services)
8. Backup sql db’s
9. Copy backups to new sql server
10. Restore sql db’s on new sql server
11. Apply roles, permissions, etc. to new sql server & restored db’s
12. Restart Farm
13. Restore SSP’s from backup
14. Cross-fingers and check to see that our SharePoint intranet is up and running
15. Go have a beer

Round 1

I attempted these steps and all went smoothly until I got to step 13 – Restore SSP’s from backup.  The restore made it to 50 percent complete then ran into this error:

SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

After several attempts and a grueling session of troubleshooting I found that the SQL Server Agent was not running on my ‘new’ SQL server (SQL Server 2008 R2 64-bit, running on Windows Server 2008 64-bit).  The fun part was that I couldn’t get it to start.  The Agent would just stop after every attempt to start it.  I tried several solutions found on-line, none of which worked.

At this point my weekend was over and we needed the production server back online, so I did the restore BACK to the original SQL Server.  SharePoint was up and running fine very quickly.

Finally, I un-installed SQL Server 2008 R2 from the new server and started over.  After carefully reinstalling SQL Server 2008 R2 I no longer had any problem with the SQL Server Agent.  Hurray, now will the SSP restore work?  I have to wait until the next weekend to find out.

Round 2

Friday – A week has gone by and I am now preparing to walk through all of those steps again.  More confident than last time perhaps, but concerned at what I might run into this time around.  I will do all of the initial steps – the backups – tonight, Friday night.  Saturday morning I will start the scary part, removing the SSP’s and moving everything to the new SQL Server.

Saturday – Everything went well up to the restore, again. This time the error was an Access Denied error pointing to the SharePoint backup directory.  Short-story resolution: change the SQL Server account to use a Domain account, make sure that domain account had permissions to the backup folder and to the sql database files folder.  I also wanted to make sure I didn’t have any more trouble getting to the backup folder so I opened it up (temporarily) to several other accounts – my ssp and app-pool users.

The SSP Restore completed successfully, with zero errors and zero warnings.  Hurray!

Hold-on, the process outlined by Microsoft ends here.  What about my content databases?

Searching about I found that I need to use stsadm to detach the content databases, one by one, from the SharePoint server and add the databases on the new server.  So I begin this task and have no problems until I get to the Config database.  It detaches fine, but then the services become disabled and the attach (setconfigdb) results in “Value cannot be null.  Parameter name: str” !  What the?!  Time for Microsoft Support calls.

Support helped resolve the ‘migration’ issues, however now the SSP is not working-including search, and there are several other things missing.  I am told I will have to add any solutions back in, and find whatever features or web parts that might not work and fix them.  Also have to open a new ticket for ‘advanced troubleshooting’ on the SSP.  It has been a long Saturday.

Round 3

Sunday – Monday (ouch)

After hours on the phone and several rounds of running psconfig we were able to get a new Config db setup and restore over it to capture most of my configuration data.  The SSP is a different story.  We had to create a new SSP, which left me with a few more days of reconfiguring the SSP, Search, Audiences, etc. before I was “back” to normal.

Wrap-up

Some good came from this pain… since I had to reconfigure the SSP I could apply some lessons learned since the original install.  Now I feel like my SSP is “better” than it was before. 😉   I improved our Search features, including a federated search we were using, I cleaned up some Audiences that were not used, etc.   I just had to find some improvements!

Advertisement