MS-Advantage's SPS to MOSS 2007 Upgrade Tips (Database Migration)

Over the last few months I have been doing quite a lot with upgrades. I got frustrated recently with Microsoft presentations stating that the database migration approach contained many manual steps (with no one from Microsoft actually willing to state what those steps were). Even the upgrade documentation says 'Repeat the restore and add database procedures for all content, search, and user profile databases'. They make it seem so simple don't they? I had previously found that I could upgrade the site content, but not the profile/audience information. No matter what I did, the upgrade resulted in me bashing my head up against a wall. Well in the week off between finishing up with SDM and starting a contract with DiData, I spent a lot of hours actually getting this to work. The information below has taken a lot of blood, sweat and tears. So rather than you 'claiming it as your own', I would appreciate it if you at least give me some credit. My company (MS-Advantage Pty Ltd) is always keen to provide additional consulting services if required :-)

My tips for upgrading:

1. Get your preconfig running early in the piece, so that you leave yourself time to cleanup orphaned objects or unused sites. The PREUPGRADESCANCONFIG.xml file should contain each definition of your custom templates. Document the unghosted pages. In most instances, you would want to Reset to Site Definition after the upgrade (otherwise you cannot take advantage of the features available in MOSS).
   
2. Run SharePoint Configuration Analyzer, making sure you package up all of the web part cab files. Create a batch file for later installation of these web parts. The Configuration Analyzer is also useful in identifying closed web parts on pages (which should typically be deleted).
   
3. Sort out your Account naming conventions and database naming conventions before the upgrade. For databases, I often prefix the farm name and suffix the host header. E.g. IntPrd_WSS_CONTENT_Intranet.
   
4. Decide on whether or not you are going to configure sites with Kerberos or not. Make sure you have knowledgeable infrastructure people who know how to use the SetSpn command and to troubleshoot kerberos related issues. I find that IE 7 in particular continually re-prompts for authentication if kerberos is not setup properly (whereas IE6 seems to revert to NTLM mode).
   
5. I prefer to do a database migration rather than an in-place upgrade or gradual upgrade. With in-place upgrade often it is difficult to clean up the old SPS installation properly (you need to stop all of the SPS services before attempting to uninstall). With the gradual upgrade, I find the documentation doesn't really cover best practices for migrating sites across (e.g. do you use stsadm -o backup, then stsadm -o restore; or do you use the export/import approach - I haven't tried the export on a SPS machine).
   
6. Make sure that you create all of your MOSS-based custom site definitions and that they are in place before you run the upgrade. I typically just copy the OOB ones and rename them based on which template most closely matches your custom template (90% of SPS implementations just change SPSTOPIC, SPSNEWS etc because of branding/integration of navigation web parts, rather than fundamental changes to list definitions etc). If you have previously used custom templates, copy across your own webtemp??.xml file with your custom site definitions in there.
   
7. In the 12\Config\Upgrade directory, you must create your own SPSUpgrade.xml file (I typically use SPSUpgrade.xml). This file maps tells the upgrade process what to do with references to custom templates that it finds in the content.
   
8. Also in the 12\Config\Upgrade directory, if you have custom templates, you need to update the SiteUpgraderConfigSPS.xml file. This file typically tells the upgrade process what layout page to use with each associated template. By default, the SPS template will use the defaultLayout.aspx page, which has a different zone layout to the previous version (3 zone columns). I often find that when migrating from the SPS 2 zone structure to the 3 zone structure, the resultant upgraded page can become wider that it was previously, thus causing people with 1024x768 resolutions to scroll. To combat this, I have previously changed the mapping for the SPS template to use the welcomeLayout2.aspx page instead of the defaultLayout.aspx, as this page has the same 2 zone structure that was used in SPS 2003. All of the other custom site definitions typically use the welcomeLayout2.aspx page also.
   
9. After creating the farm, configure base services (email, Office SharePoint Search, WSS Search). At this point you do not have any MOSS sites or SSP sites.
   
10. At this point, do a final prescan on the SPS box and do a backup of the databases (for the migration). To prevent users from possibly changing the current SPS environment, set it to read only (I typically do this via Central Administration Configure site quotas and locks from the Virtual Server List page Manage site collection quotas and locks, then select the 'Adding content prevented' radio button.
    
11. Switch back to the MOSS environment and create the MOSS web application. Specify a dummy database name. I usually give it a host header also. E.g. devintranet.
    
12. Restore the _SITE, _PROF and _SERV databases (I typically restore these with different names to match the new MOSS database naming convention. The _PROF becomes the _DB database and the _SERV becomes the _SEARCH_DB database). Set the database security on the _SITE database to match the security that has been applied to the dummy database you created (farm account should have db_owner access to the database). The server farm account should have db_owner access to the restored _DB and _SEARCH_DB databases.
    
13. To upgrade the content database, I recommend doing it from the command line (web interface may timeout if the database is large). First, detach the dummy database from the MOSS web application. Do this with the command "stsadm –o deletecontentdb –url http://devintranet –databasename IntDev_DUMMYDB_DevIntranet". Then attach (and upgrade) the restored _SITE database with the command "stsadm –o addcontentdb –url http://devintranet –databasename IntDev_WSS_CONTENT_DevIntranet"
    
14. THIS PART IS CRITICAL - Create the new mysite host with the command stsadm -o createweb -url http://devintranet/userprofile -sitetemplate SPSMSITEHOST -title "MySite Host" (I use the syntax 'userprofile' as this is used in the URL when displaying people's public my sites. Private my sites will remain as /personal/...)
    
15. Run the previously created batch file (from SCA) to install each of the web parts.
    
16. Create the SSP web application (just through 'Create or extend web application'). I typically set this up with port 11000 and give it a host header name. E.g. intdevssp1.
    
17. THIS PART IS CRITICAL - You need to perform a restore of the SSP site to capture and upgrade the profile/audience information. This must be done from the command line. The syntax is "stsadm -o restoressp -title SharedServices1 -url http://intdevssp1:11000 -ssplogin "domain\svc-spsspintdevssp1" -mysiteurl http://devintranet/userprofile -indexserver myindexservername -indexlocation "E:\Program Files\Microsoft Office Servers\12.0\Data\Office Server\Applications" -sspdatabaseserver mydbserver -sspdatabasename IntDev_IntDevSSP1_DB -ssppassword “myssppassword” -searchdatabaseserver mydbserver -searchdatabasename IntDev_IntDevSSP1_Search_DB -ssl no
    A warning message usually appears with the message “Could not find stored procedure 'dbo.proc_MSS_PropagationGetQueryServers'”. I have not worked this out as yet, but the upgrade appears to have worked successfully at this point.
    
18. Within Central Admin, apply a web application policy for a 'MOSS Admins' group (I typically do this because you can only specify an individual for site collection administrators).
    
19. By default, the account used to setup the farm will have access to the Shared Services Provider site when it is created. Although adding the MOSS Administrators group to the web application policy gives those users the right to access the site, certain activities such as modifying the user profile schema is still secured. To fix this problem, login to the SSP site using the administrator account that created the farm. Select the 'Personalization services permissions' hyperlink in the SSP site and add the MOSS Admins group. Give the MOSS Admins group full access (i.e. 'Create personal site', 'Use personal features', 'Manage user profiles', 'Manage audiences', 'Manage permissions' and 'Manage usage analytics' rights).
    
20. After an upgrade, you may notice a synchronisation issue being logged in the Windows Event Log. To fix this, from the command prompt, type:
    Stsadm –o sync –DeleteOldDatabases 0
    
21. There is a known bug with upgrades and that is that new users get an error when trying to create their my site. To fix this issue, navigate to Central Administration Application Management, and enable self service site creation. Add the application pool account associated with the my site host to the local administrators group. Perform an IIS reset.
    
22. TIP. I often find that previously in SPS/WSS, business units had to move their collaborative content into a team site (because of the more granular security). I have found that many customers often want to put these two previously segregated components back together (e.g. have one HR site that contains both published information and collaborative workspaces). To do this, you must use the stsadm -export and -import commands. I have found that if you use the minimal amount of arguments to these commands, documents for instance sometimes don't get exported (and are therefore subsequently missing following the import). The most reliable syntax I have found for the export is "stsadm -o export -url http://devintranet/sites/hr -filename hr.bak -includeusersecurity -versions 4". For the import, the most reliable syntax is "stsadm -o import -url "http://devintranet/Departments/Corporate Services/HR" -filename hr.bak -includeusersecurity -updateversions 2"
    
23. TIP. The upgrade process tries to fix and remap every bucket web instance that it finds (i.e. C1, C2 etc). Often however, if you have hardcoded links in content, these will not be detected by the upgrade. To address this, configure the search engine (i.e. do a full index crawl), then do searches for 'C1', 'C2' etc to find these hard-coded links.

I hope these tips/instructions save you the pain that I went through.