Database types and descriptions (SharePoint Foundation 2010)

 

Applies to: SharePoint Foundation 2010

This article describes the databases that are installed for Microsoft SharePoint Foundation 2010. It includes some sizing and placement information.

Databases for SharePoint Foundation 2010 can be hosted in Microsoft SQL Server 2008 R2, SQL Server 2008 with Service Pack 1 (SP1) and Cumulative Update 2, or SQL Server 2005 with SP3 and Cumulative Update 3. Stand-alone installations can also be hosted in the Express Editions of SQL Server 2008 R2 or SQL Server 2008. For more information see Hardware and software requirements (SharePoint Foundation 2010).

Note

The database names listed in this topic are automatically created when you run the SharePoint Products Configuration Wizard. You do not have to use these naming conventions. You can either specify database names when you create them, or change the database names after they have been created. For more information, see Deploy by using DBA-created databases (SharePoint Foundation 2010).

The database sizes listed in this article are based on the following ranges.

Descriptor	Size range
Small	1 gigabyte (GB) or less
Medium	Up to 100 GB.
Large	Up to 1 terabyte
Extra-large	1 terabyte or more

In this article:

• SharePoint Foundation 2010 databases

• SQL Server system databases

For a graphical overview of the databases used by SharePoint Foundation 2010, see Database model (https://go.microsoft.com/fwlink/p/?LinkId=187968).

SharePoint Foundation 2010 databases

The following databases are part of a SharePoint Foundation 2010 deployment. These databases are also part of any other SharePoint 2010 Products deployment.

Configuration

The configuration database contains data about SharePoint databases, Internet Information Services (IIS) Web sites, Web applications, trusted solutions, Web Part packages, site templates, and Web application and farm settings specific to SharePoint 2010 Products, such as default quota settings and blocked file types.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	SharePoint_Config
Location requirements	None
General size information and growth factors	Small. However, transaction log files are likely to become large. For more information, see Additional notes, below.
Read/write characteristics	Read-intensive
Recommended scaling method	Must scale up; that is, the database must grow larger, because only one configuration database is supported per farm. (Significant growth is unlikely.)
Associated health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and System Center Data Protection Manager (DPM) 2010. The configuration database is a special case for backup and recovery. For more information, see Additional notes below.
Default recovery model	Full. We recommend that you switch the configuration database to the simple recovery model to restrict growth of the log file.
Supports mirroring within a farm for availability	Yes
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

Additional notes

Transaction log files. We recommend that you back up the transaction log for the configuration database regularly to force truncation, or — if you are not mirroring your system — change the database to run in Simple recovery mode. For more information, see Transaction Log Truncation (https://go.microsoft.com/fwlink/p/?LinkId=186687).

Backup and recovery. The configuration database is backed up when you perform a SharePoint farm configuration and content backup, and some configuration settings from the database are exported and stored as XML files. When a farm is restored, the configuration database is not restored. Instead, the saved configuration settings are imported. The configuration database can be successfully backed up and restored by using SQL Server or other tools if the SharePoint farm is first taken offline.

Note

Many configuration settings are not saved during a farm configuration-only backup or restore, in particular Web application settings, service application settings, and settings that are specific to the local server. These settings are saved during a farm content and configuration backup, but some of them, such as service application proxy settings, cannot be restored during a farm recovery. For information about what is saved during a configuration backup, see Back up a farm configuration (SharePoint Foundation 2010). For information about how to document and copy configuration settings that are not backed up, see Copy configuration settings from one farm to another (SharePoint Foundation 2010).

Central Administration content

The Central Administration content database is considered to be a configuration database. It stores all site content, including site documents or files in document libraries, list data, and Web Part properties, in addition to user names and rights for the Central Administration site collection.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	SharePoint_AdminContent
Location requirements	None
General size information, and growth factors	Small.
Read/write characteristics	Varies
Recommended scaling method	Must scale up; that is, the database must grow larger, because only one Central Administration database is supported per farm. (Significant growth is unlikely.)
Associated Health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and DPM 2010. The Central Administration content database is a special case for backup and recovery. For more information, see Additional notes below.
Default recovery model	Full
Supports mirroring within a farm for availability	Yes
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

Additional notes

Backup and recovery. The Central Administration content database is backed up when you perform a SharePoint farm configuration and content backup. When a farm is restored, the Central Administration content database is not restored. The Central Administration content database can be successfully backed up and restored by using SQL Server or other tools if the SharePoint farm is first taken offline.

Content databases

Content databases store all content for a site collection, including site documents or files in document libraries, list data, Web Part properties, audit logs, and sandboxed solutions, in addition to user names and rights.

All the data for a specific site collection resides in one content database on only one server. A content database can be associated with more than one site collection.

Content databases also contain the Microsoft Office Web Apps cache, if Office Web Apps have been deployed. Only one cache is created per Web application. If multiple site collections that are stored in different content databases have Office Web Apps activated, they will all use the same cache. You can configure the size of cache, the expiration period, and the location. For more information about the size of the Office Web Apps cache, see Manage the Office Web Apps cache.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	WSS_Content
Location requirements	None
General size information, and growth factors	We strongly recommended limiting the size of content databases to 200 GB to help ensure system performance. For more information, see Additional notes, below. Content database size varies significantly by usage. For more information, see Additional notes, below.
Read/write characteristics	Varies by usage. For example, collaboration environments are write-intensive; document management environments are read-intensive.
Recommended scaling method	The content database that supports a site collection must scale up; that is, the database must be able to grow larger as needed. However, you can create additional site collections that are associated with a Web application and associate the new site collection with a different content database. Also, if a content database is associated with multiple site collections, you can move a site collection to another database.
Associated Health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and DPM 2010.
Default recovery model	Full
Supports mirroring within a farm for availability	Yes
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	Yes

Additional notes

Recommended content database size limitations

We strongly recommend that you limit the size of content databases to 200 GB to help ensure system performance.

Important

Content database sizes up to 1 terabyte are supported only for large, single-site repositories and archives in which data remains reasonably static, such as reference document management systems and Records Center sites. Larger database sizes are supported for these scenarios because their I/O patterns and typical data structure formats have been designed for, and tested at, larger scales. For more information about large-scale document repositories, see "Estimate Performance and Capacity Requirements for Large Scale Document Repositories", available from Performance and capacity test results and recommendations (SharePoint Server 2010).

Content database size estimation

Content database size varies substantially with the usage of the site. Growth factors include the number of documents, number of users, use of versioning, use of Recycle Bins, size of quotas, whether the audit log is configured, and how many items are chosen for auditing.

If Office Web Apps are in use, the Office Web Apps cache can significantly affect the size of a content database. For more information about the size of the Office Web Apps cache, see Manage the Office Web Apps cache.

Usage and Health Data Collection database

The Usage and Health Data Collection service application database stores health monitoring and usage data temporarily, and can be used for reporting and diagnostics. The Usage and Health Data Collection database is the only SharePoint database that supports schema modifications.

Note

For more information on supported Read operations, see "Read Operations Addendum" in KB 841057: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	WSS_UsageApplication
Location requirements	The Usage and Health Data Collection database is very active, and should be put on a separate disk or spindle, if possible.
General size information, and growth factors	Extra large. Database size depends on the retention factor, number of items enabled for logging and external monitoring, how many Web applications are running in the environment, how many users are currently working, and which features are enabled.
Read/write characteristics	The Usage and Health Data Collection database is very write-heavy.
Recommended scaling method	Must scale up; that is, the database must grow larger, because only one logging database is supported per farm.
Associated Health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and DPM 2010.
Default recovery model	Simple
Supports mirroring within a farm for availability	Yes. However, although you can mirror the Usage and Health Data Collection database, we do not recommend that you do. It is easily re-created in the event of a failure.
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	Yes. However, although you can asynchronously mirror or log-ship the Usage and Health Data Collection database, we do not recommend that you do. It is easily re-created in the event of a failure.

Business Data Connectivity database

The Business Data Connectivity service application database stores external content types and related objects.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	Bdc_Service_DB_
Location requirements	None
General size information, and growth factors	Small. Size is determined by the number of connections.
Read/write characteristics	The Business Data Connectivity database is very read-heavy.
Recommended scaling method	Must scale up; that is, the database must grow larger, because only one Business Data Connectivity database is supported per farm. (Significant growth is unlikely.)
Associated Health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and DPM 2010.
Default recovery model	Full
Supports mirroring within a farm for availability	Yes
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

Application Registry database

The Application Registry service application database stores backward-compatible information that is used to connect to information that is used by the Microsoft Office SharePoint Server 2007 Business Data Catalog API.

Note

When you have finished migrating an application from the Office SharePoint Server 2007 Business Data Catalog, the Application Registry service application can be disabled and the database can be deleted.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	Application_Registry_server_DB_
Location requirements	None
General size information, and growth factors	Small. Size is determined by the number of connections.
Read/write characteristics	Read-heavy.
Recommended scaling method	Must scale up; that is, the database must grow larger, because only one Application Registry service database is supported per farm. (Significant growth is unlikely.)
Associated Health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and DPM 2010.
Default recovery model	Full
Supports mirroring within a farm for availability	Yes
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

Subscription Settings database

The Microsoft SharePoint Foundation Subscription Settings service application database stores features and settings for hosted customers. The Subscription Settings service application and database are not created by the SharePoint Products Configuration Wizard — they must be created by using Windows PowerShell cmdlets. For more information, see New-SPSubscriptionSettingsServiceApplication.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard	SubscriptionSettings_
Location requirements	None
General size information, and growth factors	Small. Size is determined by the number of tenants, farms, and features supported.
Read/write characteristics	The subscription database is read-heavy.
Recommended scaling method	Scale up the database that supports the service application instance. You can scale out by creating additional instances of the service application, however, the decision to create a separate service application is likely to be based on business, rather than scale, requirements.
Associated Health rules	None
Supported backup mechanisms	SharePoint Foundation 2010 backup and recovery, SQL Server, and DPM 2010.
Recommended recovery model	Full
Supports mirroring within a farm for availability	Yes
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

SQL Server system databases

SharePoint Foundation 2010 is built on SQL Server, and as a result, makes use of the SQL Server system databases. SQL Server does not support users' directly updating the information in system objects such as system tables, system stored procedures, and catalog views. Instead, SQL Server provides a complete set of administrative tools that let users fully administer their system and manage all users and objects in a database. For more information about the SQL Server system databases, see System Databases (https://go.microsoft.com/fwlink/p/?LinkId=186699).

master

The master database records all the system-level information for an instance of SQL Server.

Default database name	master
Location requirements	None
General size information, and growth factors	Small
Read/write characteristics	Varies
Recommended scaling method	Scale up. (Significant growth is unlikely.)
Associated Health rules	None
Supported backup mechanisms	SQL Server backup and recovery
Default recovery model	Simple
Supports mirroring within a farm for availability	No
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

model

The model database is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database — such as database size, collation, recovery model, and other database options — are applied to any databases created afterward.

Default database name	model
Location requirements	None
General size information, and growth factors	Small
Read/write characteristics	Varies
Recommended scaling method	Scale up. (Significant growth is unlikely.)
Associated Health rules	None
Supported backup mechanisms	SQL Server backup and recovery
Default recovery model	Full
Supports mirroring within a farm for availability	No
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

msdb

The msdb database is used by SQL Server Agent for scheduling alerts and jobs.

Default database name	msdb
Location requirements	None
General size information, and growth factors	Small
Read/write characteristics	Varies
Recommended scaling method	Scale up. (Significant growth is unlikely.)
Associated Health rules	None
Supported backup mechanisms	SQL Server backup and recovery
Default recovery model	Simple
Supports mirroring within a farm for availability	No
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No

tempdb

The tempdb database is a workspace for holding temporary objects or intermediate result sets. It also fills any other temporary storage needs. The tempdb database is re-created every time SQL Server is started.

Default database name	tempdb
Location requirements	Locate on a fast disk, on a separate spindle from other databases. Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server and then adjust the number of files up or down as necessary. Be aware that a dual-core CPU is considered to be two CPUs.
General size information, and growth factors	Small to extra-large. The size of the tempDB database goes both up and down quickly. Size depends on how many users are using the system, in addition to the specific processes that are running; for example, online rebuilds of large indexes, or large sorts cause the database to grow quickly.
Read/write characteristics	Varies
Recommended scaling method	Scale up
Associated Health rules	None
Supported backup mechanisms	SQL Server backup and recovery
Default recovery model	Simple
Supports mirroring within a farm for availability	No
Supports asynchronous mirroring or log-shipping to another farm for disaster recovery	No