In one of my previous post we have created a sitecollection in SharePoint 2013.Whenever we create site collection by default it will get stored in a content database which you all know.
some scenarios we need sitecollection to be stored in a specific content
database. Why? What is reason for storing the site collection in a
separate or a specific content database?.
To answer the above question, we need to know that one web application can support multiple content databases and you can store only 200GB of content in one content database. Please see the Software boundary limits for SharePoint 2013. You can download the complete guide on how to create a site collection in a specific content DB by clicking on the image below.
In real world scenarios you will have to store or create sitecollection into a separate content Database. For example if you have a large number of team sites which may increase in numbers and leads to the size in future to 500GB to 1TB or even more. So you need to create additional content databases for a particular web application.
Example: If you are building portal for a customer and his company have different departments like Finance, marketing, Sales etc. Where you need to store more amount data, in those cases you need to create multiple content databases in a single web application.
Try to use below naming conventions to maintain the databases in a long term prospective for SQL guys who are maintaining your SharePoint databases.
For finance team "Finance_Team_site_Content_Database" , for marketing "Marketing_Team_site_Content_Database" and for Sales "Sales_Team_site_Content_Database" etc.
To achieve our goal perform below steps.
From Central administration screen and under "Application Management" click on Manage Content Databases”.
Click on "Add a Content database".
For example enter Database Name as "Finance_Team_site_Content_Database" under "Database Name and Authentication section" in "Add a Content database" section, leave remaining fields as it is and click ok.
The field Database Capacity Settings can be used, if you want to limit how many sitecollections you can create in a particular content database and give a warning message to the end user while creating the content database.
Please see the below Figure :Add Content Database to add the content database.
Figure:Add Content Database
For each content Database you add from central administration it will create a database in SQL server.
You can check if you have access to it or ask your SQL DBA if it’s managed environment, if it is a local VM then its up to you :) you can test whatever you want.
Add few more content databases for testing. I have added four content databases here. "Finance_Team_site_Content_Database", "Marketing_Team_site_Content_Database", "Sales_Team_site_Content_Database" and "SharePoint_Journey_2013_Content_Database".
If i create a new site collection from the central administration screen I want to store or create site collection need to store in a particular Content Database, this is our goal correct.
Let’s say I want one Team site template sitecollection for SharePoint-Journey Sales, when I create a site collection with the name of SharePoint-Journey sales it has to store in "Sales_Team_site_Content_Database" content Database.
You can see Database status field in the Figure: Content Databases List shows as started for all the databases.
Figure: Content Databases List
Before creating sitecollection you have to change the status of each content database status to stopped and only required content database should show started. In our example "Sales_Team_site_Content_Database" content database should be started.
See the below Figure:Database settings , click on all Database names
and change Database status from ready to offline except "Sales_Team_site_Content_Database" and click ok.
You can see the Figure : Content Databases where only one database status is Started all other remaining are in Stopped status and oberve the "Current number of site collections" against the "Sales_Team_site_Content_Database" is zero.
Figure :Content Databases
In the final step we will create sitecollection and try to find in which Content Database it will get stored.
Please use post on how to create a sitecollection in SharePoint 2013. After creating a site collection go to manage Content Databases under Application management section, you can see that "Current number of site collections” is showing "1" against "Sales_Team_site_Content_Database" content Database. See the below Figure. Content Databases
After completion of above steps don’t forget to change the other databases settings to Ready i.e. started. To change the status click on Database name and change Database status from offline to ready and click ok. Repeat this step for all the Content databases those are offline.
Congratulation you have completed all the required steps.
Do you want guide on how to achieve these steps? I created a step by step guide to create a site collection in a specific content Database.
Before concluding the topic, one question here is what will happen if you change the status from Ready to offline?.
The answer is during offline You can open the sites in that particular content database but write operation are not possible.
You have created a site collection successfully in specific content databases from list of content databases that are attached to the web application.
Do you want to learn more about SharePoint Fundamentals? Click the image below and enroll in the FREE course.
Devendra Velegandla is a five-time recipient of Microsoft's Most Valuable Professional (MVP) award (2014-2019) for Office Apps and Services, Office Servers and Services MVP and SharePoint Server. He received the MCC award for his contributions in Microsoft MSDN/TechNet forums. He loves sharing articles, videos, and tutorial on SharePoint and Office 365.