Experiences with upgrading a 2 TB SharePoint MOSS farm to SP2

By:   |   Comments   |   Related: > SharePoint


Upgrading a MOSS 2007 farm to SP2 can be a simple task, but not if there is 2 TB of content with lots of customizations. Here is what we did...



We had a MOSS 2007 SP1 farm with 4 SharePoint servers and 2 SQL servers in a cluster. The portal was accessed by users across the globe and there were many services being used; Excel service, Search service, Reporting service, Forms service, etc.

We did not have a pre-production environment which was close to the production environment. Another challenge was that there were lots of customizations, both branding related and custom webparts and aspx pages and many of these were not documented.

1. Setting up test environment

We were running out of time because MOSS SP1 had reached end of support. Our plan was to setup a staging or pre-production environment which was similar in topology and content to the production farm. Our production farm had 2 WFE (load balanced) and 2 application servers (one running the Central Admin site and the other an index server) all running Windows Server 2003 R2 64 bit OS. There were mainly 2 custom web applications, one for the portal site and the other for MySite (apart from system web applications like Central Admin.

We used virtual servers to setup a test farm with the same patch level, 2 load balanced WFE (web front end), 1 application server and a database SQL Server. We installed MOSS 2007 SP1 on all the 3 SharePoint servers and installed SQL Server to the same patch level. We created web applications similar to production, configured index server, setup SSP and other services.

Now we had a farm with similar a configuration as our production environment. Next we took a SQL backup of all the production content databases and attached them to the SQL Server in the test farm. This created all the managed paths and site collections on the test farm. The SharePoint administrators verified and made sure that we had all site collections migrated to the test farm.

The next step was to deploy all solution package (.wsp) files to the test farm. This step went fine without any issues. We also had to recreate all Excel trusted locations and deploy InfoPath forms. We also configured reporting services on the application server and redeployed the reports using BIDS (Business Intelligence Development Studio).

2. Upgrading the test farm to SP2

Here is the order of installation of the SP2 patch we followed:

  • Install WSS 3.0 SP2 on the application server (Do not run PSConfig wizard).
  • Install MOSS 2007 SP2 on the application server (Run PSConfig Wizard, this causes your config db to be upgraded to SP2).
  • Install WSS 3.0 SP2 on WFE (Do not run PSConfig wizard).
  • Install MOSS 2007 SP2 on WFE (Run PSConfig Wizard, which registers this server to SP2 in the config database).
  • Repeat the same in next WFE.
psconfig warning

Once this is complete, SP2 binaries get installed on all SharePoint servers and the config db (farm) was upgraded to SP2. The next step was to upgrade our content to SP2. This is an important step because our content is huge. We had our content split across 90 databases and some databases were close to 200 GB.

We used the database attach method and not an in place upgrade because of the size of the data we had. We started off by attaching the content database holding the root site collection to SharePoint using the command "AddContentdb".

stsadm.exe -o addcontentdb -url "https://website.com" -databasename contentDbName			

We checked each site collection after attaching their respective content dbs to make sure things were fine. Adding the content db would recreate any manage paths if required and create site collections. Most of the customizations like Master Pages, css, etc were preserved. Since we deployed all required "wsp" packages, web parts were also available. We just needed to activate the features.

3. QA Testing on testing farm

We formed a dedicated QA team to test entire sites in the portal and make sure the user interface was fine and there were no visible errors. The QA team did not verify the integrity of data. We sent out a communication to all Site Owners inviting them to do a level of user testing on their respective sites in the test farm. There were some issues with certain Master Pages which were customized using SharePoint Designer (that had hard-coded image URLs). This was disregarded since images would display properly once relative path was used.

We also had issues with certain SSRS reports which had hard-coded connection details in the data source file. Reconfiguring these solved the issue. The QA testing and user testing took a week to complete. Once this step was completed the test farm was ready to be upgraded to SP2. We made it a point to document every step of testing we did and every step we took to fix any issues. This later made life much easier while upgrading our production farm.

4. Planning for Production upgrade

We had documented every step involved in upgrading the test environment to SP2, the time it took to upgrade content dbs, issues we faced during QA testing, what we did to fix the issues, and captured logs.

Next we sent out a communication to all site owners and stake holders to re-check their respective sites in the test environment after issues were fixed during QA testing phase. Once we got confirmation from all Site Owners we planned the SP2 upgrade in production over a weekend. We needed to make sure we had support from all other teams during the upgrade. We had the storage vendor, the backup application vendor, the hardware vendor, and Microsoft support all on call for this activity in case something went wrong.

We also had all the internal support teams ready; Windows administrators, storage administrators, network and infrastructure support, SQL DBA, and the L1 support teams. Since we had a huge amount of data and we needed the farm back online by Monday morning, we needed to make sure we had the right people for support if there is an issue.

We made sure we had a proper rollbank plan in case we had an issue and we had to revert back to the SP1 state. First we wanted a complete backup of the data and configurations. We had a third-party application that took care of database SQL backups and file system backups. We planned for a full backup of all databases and the file system before the planned start date. The file system backup would include the 12 hive, IIS metabase, assembly, and the Inetpub folder. We also had all the solution packages, SSRS reports, InfoPath forms with us, and of course, the experience of migrating the test farm to SP2. We also made a document with details from the Central Administration website such as AAM settings, Excel Service trusted paths, web application lists with IP and port details, blocked file details, etc . More details on what to backup is mentioned in the second link in the references section.

5. Communication Plan

The corporate team drafted a communication plan for the Site owners, stake holders, and for other end users. This plan was sent out 3 weeks prior to the production upgrade date. It mentioned the need to go for an upgrade, the amount of downtime, the performance benefits after upgrade etc. It also requested of the users to plan their projects accordingly so that there was no last minute confusion. We mentioned a downtime of 48 hours and kept our fingers crossed.

We could have made our test farm read-only and redirected hits to our portal to that farm, but we did not go for that because those were all virtual servers and we had not done many security tests. Moreover there were things like adding new SSL certificates, publishing those servers on internet etc. So we went against that and took a complete down time of 48 hours, a Saturday and Sunday.

6. Production Upgrade

a. On the day of the scheduled production upgrade, we stopped the IIS on all WFEs (web front ends).

iisreset /stop

b. Stopped and disabled WSS Timer service, WSS Administration Service, Office SharePoint Search, and SSRS.

c. Ran the following stsadm commands in order on all content dbs including SSP content db and MySite content db:

stsadm -o preparetomove -contentdb "dbserver:contentdbName"
stsadm.exe -o deletecontentdb -url "https://mycompany.com" -databasename contentdbName

We scripted this process so that the "preparetomove" command was run on all content dbs and then we removed content db mapping from the configuration database by running the "deletecontent" command.

d. Installed WSS 3.0 SP2 and MOSS 2007 SP2 on Application servers and WFEs. We followed the same procedure similar to what we did in test farm.

e. Once binaries were installed on the application servers and WFEs, we ran the PSConfig wizard on the application server first and then on the WFEs. This upgraded our config database and central admin content database to SP2.
f. Next we attached the content database of the root web site collection followed by other content databases.

7. Statistics

In brief, we had 4 SharePoint servers and 2 TB of content spread across 90 databases.

The total activity took 26 hours. The SP2 binaries installation took 2 hours and attaching content databases took approximately 20 hours. Here are some details:

Task Duration
Installation of SP2 on all SharePoint servers 1 hr 10 mins
Running PsConfig Wizard 2 hrs
attaching content databases 20 hrs

8. Issues faced

1. The PsConfig wizard failed on the application server. We checked the logs and found that SSP database was set as read-only.

psconfig error

We had made it read-only before running SQL backup. After we changed the database to write mode and the issue was solved.

2. The PsConfig on the WFE was stuck on step 7. We checked the upgrade log in the "12\logs" folder and found the entry "syncupgradetimerjob: sleeping for 10 seconds" was repeating on the logs. We solved this issue by killing the "PSConfigUI" process, restarting the Timer service and the SPAdmin service, and then re-run the PSConfig Wizard.

3. We were also hit by the MOSS SP2 bug which converts the license to a trial version. We installed the hotfix suggested by Microsoft but that did not help. We had to re-enter the product key to convert the license.

We did not face many issues related to search, SSRS, or Excel services. There were some branding related issues but the development team followed the same procedure as what they did for the test farm because everything was already documented.


  1. Deploy software updates for Office SharePoint Server 2007
  2. Choose what to protect (Office SharePoint Server)
  3. Database: Stsadm operations (Office SharePoint Server)
  4. MOSS 2007 SP2 upgrade process http://blogs.msdn.com/b/gregmcb/archive/2009/06/02/moss-sp2-upgrade-process-step-by-step.aspx
Next Steps

Check out some of these other tips on MSSharePointTips.com:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manoj V Manoj V

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms