Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Managing SharePoint Content Databases with Central Administration

MSSQLTips author Ray Barley By:   |   Read Comments (8)   |   Related Tips: > Sharepoint
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:

  1. Create a web application
  2. View content database properties
  3. Create one or more content databases and set their properties
  4. 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:

SharePoint 2010 Central Administration

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:

Create a web application in SharePoint 2010 Central Administration

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.

Configure an application pool in SharePoint 2010 Central Administration

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.

Configure Database Name and Authentication in SharePoint 2010 Central Administration

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.

Configure a Failover Server in SharePoint 2010 Central Administration

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:

View Content Database Properties in SharePoint 2010 Central Administration

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:

Identify a web application you want to associate with the content database in SharePoint 2010 Central Administration

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.


Last Update: 2/23/2012


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 11, 2014 - 9:25:13 AM - Raymond Barley Read The Tip

Here is a sample script to grant access to the WSS_Content database.  I'm in the sysadmin server role; you may be able to run this with lesser permissions; you can investigate that.

Change MYDOMAIN\USERNAME to your domain and username

USE [master]

GO

CREATE LOGIN [MYDOMAIN\USERNAME] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

USE [WSS_Content]

GO

CREATE USER [MYDOMAIN\USERNAME] FOR LOGIN [MYDOMAIN\USERNAME]

GO

USE [WSS_Content]

GO

ALTER ROLE [db_datareader] ADD MEMBER [MYDOMAIN\USERNAME]

GO


Tuesday, March 11, 2014 - 9:09:37 AM - Raymond Barley Read The Tip

I'm going to assume you are trying to use your account to access the WSS_Content database outside of SharePoint like doing a query in SQL Server Management Studio.  The steps you have taken do not give your account any access to the WSS_Content database.  You have to grant access to the WSS_Content database to your account.  At a minimum you need:

- A login for the SQL Server

- A user mapped to this login that is a member of the db_datareader database role (this will allow you to query)

Alternatively if your account is a member of the sysadmin server role you get acces to everything.


Tuesday, March 11, 2014 - 5:41:50 AM - Arushi Read The Tip

I am a newbie for sharepoint. I have given administrator right to an already created Sharepoint site. I have full access to Sharepoint Central Administration. However, I am not able to open or access WSS_Content database on the sql server.

Can you please tell me what could be the reason and how can I get access to the database?


Saturday, January 19, 2013 - 1:00:09 PM - Ray Barley Read The Tip

I would suggest using PowerShell to do this stuff.  Take a look at this tip; the commands are pretty easy.

http://www.mssqltips.com/sqlservertip/2608/managing-sharepoint-content-databases-with-powershell/

 


Friday, January 18, 2013 - 6:27:19 PM - mihir Read The Tip

i have 8 servers farm and i have CA in one machine when i try to attach content database to one web application some time it can been seen from server (Ex:1,2,4) and now if i detach this content db and re attach it and this site collection is visible in server (5,6,1)

now again detach attach it is available in all servers why it is inconsistent


Monday, July 02, 2012 - 1:03:51 PM - Ray Barley Read The Tip

Here is the Central Administration shortcut command line:

"C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN\psconfigui.exe" -cmd showcentraladmin

 

 


Monday, July 02, 2012 - 11:56:13 AM - Ernest Read The Tip

I accidently copied over my top link Central Administration site. Is there a way to get Central Administration back? Thank you ..

 

 


Monday, April 09, 2012 - 6:17:35 AM - Adrian Read The Tip

There is a free solution available that automatically selects the smallest content database for new site collections: http://www.fiechter.eu/blog/Lists/Posts/Post.aspx?ID=21&mobile=0




 
Sponsor Information