SharePoint Content Database per Site Collection

The other day I was told about our SharePoint 2010 site structure and its related content database , well I was told that the site was structured something like the diagram below that I have conjured up (apologies for my bad artistic effort):-

Site Structure

Yes that’s right – under a single web application managed by IIS, there a number of site collections, but each site collection has its own content database. It appears I am well behind the curve, as its quite common in SharePoint deployments and has been a norm in the SharePoint world for many years (pre SP 2010). The default action of course is that we specify a content database when we create the web application that will host our site collection, and we can if we want after some time – just add another content database for our site collection using the Central Administration UI facility. But to actually create a content database per site collection requires a bit of more work. Why might someone want to do this? – I guess it all comes down to how you want to manage the content data based on the site collections you have, and the security implications for it. If each site collection within a single web application will represent something different in terms of its data content from other site collections then why would you not want to separate the data into actual physically different databases? It seems like a good idea to be honest.

The steps to create a content database per site collection for a single web application (using the diagram above as our example) are as follows:-

  • First of all we will use the Central Admin UI to create a new web application called MySiteCollections, running in a named pool called MySiteCollectionsPool, and setting its content database to WSS_Content_SiteCollections1.
  • We then create a new site collection for our new application MySiteCollections – let’s just create a team site. Once created it will be accessed using http://{servername}:{port}/ and this is our top-level site collection which will use the default content database for our web application WSS_Content_SiteCollections1.
  • So our next step will be to create a new site collection – which will logically be at the same level as SiteCollections1 by virtue of the fact that will be created to use a separate content database which we will call WSS_Content_SiteCollections2 and the site name we will give is – yes you have guessed it – SiteCollections2. The site will be accessed using the link http://{servername}:{port}/Sites/SiteCollections2

To do this we need to use a combination of the Central Admin UI, and also use PowerShell that can be accessed via All Programs > Microsoft SharePoint 2010 Products > SharePoint 2010 Management Shell. Therefore you will need administrative rights to both toolsets, and when you do start up the PowerShell tool for SharePoint 2010 ensure you open it by righ-clicking on the menu short cut and selecting the ‘Run as Administrator’ option. The same also applies when opening up the Central Administration website – you need to right click and select the ‘Run as Administrator’ option.

Now before I begin there are a multitude of blogs and technical articles that describe what I am about to do – but most use some complex method that requires fiddling about with existing content databases, but the method I present here is the most simple I have researched. All of what I present below has been tested in SharePoint 2010, and the process described CANNOT be different in any of the versions of SharePoint 2010 i.e. foundation, standard/enterprise server.

Step 1: – Setting up the managed path for the new site collection

We have stated that we want our new site to be accessible from:-
http://{servername}:{port}/Sites/SiteCollections2
so what we do first is create the path that will be used by the new site collection using the Central Admin UI to define the managed path by:-

  • Go to the Central Administration UI – and click the option ‘Manage Web Applications’ under the section header Application Management.
  • From the list of web applications that are listed – click on the web application that was created initially to host the first site collection as described previously. When you click on it the ribbon menu options become enabled for that web application.
  • Click on the ‘Managed Paths’ option in the ribbon menu – as shown below:-

Selecting the Managed Path Option

When you click on that ‘Managed Paths’ option a dialog will appear where you can setup an ‘Explicit’ or ‘Wildcard’ inclusion path for the site collection URL we want to create. For our purpose we want to access the site when the URL includes:

/sites/SiteCollections2

Therefore we create an ‘Explicit’ inclusion path named ‘SiteCollections2’ as shown below:-

Set Managed Path

Click on ‘Add Path’, this will move the entry into the top section of the dialog which lists all the Included paths for the Web Application. Then click OK to register the new managed path setting.

Alternatively:

You can create the managed path entry as we have done above using PowerShell via the SharePoint 2010 Management Shell. All you need to do is type the following command – but remember to replace the {servername}:{port} with the values applicable to your own environment :-

PS C:\> New-SPManagedPath -Explicit “SiteCollections2″ -WebApplication “http://{servername}:{port}”

Once the above command executes – you should be able to see the new managed path registered through the Central Admin UI, or you could type in:-

PS C:\> Get-SPManagedPath –WebApplication “http://{servername}:{port}”

The above will display all the managed paths setup for the web application.

Step 2: Create the new Site Collection with new Content Database

We can now create new site collection that will be accessed through the managed path we have just created, but also the new site collection must use a new content database. We can do this simply by using PowerShell (accessed via All Programs > Microsoft SharePoint 2010 Products > SharePoint 2010 Management Shell ). Before we create the site collection, we will create the content database using the following command:-

PS C:\> New-SPContentDatabase “WSS_Content_SiteCollections2″ -WebApplication “http://{servername}:{port}”

The above command will create the content database named WSS_Content_SiteCollections2 and associate this database with the web application specified.

The next step is to actually create the site collection, which will be specified a URL that includes the managed path we created previously, and use the content database we just created above. The following PowerShell command will do this:-

PS C:\> New-SPSite “http://{servername}:{port}/sites/sitecollections2″ -OwnerAlias “{DOMAIN\USERNAME}” -Language 1033 -Name “SiteCollections2″ -ContentDatabase “WSS_Content_SiteCollections2″

Replace the {DOMAIN\USERNAME} with your domain name and user name.  Also more importantly make sure that you launch the SharePoint 2010 PowerShell tool as an Administrator.  You can usually do this by right clicking on the short cut and selecting the option ‘Run As Administrator’.

If the command above is successful then you will receive an output like the one I got when executing the command as shown below:-

New-SPSite Creation Output

The above is my run of the command on my system, and to check that the content database has now got a site collection associated with it (i.e. WSS_Content_SiteCollections2) you can run the Get-SPContentDatabase command against your web application, there should be at least one site collection using this content database. Below is a screenshot from PowerShell when I execute the command:-

Get-SPContentDatabase Execution Output

Now when you actually access the site at http://{servername}:{port}/Sites/SiteCollections2 for the first time you will be presented with a web page to select the site template, and set new security groups for the site collection. A screenshot is shown below when accessing for the first time:-

Accessing new Site Collection First Time

After the site collection is created with the template – the only way of accessing the site is actually putting in the correct URL in the browser, there will be no differences in the menu structure either top-link or quick-links area between the SiteCollection1 and SiteCollection2 (assuming they are both created with the Team Site template). So possibly your next step is to add a link to the site collection in the top-link bar of the first site collection that was created. This way at least you can access the site from http://{servername}:{port}/ .

Summary

That is essentially the process that must be followed – as can be seen from the above in summary we can create this type of site structure where each site collection will have its own content database after creating the initial web application and site collection using just three PowerShell commands:-

  • New-SPManagedPath
  • New-SPContentDatabase
  • New-SPSite

Thats it folks – personally I think the whole process should be made easier, as the actual requirement to have this structure would be a common theme within various businesses that have different functions, which may have very little in common or wish to maintain different levels of security. I hope the upcoming SharPoint 2013 has in-built options to do this via the UI.

Posted in: Microsoft TechBook
:

3 Responses to “SharePoint Content Database per Site Collection”

  1. Toney Rochat says:

    Hello there! Do you use Twitter? I’d like to follow you if that would be okay. I’m undoubtedly enjoying your blog and look forward to new posts.

  2. Marcos Nakhle says:

    Hey there just wanted to give you a quick heads up and let you know a few of the pictures aren’t loading correctly. I’m not sure why but I think its a linking issue. I’ve tried it in two different web browsers and both show the same results.

  3. msvcp100.dll says:

    Pretty! This was a really wonderful post. Many thanks for providing these details.

Leave a Reply