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 -owneremail -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

SharePoint 2007 - Changing Content Without Creating a New Version

Tuesday, 10 June 2008 23:37 by RanjanBanerji

SharePoint offers a neat feature that creates a version of any content the moment you edit it.  This way you maintain a history of all changes, you know who changed information and of course you can revert back to a prior version.  Clearly a great feature.  But what happens if you want to make a certain change without creating a version.  No, I am not talking about turning version feature off.  What if I want to keep versions but not for this one change.

Let me explain. 

  • Imagine a document library or an image library with large numbers (1000s) of large (file size) documents/images. 
  • Imagine that these files (documents/images) add up to 10 GB. 
  • In addition to the files these libraries store properties associated with the file.  Let's suppose one of the property is the state in which the document was created and was represented by the 2 letter symbol of the state (yep, I am talking about states in the US). 
  • Now suppose the customer comes to you and says "I don't want 2 letter state codes, I want the full state name listed"


So what are you going to do?  You can assign the task to some poor person to make the data changes (Apparently a path chosen by many SharePoint experts) or you can write some code to do so.  Either way you are going to encounter one large problem.  You are about to experience a huge increase in the size of your database.  Based on my example the increase will be 10 GB.  You see as you make this trivial edit SharePoint will make a new version of not just this edit but of the entire object which will include the entire file (image or document) in question.  So if you had files adding up to 10 GB you will now have 20 GB worth of files.  But is this specific change worth creating a version for?

Also, if you attempt to make this change to all such files as mentioned in my example, irrespective of the approach you took you can make changes only to items that are not checked out to others.

So you have two problems:

  • Database bloat
  • Inability to complete the task due to certain items checked out by others


Now if you took the path to update each item via writing code you probably did it as follows (pseudo code included):

public void UpdateItem() { 
        SPListItem listItem = web.GetListItem( fileNameAndPath ); //web is an SPWeb 
        //Get the file
        SPFile file = null;
        file = listItem.File;

        listItem[ "STATE" ] = stateLookup[ listItem[ "STATE" ] ];


        file.CheckIn( "Metadata Update" );
        file.Publish( "Metadata Update" );
        file.Approve( "Metadata Update" );



But this approach will create a version and will therefore result in database bloat.  An alternative approach would be to use the system update feature as follows:

public void UpdateItem() {
    SPListItem listItem = web.GetListItem( imageNameAndPath ); //web is an SPWeb
    listItem[ "STATE" ] = stateLookup[ listItem[ "STATE" ] ];  //Lookup the full state name based on the two letter code and assign it to the property
    listItem.SystemUpdate( true );

Now with this approach you have the following advantages:

  • No need to check out.
  • It does not matter if the item is checked out by another user.
  • No version is created so no database bloat.

I must add that this approach must be used with extreme caution.  It is not a good practice to change items that others have checked out and if someone has versioning turned on on a list there is probably a reason for it.  Having said that now you know you have a way to get around certain problems if needed.


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