MOSS 2007 Database Upgrade Considerations for Large Database with Single Site Collection vs Small Database with Large Number of Site Collections

Recently I was involved in medium MOSS farm upgrade to the SP1 and December Cumulative Update. As many of you might know, there are two approaches for the SharePoint upgrade. First one is the in place upgrade which will upgrade not only farm configuration databases but also upgrade content databases. Since this is a simplest approach, many times in place upgrade seems better approach. But, if web application content database size is more than 100 GB or if web application contains thousands of site collections, in place upgrade might have lot more chances for failure due to network connectivity or database connectivity issues. In those scenarios, it is better to upgrade SharePoint environment with database detach/attach approach. Not only it will minimize the upgrade failure possibilities but also improve the overall upgrade timeframe and duration.

During the initial discovery, we have found out that there are two content databases might be candidate for database upgrade. Main portal web application content database was obvious choice for database upgrade due to its sheer size. Portal web application had one large site collection and 250 GB database size. Other main web application in the MOSS farm was the My Sites. My Sites database size was 12 GB (Typical candidate for in-place upgrade) but it had 6000 My Sites – in other words, it had 6000 site collections. Initially we weren’t sure what approach would be better for the My Sites database upgrade.

During the planning process, we have decided to have only Portals database for the database migration candidate because of its large database size. At the last moment, due to business importance of the My Sites (easy to recover in the upgrade failure scenarios), we have decided to have My Sites database for the database migration. Overall, our plan was to have mixed approach. Portals and My Sites databases would be upgraded with database migration method and all other content databases along with configuration databases would use in-place upgrade approach.

So, the question is – Which database would upgrade faster? Large 250 GB portals database with one site collection or small 12 GB My Sites database with 6000 site collections? You will be surprised to know how long it took us to upgrade each databases using database detach/attach method.

Small 12 GB my sites database with 6000 site collections took 3 hours to upgrade the My Sites database and all the site collections. On the other hand, large 250 GB portals database with one single site collection took only 5 minutes to upgrade the database. Obviously I was surprised to see how fast portals database was upgraded compare to the My Site database. Because of maybe I am looser having nothing else to do or curiosity, I was looking for the reasons for why Portals database upgrade was faster than My Sites database. Looking further in the upgrade log, I have found out database attach/migration method takes only couple minutes to upgrade the database itself and to attach the content database to the web application. Apart from upgrading databases itself, it upgrades each site collection with features, web parts, and other updates, retain existing customizations, and clean up any obsolete SharePoint features. Upgrading one site collection normally takes 2-3 minutes depends on the complexity of the site collection. But, web application like My Sites with large number of site collections will multiply its upgrade duration.

In Short, lesions learned – It is important to remember that content database with large number of site collections will take lot more time to upgrade than large database with small number of site collections.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s