SharePoint 2010 and 2013 Content Databases(One database per Site Collection)

Is it possible have a Web Application with many Site Collections and multiple databases? One database per Site Collection?

Yes it is possible.

• We can have multiple databases attach to a web application(500 is limit).
• We can have the many site collections and provision one site collection into one content DB.

While learning more about SharePoint I wanted to figure out how the databases are set up for web applications and site collections. While digging into this, I noticed a few things that can be slightly confusing. When creating a web application you can specify the database you want the web application to use and all is well. Then, when creating a site collection within that web application you are no longer allowed to pick a database. By default, the site collection will use the same databases that are available for the parent web application. At this point I started asking myself some questions, what happens if your web application has multiple content databases associated with it? How do you know which database your site collection is being stored into?

To add another content database to your web application do the following:

1.) Navigate to Central Administration
2.) Application Management
3.) Manage content databases
4.) Select the Web Application in the top right
5.) Select add a content database

How does SharePoint assign site collections to content databases?
Now that you have two content databases how does SharePoint store site collections in them by default? SharePoint uses a technique called round robin, it will rotate out database for each site collection depending on how many site collections that database currently. For example, suppose you have one web application and three content databases (db1, db2, db3). You currently have two site collections in db1 and db2, db3 are empty. You create another site collection and it will be stored in db2, Create another site collection it will be stored in db3. SharePoint will use the database that has the least amount of site collections in it until all databases are equal. If two databases have the same amount of site collections SharePoint will place the site collection in the content database that has not been used in the longest amount of time.
The other way SharePoint picks which database to place the new site collection in is the maximum number of sites allowed per content database. The new site collection will go in the content database with the largest difference between the Maximum Number of Site Collections and the Current Number of Site Collections. For example…..
When creating a new site collection with the following information which content database will the site go into?

Content Database 1:
Current Number of Site Collections = 5
Maximum Number of Site Collections = 10
Difference = Maximum Number of Site Collections - Current Number of Site Collections = 5

Content Database 2: 
Current Number of Site Collections = 2
Maximum Number of Site Collections = 5
Difference = Maximum Number of Site Collections - Current Number of Site Collections = 3
The new site collection will go into Content Database 1. It has the greatest difference.
What if you want specific database for a specific site collection?
If you open the content database in central administration there is an option to change the database status from Ready to offline. This means that no new site collection can be created. That makes sense but I also just assumed that the current site collections would stop working once I made a content database offline, this is however not the case. The Site Collections in the database will continue to function as normal. Using this option you are able to set a specific Site Collection to a specific database.

1. Turn all current content database to offline

2. Create a new content database

3. Create your new site collection

This will automatically assign the site collection to your new content database. To make sure no other site collections are added to this you have two options. Turn the database into offline mode or set the Database Capacity Settings.

1. In central Administration

2. Application management

3. Manage Content Databases

4. Select the database

5. Settings are near the bottom

       6. Set the warning level to 0 and the max level to 1

The Database Capacity Settings are a tad confusing. It says:

“Number of sites before a warning event is generated”

It should say:

“Number of Site Collections before a warning event is generated”

You now know how content databases in SharePoint function and how to have a specific content database for a specific Site Collection.

Creating one Content Database Per Site Collection In SharePoint 2010 using Windows PowerShell 

One of the most common problems I see in a client built SharePoint environment is multiple site collections comingled in the same content database.  While this isn’t the end of the world, it’s a problem because SLAs, security, and even back strategies are often assigned to specific databases based upon the needs of the users that will be using the site collections hosted in each database.  If a site collection is not hosted in the database that the admin intended because SharePoint simply picks the first available content database with room  the administrator may unwittingly incorrectly back or, even worse, fail to backup a particular site collection because it does not exist in the proper database.  This is why I am a proponent of backing up at the Native SQL level but also at the site collection level.

On to the point of this article, how do I use PowerShell to create a site collection and assign the appropriate database

Create the Site Collection

First, launch the SharePoint 2010 Command Shell (do not just run PowerShell – although you can do this you have to manually import the SharePoint cmdlets.  If you receive the cmdlets not found issue then you have to modify your access to the SharePoint_Config database.

Now that PowerShell is ready, the command is quite simple. 

Create a new Explicit managed path that will host the site collection.  In the example below we are creating the “IT” managed path.

new-spmanagedpath -RelativeURL /IT -WebApplication http://sharepoint –Explicit

Create a new content database.  Many administrators get creative with the naming, I recommend you stick with best practices and prefix the content database WSS_Content_*

new-spcontentdatabase -name WSS_Content_IT -webapplication http://sharepoint

Create the Site Collection, note that  you can specify the template as a parameter here if you’d like.

new-spsite -name "IT" -ContentDatabase WSS_Content_IT-url http://sharepoint/IT/ -OwnerAlias "corp\spapppool" –SecondaryOwnerAlias “corp\spsearch”

Enable the publishing prerequisites

sp-enablefeature "PublishingPrerequisites" -url http://sharepoint/IT/

Enable the publishing feature itself

sp-enablefeature "PublishingResources" -url http://sharepoint/IT/