Saturday, May 12, 2007

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.


Shawna McHargue said...

First off let me say thanks for the most useful and comprehensive Blog on MOSS migration currently on the web. I have a few questions however. After 2 weeks of failed attempts to migrate our SPS 2003 instance, we think we've settled on Database Migration. Using trial and error and parts of your guide we keep stalling at the ssprestore function. Can you tell me what exactly the -url you're using is? In all your examples you use one server name but in this instance you're using another. Is this the old SPS site? A new site on the MOSS server that you didn't talk about creating? A miss print? Currently I'm getting an Action 12.0.4... Microsoft failed error and my SSP shows Provisioning failed with the same error number. Thanks in advance for any light you can shed.

Alan Coulter said...

Hi Shawna. In point 16 I mentioned about creating the SSP web application. I typically use a consistent portal number of 11000, but you can use what you want. In addition to this, I attach a host header to this SSP site via IIS Manager. My naming convention is farmnamesspindex. So the first SSP site in the intdev farm is intdevssp1, the second intdevssp2 and so on. The devintranet url is just the host header I have attached to the restored portal site on port 80. Hope this helps. Type the restore ssp by hand. Don't copy/paste from a document as sometimes you will get a syntax error (weird Windows clipboard behaviour). Good luck.

Krish said...

I have migrated wss 2.0 site to MOSS 2007 using database migration. When I opend the migrated site, it doesn't shown the out of box web parts that are available in MOSS 2007 like BDC webparts, Excel web web parts.
I have cross checked this with the site that is created with team site template in MOSS.
Do I need to do any post migration tasks to view out of box webparts in migrated sites?


Unknown said...

I am having the same problem with a site migrated from WSS 3.0 to MOSS 2007. None of the MOSS webparts are available only the WSS webparts are there.

Unknown said...

I just figured out how to add the missing web parts from a migrated site. Go into operations in central admin -> Upgrade and Migration then click enable features on existing sites. That should fix all migrated sites. Hope this helps.

Shawna McHargue said...

Thanks again. If you check over on my blog you will see I've solved the problem ( I was getting before. However I'm again stalling, I noticed now, don't know how I missed it before, that you're using a host header I'll try that tomarrow. Wednesday I was finally able to see a successful MySite migration however they only way I was successful was to mount the _prof db on a different port, which would have been fine but somehow the private pages where both at the root site and the other port and the, public page was at the other port, while all the subsites where at the root. Needless to say it was too confusing to keep. When I started over I kept getting a site already resides at this address, hopefully the host header will fix that.

Thanks again!

jpac said...

Is his process for migration to the same machine or a different machine entirely???

Sandeep Rao said...

I am getting folllowing error when i run the restoressp command as mentioned in step no 17 the bolg

The specified credentials are invalid.
Parameter name: account

Can someone please help mein getting this problem solved.

Vielka said...


In step 17 of the blog, which one is the search database that I have to specify in the command line, in the -searchdatabasename?

a) The one restored from the SPS 2003 installation?
When I use this, I receive an error stating that "the specified database is not a valid search database"

b) The one created when I activated the WSS Search Service, in MOSS 2007.

Unknown said...

give me any idea that after my site upgradation is it necessary to upgrade service database


Unknown said...

Thanks! This has been a life-saver for me.

The only problem I had after this was that I had to reconfigure my AD connector - MOSS kept telling me that the account I specified couldnt access the AD server, but when I ignore the error it sync'd just fine anyway - and I had to remove and add all the permissions for sites - kept telling me that the accounts didnt have access, but removing and re-adding the users worked for me.

Unknown said...

Web parts are being commented out after migration to MOSS.
I haven't seen this before...not able to find why this is happening?

Any CLues?

Srividya said...

our company currently use sharepoint 2003 portal server, now we are trying to migrate from 2003 to moss 2007, we have some 8 GB of data, i tried gradual upgrade first for migrating, but i got request time out error, so i tried content database migration as my second method, i followed the below steps to migrate

my 2003 machine configuration
1.sql server 200
2.sharepoint portal server 2003

my 2007 machine configuration
1. sql server 2005
2. MOss 2007 the prescan tool by in sharepoint 2003 machine and took the backup of site,serv and prof database from sqlserver 2003
2.created a web application , gave the web application name , load balance url(local ip of Moss 2007 machine) , and content database name as 2003 site database name
3. run the stsadm addcontent db command to attach the database to the site created above.
4. got the operation successful after the attaching process,
5. when i tried to browser the site, everything seems to be fine in the local Moss machine.
6. But when i tried to browse the site using the public ip, the site home page is displaying properly but when i try to access any document library, which has folder in it, the address is mapping to local machine name of moss.

can you please suggest me what has to be done for this

thanks in advance

Grossmeister said...

Hi Alan.
I tried to migrate user profile database
stsadm -o restoressp -title portal_SSP1 -url http://test:11000 -ssplogin domain_name\ssp_account -mysiteurl http://test:1024/personal -indexserver domain_name\test -indexlocation "C:\Program Files\Microsoft Office Servers\12.0\Data\Office Server\Applications" -sspdatabaseserver test\sharepoint -sspdatabasename portal_PROF -ssppassword ssp_acc_pass -searchdatabaseserver test\sharepoint -searchdatabasename portal_SERV -ssl no
but received 2 errors:
A site already exists at URL http://test:1024/personal
The specified database is not a valid search database

Then I tried to migrate another portal, I tried to change "/personal" to "/my":
stsadm -o restoressp -title portal_SSP1 -url http://test:11000 -ssplogin domain_name\ssp_account -mysiteurl http://test:1024/my -indexserver domain_name\test -indexlocation "C:\Program Files\Microsoft Office Servers\12.0\Data\Office Server\Applications" -sspdatabaseserver test\sharepoint -sspdatabasename portal_PROF -ssppassword ssp_acc_pass -searchdatabaseserver test\sharepoint -searchdatabasename portal_SERV -ssl no
and at this time I received no errors, just
The server is not joined to a farm.

Can you say something about it?

Unknown said...

Do not attach the component settings (_SERV) database during a database migration
When you perform a database migration, you do not need to migrate and attach the SharePoint Portal Server 2003 component settings database (the search database, usually named "ID_SERV" where ID is an ID such as the server name). Rather, you must recreate the search database and reconfigure your search settings when you perform a database migration. This is because Search settings from SharePoint Portal Server 2003 were stored both in the registry on the server and in the database, and a database migration does not contain all of the settings.

If you attach the component settings (search) database during database migration, the upgrade process will fail when upgrading the shared services and you maysee the following message: Could not find stored procedure 'dbo.proc_MSS_PropagationGetQueryServers'.

Perform the database migration again, and do not attach the component settings (_SERV) database.

Please look at the following technet article.

Unknown said...

Thanks for the great instructions for the upgrade. I have upgraded (using database migration) with only a few minor issues that I hope you can help with. Currently, the mysites and search are on the default SSP and associated with our main portal web app ( For the default SSP, the SSP admin page is unreachable and search isn't working. After some researching, I decided to create a new SSP in hopes that that would fix both issues. The new SSP appears to work, but I now need to move the mysites (without losing anyones' sites) and search to the new SSP which is working (search, AD, etc.). The real problem is that I want to have mysites available via the URL that they have now ( & Do I need to move to the new SSP, fix the current SSP (or recreate it) and then move them back? Any suggestions or guidance would be great.


Claudio Bertoncello said...

Hi Alan,

I have migrated SPS2003 farm to MOSS2007 farm by Content DB upgrade with only a problem.... SSO database! If I backup the SSO DB from the SPS installation (SQL Server 2005) by SSMS and restore it in to the MOSS installation, I don't be able to read credentials. It seems a cryptographic key problem, but I had also backed up and restored the cryptographic key. Have you a suggestion for this issue?

Thanks in advance


MOSSDeveloper said...


I have a different question all together.

Here are the steps we have taken to upgrade and revert

1) We recently upgraded a WSS2.0 site to Wss3.0 and MOSS2007

2) Discarded the results of the gradual upgrade for 2 sites and reverted to the previous version site using the steps below

( this process deletes entries of the sites from the configuration database)

Now we want to Reupgrade the 2 sites back to WSS 3.0 and MOSS 2007 .

Questions we need answers to

1) We would like to know if the Content Databases will be reacreated with the Reupgrade or if we can still attach the existing 2007 content databases to the sites when we reupgrade.

2) Do we need to delete the 2007 content databases ? If so how do we recreate them


Jeroen Ritmeijer said...

Very useful entry.

For one of my customers I have done an upgrade in 'gradual Upgrade' mode.

One of the issues we ran into was that we were not able to restore WSS3 site collections if they were upgraded from WSS2.

The solution is to manually delete the old WSS2 site collection after upgrading.

For a full breakdown see:!8A48A27460FB898A!974.entry