Administration: Migrating to a new SQL Server in MOSS (SQL 2005 to 2008)

1. Overview

In this post I will describe the steps I took to successfully migrate from a SQL 2005 server to a brand new SQL 2008 server within a medium size production MOSS farm serving around 2000 users. The process makes use of the Microsoft supported method of migrating to a new DB server: SQL connection aliases.

As always, I strongly recommend you follow the steps below in a test environment prior to carrying them out in a live farm.

2. Scenario

Prior to the migration, we had a medium sized MOSS farm consisting of the following physical servers:

• 1 x86 SQL 2005 server running Windows Server 2003 SP2


• 1 MOSS application server hosting the MOSS Central Administration Web site and the query / index roles.


• 1 MOSS WFE server also hosting a MOSS Central Administration Web site for redundancy.
  

We host around 50 Web applications, each with their own content database.

The SQL 2005 server was very short on disk space (less than 20%) and, having considered a number of options including upgrading the disk capacity we decided that the simplest and safest (read "least risky") approach would be to purchase a new high capacity server and repurpose the existing SQL server (e.g. use it as an additional WFE server for redundnancy and performance).

We also decided that rather than install x86 Server 2003 and SQL Server 2005 on the new server, we would "future proof" the SQL back end by upgrading to x64 Server 2008 and SQL Server 2008.

As we would be repurposing the existing SQL server, we decided to go with the "Move all databases" approach documtned by Microsoft in this article: http://technet.microsoft.com/en-us/library/cc512725.aspx#Section1.

3. Gotchas

There are a few "gotchas" that I recommend you consider when planning to move to a new SQL server:

• There is no supported method of changing the name of the server that the MOSS Admin Content DB resides on. Instead, the documented MS resolution is to use SQL aliases to point MOSS WFE servers to the new SQL server (which in my experience seems to be very effective, but feels like a bit of a hack).
• The process for backing up and restoring MOSS SSPs is different to that used to move content databases.
• The entire farm must be stopped prior to moving any databases to prevent any synchronsiation issues. In my case downtime was around 3 hours.
• The SSP backup may fail if you have renamed any of your Web applications (see below).

With regard to the last issue, the error message I received when attempting to backup the SSP as a part of the migration was as follows: "Object Shared Search Index failed in event OnPrepareBackup." This is documented here: http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/9087ae96-c730-4b9f-85a4-67b4a4d67e65

The steps I took to resolve the SSP backup issue are contained in the appendix of this post, and are only relevenat if the SSP backup fails for you with the same error message.

4.1 High level steps

1. Backup all databases (including MOSS content and configuration databases)
2. Backup IIS on all WFE servers
3. Ensure integrity & dates of NTBackup file system backups on MOSS WFE servers (e.g. 12 hive, IIS directories)
4. Follow DB migration process outlined below
5. Stop SQL services on original DB server to ensure migration success
6. Test all portals
7. Add Backup & maintenance routines to SQL server
8. Modify any network or off-site backup routines your organisation may have to include the new server.

4.2 Detailed DB migration steps

Note that the steps below are basically a concise version of those contained at
http://technet.microsoft.com/en-us/library/cc512725.aspx with my own notes added in blue that I took whilst following them:

1. Follow http://technet.microsoft.com/en-us/library/cc263187.aspx to prepare the new DB server.
2. Record which Web applications are associated with the SSP
3. Back up an SSP by performing the following steps:
   
   On the drive on which SharePoint Products and Technologies is installed, change to the following directory:
   
   %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.
   
   If you do not already know which node you want to back up, type the following command:
   
   stsadm -o backup -showtree
   
   In our case the SSP name was "SharedServices". Note that the UNC directory must be a shared drive accessible to both the WFE and DB servers as opposed to a local path on the WFE server.
   
4. Back up each SSP by typing the following command:
   
   stsadm -o backup -directory -backupmethod full -item
   
   Ensure the shared drive ("UNC Path") is available to the SQL server - i.e. in the format (\\server name\folder name) as opposed to a directory local to the server. This should complete with 0 warnings and 0 errors. The account used to perform this action must have access to backup databases on the database server (e.g. domain Administrator account).
   
5. After backing up, remove each SSP by following these steps:
   
   On the disk on which Microsoft SharePoint Products and Technologies is installed, change to the following directory:
   
   %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.
   
   To remove an SSP, use the following command:
   
   stsadm -o deletessp -title -deletedatabases -force
   
   I believe that removing the content DB in central administration has the same effect.
   If using STSADM, check the content database name for the SSP Web application in central administration. In our case the SSP contentdb name was "WSS_Content_".
   
6. Delete each SSP content databases in SQL Management Studio.
7. Stop the farm by stopping all SharePoint services on all WFE servers (documented in MS article referenced above). Then, on each server that is running the Central Administration Web site, at the command prompt, type iisreset /stop.
8. Back up all databases on the existing (old) database server. I performed a full backup of all "User" databases, then copied them to a separate server within the farm for safety.
9. In Windows Explorer, locate the database backup (.bak) files that you want to move, and then copy or move them to the destination server.
   Important: Move only the backup files to the destination database server. Do not move any databases or other files at this time.
10. Restore all databases on the destination database server.
11. use http://support.microsoft.com/kb/918992/ to copy all SQL Server logins using a couple of stored procedures.
12. Refer the farm to the new database server by creating a SQL Server connection alias.
    Add aliases for both the FQDN and hostname.e.g. add hostname and hostname.domain.com. Perform this step on all MOSS servers in the farm.
13. Start the farm by starting all SharePoint services on all WFE servers (documented in MS article referenced above). Then, on each server that is running the Central Administration Web site, at the command prompt, type iisreset /start.
14. Obtain the GUID of each successful SSP backup:
    
    stsadm -o backuphistory -directory
    
15. Restore each SSP to the new location:
    
    stsadm -o restore -directory -restoremethod new -backupid -newdatabaseserver
    
    Again, Ensure the shared drive ("UNC Path") is available to the new SQL server - i.e. in the format (\\server name\folder name) as opposed to a directory local to the server. This process took around 10 minutes to complete. This should complete with 0 warnings and 0 errors. Note that this process can be rolled back (by deleting the restored SSP) using:
    
    stsadm -o deletessp -title -deletedatabases -force
    
16. Remove backup SSP directories.

5. Summary

In this post, we took a look at the process for migrating all databases from a SQL 2005 x86 server to a SQL 2008 x64 server. In future posts, I may go into more detail around the specification of the new server including decisions about the hardware choices and RAID configuration. Please post a comment if this post helped you!

6. Appendix: How to resolve SSP backup error "Object Shared Search Index failed in event OnPrepareBackup."

This is only appicable if you receive the error above when trying to backup a SSP. Note that this takes a LONG time if the SSP backup fails for multiple Web applications - you may decide to simply recreate the SSP rather than carry out this process but I chose this option as it seems to me like the least risky choice. In my case, this process took 10 hours for 23 Web applications but this time will vary depending on your experience with MOSS and number of Web parts you have.

1. Document the following MOSS Central Administration settings for each Web application that needs to be recreated to allow an SSP backup to succeed: "Policy for web application", "authentication providers", "Web application general settings" (timezone) settings, which Web parts that are deployed to which Web application, any people picker settings that need to be restored using STSADM.
2. Take the farm offline - in my case I simply created a rule to block all connections using ISA server 2006.
3. Backup IIS for all WFE servers.
4. Backup all MOSS DBs including content and configuration databases (you never know...).
5. Delete the Web application in SharePoint that failed to backup - do NOT delete the IIS Web site or content DB.
6. Ensure you have a full backup of the IIS Web site content - I used NTBackup. In particular, ensure you have a backup of the Web.config file as SharePoint overwrites this when you recreate the Web app.
7. Delete IIS Web site
8. Create new Web application with new IIS site & note down default (temporary ) content DB name (to be deleted)
9. IISreset /noforce
10. Remove temporary content db in MOSS Central Administration
11. Delete temporary content DB in SQL Server Management Studio.
12. Reattach correct content DB and confirm no. sites > 0- restore web.config from NTBackup-
13. Repeat the above for all sites that are reported when attempting to backup the SSP.
14. Backup IIS on all WFE servers again in case a manual virtual directory restore is required
15. Restore IIS backup on all WFE servers
16. Reconfigure the "Policy for web application", "authentication providers" and "Web application general settings" (timezone) settings for each restored Web application in MOSS central administration.
17. Redeploy web parts to all restored web applications
18. People picker settings for all restored web applications using STSADM.

Subscribe to the RSS feed

Follow me on Twitter

Follow my Networked Blog on Facebook

Add this blog to your Technorati Favourites