Database types and descriptions (SharePoint Server 2010)

 

Applies to: SharePoint Foundation 2010, SharePoint Server 2010

This article describes the databases that are installed for Microsoft SharePoint Server 2010. It includes some sizing and placement information. For more information about where to locate databases, see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010).

Databases for SharePoint Server 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 Server 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 Server 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

  • SharePoint Server 2010, Standard Edition databases

  • SharePoint Server 2010, Enterprise Edition databases

  • Project Server 2010 databases

    If you are running SharePoint Server 2010, you may also have Microsoft Project Server 2010 in the environment.

  • FAST Search Server for SharePoint 2010 databases

    If you are running SharePoint Server 2010, you may also have Microsoft FAST Search Server 2010 for SharePoint in the environment.

  • SQL Server system databases

  • SQL Server Reporting Services databases

For a graphical overview of the databases used by SharePoint Server 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 Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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 in SharePoint Server 2010. For information about how to document and copy configuration settings that are not backed up, see Copy configuration settings between farms (SharePoint Server 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. If Microsoft SQL Server PowerPivot for Microsoft SharePoint is installed, the Central Administration content database also stores the Excel worksheets and PowerPivot data files used in the PowerPivot Management Dashboard.

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.

If you are using PowerPivot, the Central Administration content will grow over the span of one year, assuming that you use default settings that keep usage data collection and data refresh history for 365 days. For more information about PowerPivot for SharePoint, see Plan a PowerPivot Deployment in a SharePoint Farm (https://go.microsoft.com/fwlink/p/?LinkID=186698).

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 Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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.

Content databases also store user data for PowerPivot for SharePoint, if it has been installed in the environment.

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. For specific guidance about how to size content databases, see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010).

Associated Health rules

None

Supported backup mechanisms

SharePoint Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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.

If PowerPivot for SharePoint is in use, the Excel files stored in SharePoint Server grow larger, which increases the size of the content database. For more information, see Plan a PowerPivot Deployment in a SharePoint Farm (https://go.microsoft.com/fwlink/p/?LinkID=186698).

For detailed recommendations about how to calculate the size of a content database, see Storage and SQL Server capacity planning and configuration (SharePoint Server 2010).

Usage and Health Data Collection database

The Usage and Health Data Collection database is used by the Usage and Health Data Collection service application. It 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 Server 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.

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes. However, although you can replicate 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 replication using SQL Server AlwaysOn availability groups for disaster recovery

No

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 Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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 Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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 Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

SharePoint Server 2010, Standard Edition databases

The following databases are part of a SharePoint Server 2010, Standard Edition deployment.

Secure Store database

The Secure Store service application database stores and maps credentials, such as account names and passwords.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

Secure_Store_Service_DB_

Location requirements

For secure credential storage, we recommend that the secure store database be hosted on a separate database instance or database server that has access limited to one administrator. By default, if the database is hosted on the default SharePoint database server and instance, all database administrators will have access to the secure store database.

General size information, and growth factors

Medium. Size and growth are determined by the number of target applications, number of credential fields per target application, and the number of users stored in each target application.If auditing is turned on, the number of read/write operations performed against a given target application also affects size.

Read/write characteristics

Equal read/write ratio

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 Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

State database

The State service application database stores temporary state information for InfoPath Forms Services, the chart Web Part, and Visio Services.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

StateService

Location requirements

None

General size information, and growth factors

Medium-large. Size is determined by the use of InfoPath Forms services and Visio Services.

Read/write characteristics

Varies

Recommended scaling method

Scale out; add another state database to the service application by using Windows PowerShell cmdlets. For more information, see Manage the State Service (SharePoint Server 2010).

Associated Health rules

None

Supported backup mechanisms

SharePoint Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Web Analytics Staging database

The Staging database temporarily stores un-aggregated fact data, asset metadata, and queued batch data for the Web Analytics service application.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

WebAnalyticsServiceApplication_StagingDB_

Location requirements

None

General size information, and growth factors

Medium. Size varies based on the number of reports being generated

Read/write characteristics

Varies

Recommended scaling method

Scale out; associate another Web Analytics Staging database with the service application instance.

Associated SharePoint health rules

A rule checks whether the service broker queue is enabled for the Web Analytics databases.

Supported backup mechanisms

SharePoint Server 2010 backup and recovery, SQL Server, and DPM 2010.

Default recovery model

Full

Supports mirroring within a farm for availability

No

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Web Analytics Reporting database

The Reporting database stores aggregated standard report tables, fact data aggregated by groups of sites, date and asset metadata, and diagnostics information for the Web Analytics service application.

Important

For large-scale environments, we recommend that you run the Web Analytics service application Reporting database on a server that is running SQL Server 2008 Enterprise Edition so that the Web Analytics service application can take advantage of table partitioning.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

WebAnalyticsServiceApplication_ReportingDB_

Location requirements

None

General size information, and growth factors

Extra-large. Size varies based on retention policy.

Read/write characteristics

Varies

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

A rule checks whether the service broker queue is enabled for the Web Analytics databases.

Supported backup mechanisms

SharePoint Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Search service application Administration database

The Administration database hosts the Search service application configuration and access control list (ACL), and best bets for the crawl component. This database is accessed for every user and administrative action.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

Search_Service_Application_DB_

Location requirements

The Administration database should fit into RAM on the server so that the server can handle the end-user query load most efficiently. Because of this requirement, it is usually best not to have the Administration and Crawl databases located on the same server.

General size information, and growth factors

Small to medium. The factors that influence growth include the number of best bets, the number of content sources and crawl rules, the security descriptions for the corpus, and the amount of traffic.

Read/write characteristics

Approximately equal read/write ratio

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 Server 2010 backup and recovery.

Default recovery model

Simple

Supports mirroring within a farm for availability

Yes

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

No

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Search service application Crawl database

The Crawl database stores the state of the crawled data and the crawl history.

Important

For large-scale environments, we recommend that you run the Crawl database on a server that is running SQL Server 2008 Enterprise Edition so that the service application can take advantage of data compression.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

Search_Service_Application_CrawlStoreDB_

Location requirements

The Crawl database is very I/O intensive, and causes the SQL Server cache to be flushed regularly. In large-scale environments, we recommend that you locate this database on a server that does not contain the Property database or other databases involved in end-user tasks.

General size information, and growth factors

Medium to large. Generally, the Crawl database starts medium-sized and grows over time, without shrinking. The factors that influence growth are the number of items in the corpus.

Read/write characteristics

Read-heavy. The read/write ratio is 3:1.

Recommended scaling method

Scale out; associate another Crawl database with the service application instance. Multiple Crawl databases can be placed on the same server, if the server can handle the I/O per second required.

Associated Health rules

The Search - One or more crawl databases may have fragmented indices on-demand health rule defragments indices health rule rebuilds statistics, and — if the system is running SQL Server Enterprise Edition — turns on data compression at the page level.

Supported backup mechanisms

SharePoint Server 2010 backup and recovery.

Default recovery model

Simple

Supports mirroring within a farm for availability

Yes

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

No

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Search service application Property database

The Property database stores information that is associated with the crawled data, including properties, history, and crawl queues.

Important

For large-scale environments, we recommend that you run the Property database on a server that is running SQL Server 2008 Enterprise Edition so that the service application can take advantage of data compression.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

Search_Service_Application_PropertyStoreDB_

Location requirements

At least one-third of the Property database should fit into RAM on the server.

In large-scale environments, we recommend that you put this database on its own server to achieve faster query results.

General size information, and growth factors

Large to extra-large. Factors that influence growth are the number of managed properties and the number of documents.

Read/write characteristics

Write-heavy. The read/write ratio is 1:2.

Recommended scaling method

Scale out; associate another Property database with the service application instance. We recommend that you locate each additional Property database on a different server.

Associated Health rules

The Search - One or more property databases have fragmented indices health rule runs once a week by default. It defragments indices, rebuilds statistics, and — if the system is running SQL Server Enterprise Edition — turns on data compression.

Supported backup mechanisms

SharePoint Server 2010 backup and recovery.

Default recovery model

Simple

Supports mirroring within a farm for availability

Yes

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

No

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

User Profile service application Profile database

The Profile database stores and manages users and associated information. It also stores information about a user's social network in addition to memberships in distribution lists and sites.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

User Profile Service Application_ProfileDB_

Location requirements

None

General size information, and growth factors

Medium to large. Growth factors include additional users and the use of news feeds. News feeds grow with user activities. The default is to maintain the last two weeks of activity, after which a timer job deletes the news feed items older than two weeks.

Read/write characteristics

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 Server 2010 and SQL Server backup and recovery.

Default recovery model

Simple

Supports mirroring within a farm for availability

Yes

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes. An administrator can also use the User Profile Replication Engine, part of the SharePoint Administration Toolkit, to replicate user profiles and social data, such as social tags, notes, and ratings, between User Profile Service applications. This replication can be one-way or bidirectional. For more information, see User Profile Replication Engine overview (SharePoint Server 2010).

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

User Profile service application Synchronization database

The Synchronization database stores configuration and staging data for use when profile data is being synchronized with directory services such as Active Directory.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

User Profile Service Application_SyncDB_

Location requirements

General size information, and growth factors

Medium to large. Growth factors include the number of users and groups, and the ratio of users to groups.

Read/write characteristics

Approximately equal read/write ratio.

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 Server 2010 and 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, however, an administrator can use the User Profile Replication Engine, part of the SharePoint Administration Toolkit, to replicate user profiles and social data, such as social tags, notes, and ratings, between User Profile Service applications. This replication can be one-way or bidirectional. For more information, see User Profile Replication Engine overview (SharePoint Server 2010).

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

User Profile service application Social Tagging database

The Social Tagging database stores social tags and notes created by users, along with their respective URLs.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

User Profile Service Application_SocialDB_

Location requirements

None

General size information, and growth factors

Small to extra large. Growth factors include the number of tags, ratings and notes that have been created and used.

Read/write characteristics

Read-heavy. The read/write ratio is approximately 50:1.

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 Server 2010 and SQL Server backup and recovery.

Default recovery model

Simple

Supports mirroring within a farm for availability

Yes

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes. An administrator can also use the User Profile Replication Engine, part of the SharePoint Administration Toolkit, to replicate user profiles and social data, such as social tags, notes, and ratings, between User Profile Service applications. This replication can be one-way or bidirectional. For more information, see User Profile Replication Engine overview (SharePoint Server 2010).

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Managed Metadata database

The Managed Metadata service application database stores managed metadata and syndicated content types.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

Managed Metadata Service_

Location requirements

None

General size information, and growth factors

Medium. Growth factors include the amount of managed metadata.

Read/write characteristics

Read-heavy. The read/write ratio is approximately 1,000:1.

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 Server 2010 backup and recovery.

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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Word Automation Services database

The Word Automation Services database stores information about pending and completed document conversions.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

WordAutomationServices_

Location requirements

None

General size information, and growth factors

Small

Read/write characteristics

Varies

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 Server 2010 backup and recovery.

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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

SharePoint Server 2010, Enterprise Edition databases

The following databases are part of a SharePoint Server 2010, Enterprise Edition deployment, in addition to the databases that ship with the Standard Edition.

PerformancePoint service application database

The PerformancePoint service application database stores temporary objects, persisted filter values, and user comments.

Note

Microsoft SQL Server 2008 Analysis Services (SSAS) is also recommended as a data source for the PerformancePoint service application. If you have configured SQL Server Analysis Services and created cubes, additional functionality, such as drill-down analytics from published dashboards, becomes available.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

PerformancePoint Service Application_

Location requirements

None

General size information, and growth factors

Small. User comments and annotations are persisted indefinitely and increase based on the number of application users. Temporary items are removed once per day.

Read/write characteristics

Varies

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

The Database not available health rule verifies that the PerformancePoint database is available.

Supported backup mechanisms

SharePoint Server 2010 backup and recovery.

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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Project Server 2010 databases

Project Server 2010 requires SharePoint Server 2010, Enterprise Edition.

Draft database

The Draft database contains data for editing projects. This database also hosts the tables used by the Project Queue. Data in the Draft database is not directly accessible by end users.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

ProjectServer_Draft

Location requirements

Must be located on the same database instance as the Published and Archive databases.

General size information, and growth factors

Medium

Read/write characteristics

Equal read/write ratio.

Recommended scaling method

Scale up the database that supports the service application instance.

Associated Health rules

None

Supported backup mechanisms

SharePoint Server 2010 and SQL Server backup and recovery.

Default recovery model

Full

Supports mirroring within a farm for availability

Yes, but you must follow additional steps.

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes, but you must synchronize the logs of all of the databases.

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes, but you must follow additional steps.

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Published database

The Published database contains a copy of all of the projects that have been published. The Published database also contains tables that are specific to Project Server (timesheets, resources, custom fields, security definitions, and other metadata). This database also hosts the tables used by the Timesheet Queue. Data in the Published database is not directly accessible by end users.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

ProjectServer_Published

Location requirements

Must be located on the same database server as the Draft and Archive databases.

General size information, and growth factors

Medium

Read/write characteristics

Equal read/write ratio

Recommended scaling method

Scale up the database that supports the service application instance.

Associated Health rules

None

Supported backup mechanisms

SharePoint Server 2010 and SQL Server backup and recovery.

Default recovery model

Full

Supports mirroring within a farm for availability

Yes, but you must follow additional steps.

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes, but you must synchronize the logs of all of the databases.

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes, but you must follow additional steps.

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Archive database

The Archive database stores the backup data of projects, resources, calendars, enterprise custom fields, the enterprise global Project Web Access view definitions, Project Web Access system settings, and category and group security settings as set up by the Project Web Access administrator. Data in the Archive database is not directly accessible by end users.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

ProjectServer_Archive_

Location requirements

Must be located on the same database server as the Draft and Published databases.

General size information, and growth factors

Small, but can grow to extra large. If your system is using archiving functionality, this database will be at least as large as the Draft database, but is likely to be much larger. You can set a limit for the size of the Archive database to be no larger than a specified multiplier of the Draft database.

Read/write characteristics

Equal read/write ratio.

Recommended scaling method

Scale up the database that supports the service application instance.

Associated Health rules

None

Supported backup mechanisms

SharePoint Server 2010 and SQL Server backup and recovery.

Default recovery model

Full

Supports mirroring within a farm for availability

Yes, but you must follow additional steps.

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes, but you must synchronize the logs of all of the databases.

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

Yes, but you must follow additional steps.

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

Reporting database

The Reporting database is the repository for the entire portfolio of projects in Project Server. These tables present stable snapshots of each project plan based on the last time a project was published to Project Server and include de-normalized time phased data, allowing for advanced reporting capabilities outside of the Project client. Data in the Reporting database is accessible by end users.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

ProjectServer_Reporting

Location requirements

None

General size information, and growth factors

Large

Read/write characteristics

Read-heavy

Recommended scaling method

Scale up the database that supports the service application instance.

Associated Health rules

None

Supported backup mechanisms

SharePoint Server 2010 and SQL Server backup and recovery.

Default recovery model

Full

Supports mirroring within a farm for availability

Yes, but you must follow additional steps to ensure that the Project databases and logs are fully synchronized.

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

Yes, but you must follow additional steps to ensure that the Project databases and logs are fully synchronized.

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

FAST Search Server for SharePoint 2010 databases

FAST Search Server 2010 for SharePoint requires SharePoint Server 2010, Enterprise Edition.

Search Administration database

The Search Administration database stores and manages data related to administration of FAST Search Server 2010 for SharePoint. This includes search setting groups, keywords, synonyms, document and site promotions and demotions, term entity extractor inclusions and exclusions, spell check exclusions, best bets, visual best bets, and search schema metadata.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

FASTSearchAdminDatabase

Location requirements

None

General size information, and growth factors

Small. Growth and size are affected by the number of keywords, synonyms, document promotions and demotions, site promotions and demotions, term entity extractor inclusions, term entity extractor exclusions, spell checking exclusions, best bets, and visual best bets.

Read/write characteristics

Read-heavy

Recommended scaling method

Scale up the database. (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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

PowerPivot for SharePoint

PowerPivot for SharePoint extends SharePoint Server 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint Server 2010.

PowerPivot for SharePoint requires that SQL Server 2008 R2 Enterprise Edition Analysis Services be installed in the environment.

Note

The use of PowerPivot for SharePoint also influences the size of the Central Administration content database and the content databases in which the Excel workbooks are stored. For more information, see Plan a PowerPivot Deployment in a SharePoint Farm (https://go.microsoft.com/fwlink/p/?LinkID=186698).

PowerPivot Application database

The PowerPivot Application database stores the location of cached or loaded PowerPivot data files, data refresh schedules, and PowerPivot usage data that is copied from the central usage data collection database.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

DefaultPowerPivotServiceApplicationDB

Location requirements

None

General size information, and growth factors

Small

Read/write characteristics

Recommended scaling method

Scale up the database that supports the service application instance.

Associated Health rules

None. Rely on SQL Server health rules.

Supported backup mechanisms

SQL Server backup and recovery.

Default recovery model

Full

Supports mirroring within a farm for availability

Yes, but mirroring is managed solely through SQL Server: SharePoint Server 2010 is not aware of the PowerPivot database.

Supports asynchronous mirroring or log-shipping to another farm for disaster recovery

No

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

SQL Server system databases

SharePoint Server 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups 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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

SQL Server Reporting Services databases

The following SQL Server Reporting Services databases can be used as part of a SharePoint Server 2010 deployment.

Note

If your deployment requires the use of Access Services, the requirements for Reporting Services depend on the mode in which you are running, as follows:

  • Local mode requires only SharePoint Server 2010 and the SQL Server 2008 R2 Reporting Services (SSRS) Add-in.

  • Connected mode requires SharePoint Server 2010, the SSRS Add-in, and a SQL Server 2008 R2 Report Server, available in Standard or Enterprise Edition.

For more information about how to manage SQL Server Reporting Services databases, see Report Server Catalog Best Practices (https://go.microsoft.com/fwlink/p/?LinkID=185486).

ReportServer database

The SQL Server Reporting Services ReportServer database stores all report metadata including report definitions, report history and snapshots, and scheduling information.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

RSDB

Location requirements

Must be located on the same database server as the ReportServerTempDb database.

General size information, and growth factors

Small

Read/write characteristics

Read-heavy

Recommended scaling method

Scale up the database

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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

ReportServerTempDB database

The SQL Server Reporting Services ReportServerTempDB database stores all the temporary snapshots while reports are running.

Default database name prefix when installed by using the SharePoint Products Configuration Wizard

RSTempDB

Location requirements

Must be located on the same database server as the ReportServer database.

General size information, and growth factors

Small to extra large. The size of the ReportServerTempDB database goes both up and down quickly, depending on the number of concurrent interactive users, and the number of report snapshots.

Read/write characteristics

Read-heavy

Recommended scaling method

Scale up the database

Associated Health rules

None

Supported backup mechanisms

SQL Server backup and recovery, but we do not recommend that you back up this database.

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

Supports synchronous replication using SQL Server AlwaysOn availability groups for availability

No

Supports asynchronous replication using SQL Server AlwaysOn availability groups for disaster recovery

No

See Also

Other Resources

Resource Center: Capacity Management for SharePoint Server 2010
Resource Center: SQL Server and SharePoint Server 2010 Databases