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.
Thanks for reading!