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 your 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 than 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 the 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 (if you have Publishing feature activated, you can do the same steps via Site Settings menu)
The way to move your sub-sites to new content database is to follow the next steps (take into account that you need to move either the site collection or create new site collection for your sites, because sites can't be moved without site collection)
- Go to Central Administration > Application Management > Content Databases and set the existing content database to “Offline” mode (or quiescence it via Central Administration)
- 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
- Use the following command to export your site to the disk “stsadm -o export -urlhttp://<path_to_site> -filename c:\<exportedSite>.exp -overwrite -nofilecompression -includeusersecurity -cabsize 1024”.
- Delete your exported site collection using "ststadm -o deleteweb"
- Create new empty location with the same path, via stsadm –o createweb/createsite;
- 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 –urlhttp://<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.
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.
Take into account that Sites locate inside Site Collection and can't be moved to new Content DB without Site Collection