SP-2007. One Content Database per Site Collection

In SharePoint 2007 when you create an application you specify the name the database to use.  Most people, therefore, conclude that an application will use one database.  This is not necessarily a very scalable idea.  But then you will often hear that an application can have more than one database.  The more commonly heard statement is that a Site Collection can have its own database.

True, very true.  Each site collection under an application can be made to have its own database.  But how?  When you create a site collection you are never asked which database to use or if a new database needs to be created.

Well our friends at Microsoft have made this in a very confusing process.  But here goes:

• Let's start with creating a new application.  When we create the application we tell it to use the following database: WSS_CONTENT_1
• Now create a site collection (SC1) under this application and a few sites under the site collection.  All the sites will use the WSS_CONTENT_1 database.
• Now go to Central Administration, Applications, Content Databases.  Select WSS_CONTENT_1 and change its status to "Offline".
• Now under Central Administration, Applications, Content Databases create a new content database WSS_CONTENT_2.  Make sure its status is "Ready".
• Now create a new site collection SC2 and create a few sites for SC2.  They will use WSS_CONTENT_2.
• Now create yet another site under SC1.  It will use WSS_CONTENT_1.  This is despite the fact that WSS_CONTENT_1 is "Offline."

So what we have achieved through this roundabout way is a content database for each of our site collections SC1 and SC2.  It would have been a lot better if when creating a site collection in Central Admin one was asked if a new database is to be created or if one wants to use an existing database.

In the Central Administration, Application Management, Content Database module setting a database offline means that from this point on no new site collection will be created in this database.  However this database is still usable and new sub-sites under an existing site collection can be created.  One has to be very careful to take each content database offline once a site collection has been created to prevent any other site collection from being created to use it.  This way you can make sure that each site collection has its own database. 

BTW, the same can be achieved via an stsadm command:

stsadm -o createsiteinnewdb -url http://your.application.com/SiteCollection -owneremail owner@application.com -ownerlogin Application\Owner -sitetemplate sts -title “Title” -databaseserver servername -databasename WSS_Content_Somename

Why would you want a site collection to have its own database?  Well that depends on what you are building and is a subject that is best handled in another post.