By: Ray Barley | Comments (8) | Related: > SharePoint Administration
Problem
I'm a DBA and I need to understand how to create SharePoint content databases and map out which site collections get stored in a particular content database. I've read the tip Managing SharePoint Content Databases with PowerShell, but I would rather learn how to do this using SharePoint's Central Administration since I'm a DBA and not a developer-type. Can you provide the details on using Central Administration to create content databases and map out my site collection storage?
Solution
There are a number of concepts about SharePoint 2010 that are of particular interest to DBAs; take a look at the tip Introduction to SharePoint 2010 for SQL Server DBAs for a quick overview. In this tip I'm just going to focus on the task at hand - managing content databases. The previous tip is important for understanding the big picture.
Content databases are used to store your SharePoint data. This data consists of sites, permissions, documents, lists, etc. Content database can grow pretty quickly so you should proactively manage creating content databases and deciding what data gets stored in each content database.
A content database is closely related to two other SharePoint objects: a web application and a site collection. A web application is backed by an Internet Information Services (IIS) web site, and it contains one or more content databases which can contain one or more site collections. Think of a site collection as the "container" for your data and also as the security boundary. Security is defined at the site collection level allowing you to control access to the sites and their data. For example, if you have sensitive data that only a few people can access, you might consider putting that site collection in its own content database allowing you to easily define the security of who can access the data.
From the standpoint of backups, restores and database maintenance (e.g. DBCC, index reorganization, etc.) having multiple content databases is essential in order to meet your service level agreements. I have one more tip planned which will go in to detail about setting up these types of maintenance jobs. There are a few things that DBAs want to be aware of that are particular to SharePoint content databases.
In this tip I will use a typical scenario to walk through how we create content databases in SharePoint Central Administration. Assume you want to create a SharePoint intranet. You want each department to have their own site for tracking calendars, storing documents, etc. The following are the steps you would perform in SharePoint Central Administration:
- Create a web application
- View content database properties
- Create one or more content databases and set their properties
- Create one or more site collections
In the sections that follow we will use Central Administration to perform these tasks.
Central Administration
When you install and configure SharePoint 2010, you will provision the Central Administration web application that SharePoint administrators use. The account you use will automatically be added to the SharePoint Farm Administrators group; you need to be a member of this group to access Central Administration. You can launch Central Administration from the Windows Start menu; i.e. Start, All Programs, Microsoft SharePoint 2010 Products, SharePoint 2010 Central Administration. When you launch Central Administration you will see the following page in your browser:
The options that we need can be found in the Application Management section of the Central Administration home page.
Create a Web Application
When you create a web application, you also create a content database. By default SharePoint will store the data for every site collection that you create for this web application in this content database.
To create a web application, click Manage web applications on the Central Administration home page. The following page will be rendered in your browser:
Click New on the ribbon to display the create web application page. There are a number of parameters that you need to enter; I'm going to focus just on the ones that are pertinent to the DBA; they are shown below.
The application pool account will be added to the db_owner database role in the new content database. This is the account that will be used by SharePoint to access the content database.
The database server and database name identify the new content database to be created. As an aside it's a good idea to use a SQL Server Alias for the database server to make it easier for you to move your databases to another server if necessary; you can edit the alias rather than have to go edit every content database's properties. For additional details on SQL Server aliases, take a look at the tip How to Setup and Use a SQL Server Alias.
Fill in the database server if you are using database mirroring. Note that you can do this at a later time; you have to configure mirroring; it's not done for you.
View Content Database Properties
Click Manage content databases on the Central Administration home page to view the properties of the content database that we just created with the new web application; the following page is displayed:
When you use Central Administration to create site collections in your web application, SharePoint decides which of the available content databases will be used to store your site collection's data. In order for you to determine which content database will be used, you have to set one of the following properties of the content database in order to stop SharePoint from storing new site collections in the content database:
- Change the database status from Ready to Offline; Offline just means that no new site collections can be created in the content databases
- Change the maximum number of site collections to be the same as the current number of site collections; you also need to change the site collection level warning to 1 less than the current number of site collections
To make either of these changes, click on the content database name hyperlink and you will be taken to the page where you can edit the properties. As a best practice you should make one or both of these changes after a new content database is created so that someone doesn't create a site collection and allow SharePoint to put it in the content database of its choice rather than yours.
Create a Content Database
Click Add a content database from the Manage Content Databases page to create a new content database. Note that you need to identify the web application that you want to associate with the content database. The following page will be displayed:
The above page has the same properties that we already discussed in the View Content Database Properties section above (the maximum number of site collections and site collection level warning are not shown in the above screenshot due to space limitations).
Next Steps
- Plan ahead and map out the content databases that will be used to store your site collections. As content databases can grow quickly, you want to be able to meet your service level agreements by spreading the data out over multiple databases and keeping the size manageable.
- Take a look at the tip Managing SharePoint Content Databases with PowerShell to see how to create content databases with PowerShell; you can do a lot more with PowerShell.
- Stay tuned for future tips that cover database additional administration topics related to SharePoint databases.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
