Best Practices for SharePoint Content Databases in SQL Server

By: Ray Barley | Read Comments (12) | Print Ray is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert. Related Tips: More

Problem
I'm an experienced SQL Server DBA, but new to administering SharePoint 2007 databases.  Our current SharePoint databases are very large and getting bigger every day.  Based on my initial research the content database is the one I am going to focus on initially.  Can you give me the details I need to know to implement best practices for this database? 

Solution
I want to start out with a bit of clarification about SharePoint.  When I use the name SharePoint in this tip, I am referring to Microsoft Office SharePoint Server (MOSS) 2007 or Windows SharePoint Services 3.0 (WSS).  WSS was included with Windows Server 2003, while MOSS is a part of Microsoft Office and requires additional licensing.  Both WSS and MOSS utilize content databases; therefore this tip will apply to either one.

There are quite a few SQL Server databases created and used by SharePoint.  The content database is the repository for a lot of information such as web applications, site collections, sites, documents, lists, etc.  Most things that business users do in SharePoint utilize the content database; search is an exception which uses its own database.  As a general rule the normal database best practices apply to SharePoint databases, but there are exceptions.  In this tip we'll focus on the exceptions.  Let's start out with a little bit of SharePoint administration then discuss some best practices around dealing with very large content databases.

SharePoint Administration

SharePoint provides two tools for administration: the SharePoint Central Administration web application and a command line tool called STSADM.EXE.  The web application is easy to use and adequate in most instances.  You can launch the web application from the Start menu by clicking All Programs, Microsoft Office Server, then SharePoint 3.0 Central Administration.  The default location for the STSADM.EXE utility is \Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN.  STSADM is a very versatile tool supporting many operations through a whole bunch of command line parameters.

When you create a web application using SharePoint Central Administration, a content database will be created for you.  However, there are no parameters that you can specify for the database other than its name.  In order to specify things like database file size, maximum size, growth, and path you will need to create the database yourself then use STSADM to create the web application. 

Best Practices

The following represents Microsoft's guidance and best practices on content databases, as specified in various places on technet.microsoft.com:

• Limit the content database size to 100GB; use multiple content databases if necessary.
• Create multiple data files and spread them across multiple disks; only create files in the primary filegroup.
• The number of data files should be less than or equal to the number of core CPUs; count dual core processors as two CPUs; count each processor that supports hyper-threading as one CPU.
• Create data files of equal size.
• Consider using SQL Server backup and restore; the SharePoint built-in tools can only restore files to the backup location.

Based on the above recommendations, let's explore creating a content database and using STSADM to create a web application that uses the content database we created, add a content database to a web application and moving site collections from one content database to another.

Creating a Content Database

A sample script to create a content database is shown below:

CREATE DATABASE WSS_Content_MSSQLTIPS
ON PRIMARY
( NAME = MSSQLTIPS_01_data,
FILENAME =
    'F:\data\MSSQLTIPS_01_data.mdf',
SIZE = 20,
MAXSIZE = 40,
FILEGROWTH = 5 ),
( NAME = MSSQLTIPS_02_data,
FILENAME =
    'G:\data\MSSQLTIPS_02_data.ndf',
SIZE = 20,
MAXSIZE = 40,
FILEGROWTH = 5 )
LOG ON
( NAME = 'MSSQLTIPS_log',
FILENAME =
    'c:\data\MSSQLTIPS.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB );
go

alter database WSS_Content_MSSQLTIPS
set single_user with rollback immediate
go

alter database WSS_Content_MSSQLTIPS
collate Latin1_General_CI_AS_KS_WS
go

alter database WSS_Content_MSSQLTIPS
set multi_user
go

Below are the main points from the previous script:

• Multiple data files being created in the primary filegroup; I'm working with Microsoft Virtual PC and I only have three drives available.
• The size, maxsize and filegrowth parameters are just examples; specify appropriate values for your environment.  If you expect your content database will require more than 100GB, consider creating it with an initial size of 100GB then add content databases as necessary.
• The collation Latin1_General_CI_AS_KS_WS is required.

After creating the content database, there are two steps to be performed

• Set the database owner to the SharePoint Setup account
• Add the Shared Service Provider service account to the db_owner database role

There are many user accounts in a SharePoint implementation; you will need to get the above accounts from your SharePoint administrator.  Please refer to the TechNet article Deploy Using DBA-Created Databases for additional details.

Using the Content Database in a New Web Application

When you create a new web application, you have to use STSADM in order to use a content database that you created.  A sample STSADM command is shown below (for ease of reading the parameters are shown on separate lines; they must all be on a single line when you execute the command):

STSADM.EXE 
-o extendvs 
-url http://biwsssql2008:6666 
-donotcreatesite 
-exclusivelyusentlm 
-databaseserver biwsssql2008 
-databasename WSS_Content_MSSQLTIPS 
-apidtype configurableid 
-description MSSQLTIPS 
-apidname MSSQLTIPS 
-apidlogin biwsssql2008\spcontentpool 
-apidpwd pass@word1

Hopefully you can have your SharePoint administrator handle this task.  Since our focus is on SQL Server, I'll just highlight the database-related parameters which should be self-explanatory: databaseserver and databasename.  Please refer to STSADM documentation on the extendvs operation for details on the above parameters.

Monitoring the Content Database Size

As users add content to their SharePoint sites, the content database will grow.  You will want to monitor the size of the database file to stay within the 100GB limit per Microsoft's recommendation.  Take a look at our earlier tip Collecting SQL Server Database Usage Information for an example of how to track the size of your content database. 

You should also monitor the size of the site collections in the content database.  You can use the following STSADM command to query information about the content databases in a web application:

The -url parameter specifies the web application.  The above command will output an XML document showing the list of site collections, the content database where they live, the size of the site collection in megabytes, etc.  The following is a sample of the output:

In the next sections we'll discuss adding a content database to a web application and moving site collections between content databases.

Adding a Content Database

As you get a better idea of the growth of the content database, you can add one or more content databases to a web application if necessary.  In order to add a content database that you have created you will need to use the STSADM command.  The following is a sample command:

STSADM.EXE 
-o addcontentdb 
-url http://biwsssql2008:6666 
-databasename WSS_Content_MSSQLTIPS_02

Once again the parameters are shown on separate lines to make it easy to read; they need to be on a single line when you execute the command. 

When there is more than one content database associated with a web application, site collections are added to the content databases in a round-robin manner.

Moving Site Collections Between Content Databases

If you have a content database that is getting too big, you can move one of more of its site collections to a different content database.  Run the STSADM -o enumsites command described above and redirect the output to a file; e.g.:

Edit the output file using Notepad and delete the sites that you do not want to move; i.e. edit the sites.xml file so that it contains the list of site collections in a single content database that you want to move to another content database.  You can then use a command like the following to move the site collections to another content database:

STSADM.EXE
-o mergecontentdbs
-url http://biwsssql2008:6666
-sourcedatabase <databasename>
-destinationdatabase <databasename>
-operation 3
-filename sites.xml

Please note the following caveats on the mergecontentdbs operation:

• You should have your SharePoint administrator take care of this.
• Moving large site collections can take a long time.
• The mergecontentdbs operation was added in SharePoint service pack 1.
• There are some important fixes to mergecontentdbs in Windows SharePoint Services service pack 2.

Please refer to mergecontentdbs for additional details on this operation including space required, permissions, availability of site collections during execution, etc.

Next Steps

• The are many SharePoint-specific best practices for SQL Server databases; we will explore others in future tips.
• Take a look at Physical Storage Recommendations (Office SharePoint Server) for additional Microsoft guidance in this area.

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code