Intro

Ken Zheng posted a good overview of how to create a Site Collection in new Content Database http://littletalk.wordpress.com/2008/10/10/creating-a-site-collection-in-a-new-content-database/

I’d like to add some notes to Ken’s post, regarding planning site collections and sub-sites among different content databases.

There are some reasons, why you could have several content databases:

• MS recommends to have content database not more the 100Gb, otherwise it could hinder performance.
• Data usage optimization
• It simplifies farm backup and restoration.
• It provides flexibility for Disaster Recovery (DR) strategy.

Scenario - Data Usage

The major advance of several content databases is splitting your site collection and sub-sited among several content databases based on the content type and content usage. For example, you can have media sites and media data stored in separate content database and separate SQL server, which is tuned, indexed and partitioned to work with BLOB data; or sites which are used for document management, in separate SQL server with content database optimized for textual information.

If you will store different type of content information inside one big single content database in won’t help you to use all advantages of SQL Server to optimize your data usage.

Scenario – Backup/Restore

Having several content database makes you data restoration faster, because you need to restore only that content database, where origin site/data located. 

Scenario – DR

Usually when you implement DR strategy your DR box locates outside current network and outside the current organization, and you could have slow network connection there (reasonably slower then inside your network). So, setting SQL mirroring takes a lot of time if you have very big database. Moreover, with the different content databases you can leverage the importance of your data and mirroring to different locations, for example mirroring your vital financial data in one content database to the box outside your enterprise, and other content databases inside your network. 

Tips and Tools

Ken describes how to create new sites in new content databases, but what if you already have 200Gb content database and what to split and reorganize your data across several new content databases?! SharePoint doesn’t provide OOTB UI features for this. The standard way to reorganized you data for the sub-sites is to use STSADM tool (I will discuss moving site collections separately).

The way to move your sub-sites to new content database is to follow the next steps

1. Go to Central Administration > Application Management > Content Databases and set the existed content database to “Offline” mode
2. Add new content database in Central Administration > Application Management > Content Databases. (you can use different SQL server). Now all new sites will be created in this content database
3. Use the following command to export your site to the disk “stsadm -o export -url http://<path_to_site> -filename c:\<exportedSite>.exp -overwrite -nofilecompression -includeusersecurity -cabsize 1024”.
4. Delete your exported site collection in Central Administration > Application Management > Delete Site Collection
5. Create new empty site with the same path, via stsadm –o createweb/createsite;
6. Import you site to the same url, and site will be created in the new content database. Use the following command for this “stsadm -o import –url http://<path_to_site> -filename c:\<location_of_your_exorted_package>.exp -nofilecompression -haltonfatalerror –includeusersecurity”
   

Now you will have you sub-site in new content database. It’s not very user friendly approach, and is an error proned a bit.

But there is one small tool - SharePoint Administration Tookit, which helps you to to reorganise the whole site collections. This feature will install a new section inside “Applications”and you can move your site collection via Central Administration interface. 

Resume

Plan you site collections / sub-sites content with you database administration guys

Take into account that you can’t split site and site content between content databases, for example you can’t specify storing all site’s *.avi files in separate content db. I hope it will be changed in the next version of SharePoint, but now site and site content are stored together.

Mirror: Tips to create a Site Collection in new Content Database