I’ve done a lot of SharePoint Services 3 to Foundation 2010 migrations now and one thing I’ve learnt is that they all have to be treated individually on their merits. A recent one was a move from WSS 3 on SBS 2003 using SQL 2005 Embedded Edition to a SharePoint Foundation as part of SBS 2011 Standard Edition using SQL 2008R2 Express Edition.
The WSS 3 had a single site collection in a single content database. I was a little concerned that this database was 7.2GB and I’d have a 10GB limit on SQL Express that is on the Foundation Server. However I thought I had plenty of spare space for this to work.
I did the usual steps to complete the Farm configuration on the new SBS box and applied WSS Service Pack 2 to the old SBS 2003 box. The Pre Upgrade Check in STSADM gives you a special warning to let you know that you have a content database that is over 4GB and you might have to do something when you migrate. Your options are along the lines of:
- Deploy SQL Workgroup or above and use that for the content database
- Remove content from the database before upgrading
- Use a time machine to return to point of installation and change the design to use multiple site collections in separate databases
- Hope that it will fit following the upgrade
Option 4 may be an attractive one but what if we use a SQL Standard in a test machine to trial the upgrade before we attempt to attach to the live SBS 2011 Server.
I restored the database to a test machine running SharePoint Foundation 2010 using a SQL Standard and used STSADM –o addcontentdatabase. I didn’t pre-allocate any space to the database or the log so that I could see the files grow. The database was set to Simple Logging
The STSADM starts counting its percentages and then stopped at 19.69% going no further. A check of the upgrade.log file shows that it ends with:
[STSADM] [UpgradeAllDocStreamsAndAllDocVersions (126.96.36.199)] [DEBUG] [5/14/2012 10:13:33 PM]: Populating AllDocStreams Content Column [STSADM] [SPContentDatabaseSequence] [DEBUG] [5/14/2012 10:13:33 PM]: Executing SQL DDL Script.
SQL is still busy, so I use SQL Activity Monitor to see what it’s doing. I can see one process of interest on my Content Database that is executing the following:
INSERT INTO AllDocStreams ( Id, SiteId, InternalVersion, Content, Size, RbsId) SELECT ADV.Id, ADV.SiteId, ADV.InternalVersion, ADV.Content, ADV.Size, ADV.RbsId FROM AllDocVersions as ADV WITH (TABLOCK)
Meanwhile the Log file and the database file both start growing and keep growing until the log file is 5.3GB and the database is 11.7GB. This happens as the result of a single SQL transaction so, even on simple logging, the transaction log will grow excessively. You can of course shrink the log afterwards. If you try this on the live site with a 10GB limit the upgrade will fail on allocating space when the database file gets to 10GB.
In this case the customer archived old data out of the content database and we disabled the Recycle Bin to reclaim space. The database could then be shrunk to 5.5 GB and upgraded to 9GB. We now just need to deal with the limited capacity for growth in the content database, could really use that time machine.