SharePoint 2007. One Content Database per Site Collection

Tuesday, 16 September 2008 20:17 by RanjanBanerji

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.

Categories:   SharePoint
Actions:   E-mail | Permalink | Comments (5) | Comment RSSRSS comment feed

Comments

January 20. 2009 05:09

Leon Zandman

The stsadm command always creates a new database. You'd better use Gary Lapointe's Custom STSADM Extensions. He has made a command called "gl-createsiteindb" that's much more powerful than the built-in stuff. See: stsadm.blogspot.com/.../...e-site-in-database.html

Leon Zandman

June 21. 2009 00:05

lingaraj raul

whatever you are writing is correct . But when we made a big project having so many site collection . At that if we are creating so many content DB that's have boardern on the server and also confuse for the user. So In my point of view , we have to create less no. of content DB for a Web Application.

lingaraj raul

December 2. 2010 12:16

pingback

Pingback from supermallamerica.com

Five sure-fire hit boys’ toys this Christmas | Supermallamerica.com Blog

supermallamerica.com

July 30. 2011 01:19

Cesar Cedeno

Hi, Im looking for the most efficient way to create a site in a existing database via STSADM command line in sharepoint 2007. I have done it by setting the database status to offline and this works how ever if the database count is
very large it dosent seem to be very efficient way to acomplish the task, please if anyone know of a way other then
by GL-CREATESITEINDB extended command it will be greatly appriciate it.

thanks in advance for any help!

Cesar C.

Cesar Cedeno

September 15. 2017 17:29

pingback

Pingback from webdolum.com

Sharepoint 2007 Unknown Error Creating Web Application

webdolum.com

Add comment


(Will show your Gravatar icon)

  Country flag

biuquote
  • Comment
  • Preview
Loading