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