Unique Content Database per SharePoint Site Collection

It’s a question that comes up in every deployment of SharePoint: Should I have many site collections or one site collection with many sub sites?  My definitive answer: “It depends”.  

This post isn’t to address that however.  Let’s assume you decided on many site collections.  Which will bring you to another question you’ll ask yourself.  Should I have one content database for all of the site collections, or one content database for each site collection?  My definitive answer: “It depends”.

This post isn’t to address that either.  Let’s assume that you decided on one content database per site collection.  Or, let’s assume that you are going to create a new site collection and you want it to have its own content database.  Now that, we are going to address.

So how to in SharePoint?  Well, it’s not exactly intuitive, but none-the-less we can do it.

*****
Update: after comments here and on Twitter, just want to explicitly state (I was a little vague in the post) that there are other ways to do this than just this method – this is just how I do it. And be sure to set the databases back to ‘Ready’ when you are done creating your site collection(s).
*****

In Central Administration, you need to examine the current Content Database situation for your application.  Browse to content databases at: Central Administration > Application Management > SharePoint Web Application Management

In our example, we only have one Content Database (WSS_Content_Clients) with 1 site collection in it.

First, let’s create a new content database.  So, click on ‘Add a content database’ and go through the steps.  I recommend naming the content database the name of the site collection.  In our example, I called it WSS_Content_NEWSITECOLLECTION.

Now if we look at our content databases for our web application, you’ll see that we have two.  And WSS_Content_NEWSITECOLLECTION has zero site collections in it.

So what will happen if we create a new site collection in this Web Application?  When creating a new site collection in Central Admin there isn’t a means to select which content database to use.  Or is there?… Umm hmm.

Edit WSS_Content_Clients database (the database you don’t want to have the new site collection), and set it’s status to ‘Offline’.  Don’t worry, the database isn’t offline in typical sense.  It just means that new site collections can’t be created in it.  (This is just a bad name all together, but oh well).

Now if we look at our content databases, we’ll see that only our WSS_Content_NEWSITECOLLECTION is available for new site collections.

You’re all set now.  Go and create yourself a new site collection, and it will automatically create itself in our new content database, WSS_Content_NEWSITECOLLECTION.  Afterwards, you can stop it by setting it to offline as well to prevent other site collections being created in it.

There are other ways to achieve what we just did, but this one works for me.  Old habits are hard to break I guess.

Now, the next question that you’ll most likely end up asking is: How on earth am I going to figure out which site collection goes with what content database from a management standpoint?  Naming conventions don’t carry us but so far do they?

No worries there either, head over to your list of site collections for you application at:
Central Administration > Application Management > Site Collection List

Select the site collection, and it will tell you what Content Database it is using.