Understanding SharePoint and SQL
Installing SharePoint results in the creation of a number of databases in the SQL Server, as follows:
• Configuration database (SharePoint 2010 Foundation)—An important database generally called SharePoint_Config. This database stores and manages data associated to all the SharePoint databases in the farm, IIS websites, trusted solutions, WSP packages, site templates, and web application and farm settings. This database is generally small in size.
The configuration database should reside on the same SQL server instance as the Central Administration database because they have a strong dependency on each other.
• Central administration content database (SharePoint 2010 Foundation)—The content database from for the Central Administration website. The name of the database will be in the form SharePoint_AdminContent_GUID.
Only one instance of the Central Administration content database and configuration database will be created per farm.
• Content database (SharePoint 2010 Foundation)—Stores all the content stored in SharePoint websites, including list configuration and data; files/documents from SharePoint libraries, including the ones stored in the web part galleries, master page galleries, and so on; web part properties; audit logs; sandboxed solutions; and so on. It also stores data for Office web applications (Excel, Access, OneNote, InfoPath, and so on). These databases start with WSS_Content.
• Usage database (SharePoint 2010 Foundation)—Supports the new Usage and Health Data Collection Service Application service.
• Business data connectivity (SharePoint 2010 Foundation)—Supports BCS services. It stores the external content types and associated metadata. These databases follow the naming convention of Bdc_Service_DB_GUID.
The business data connectivity database stores only the external content types and associated metadata and not the actual data from the external systems.
• Application registry database (SharePoint 2010 Foundation)—Stores data required to support backward compatibility for Business Data Connectivity (BDC) from SharePoint 2007. This database is used only during the upgrade process and can actually be deleted after the upgrade is complete. This database follows the naming convention of Application_Registry_Service_DB_GUID.
• Subscription settings database (SharePoint 2010 Foundation)—Supports the Subscription Settings Service. This database is used to support the new partitioning feature for SharePoint 2010.
• Search administration database (SharePoint 2010 Standard)—Used by the SharePoint 2010 Search service. It stores the configuration information associated to search and Access Control List (ACL), which is used for securing content that is indexed.
• Crawl database (SharePoint 2010 Standard)—Also used by the SharePoint 2010 Search service. This database stores the state of the crawled data and the crawl history.
The crawl database can grow to be very large based on the amount of content that you are indexing.
• Property database (SharePoint 2010 Standard)—Another database used to support SharePoint 2010 Search service. This database stores information associated to crawled data (that is, properties, history, and crawl queries).
• Web analytics staging database (SharePoint 2010 Standard)—Stores temporary usage data collected from the usage database. The data comes to this database in an unaggregated format and the Web Analytics Service takes this data, processes it, aggregates it, and then sends it to the web analytics reporting database. This database is cleaned out every 24 hours but is then refilled with new data that has been collected.
• Web analytics reporting database (SharePoint 2010 Standard)—A new database for SharePoint 2010 used to support the Web Analytics Service. This database stores all the aggregated analytics data collected across the SharePoint 2010 farm. This is the database the usage reports run against, and there is only one of these databases per farm. This database can grow to become very large relative to the amount of data stored in the entire farm. This database only has analytics data; it does not have any actual data from the content databases. By default, data is stored in here for up to 25 months.
• State database (SharePoint 2010 Standard)—The state service is used to support storing temporary data across HTTP requests. This database is utilized by InfoPath Form Services, Visio Services, Exchange, Chart Web Part, and so on. The space required for this database is driven by the services that use this database. Multiple state databases can be added through PowerShell commands.
• Profile database (SharePoint 2010 Standard)—Used by the User Profile service to store profile data. This database will not become very big and the size is based on the amount of data stored about each user. The database needs to support heavy read operations to get user data that is accessed commonly (user permissions are not stored here; they are in the content database).
• Synchronization database (SharePoint 2010 Standard)—Another database used by the User Profile service. Its purpose is to store the configuration of the service that brings user profile data into SharePoint. It is also used to stage data being synchronized from directory services like Active Directory. The size of this database is relative to the number of users and groups being synchronized. This database needs to support both heavy reading and writing when the synchronization service is running.
• Social tagging database (SharePoint 2010 Standard)—Used by the User Profile service for storing social tags and notes created by users for content in SharePoint.
• Managed Metadata Service database (SharePoint 2010 Standard)—Database used by the new Managed Metadata Service.
• Secure store database (SharePoint 2010 Standard)—Used by the secure store service of SharePoint 2010. This is the new SharePoint 2010 service to support Single Sign-On. It stores user credentials and passwords.
• Word automation services database (SharePoint 2010 Enterprise)—This database is used by the Word Automation service.
• PerformancePoint database (SharePoint 2010 Enterprise)—Used to support PerformancePoint. It stores temporary objects and settings needed to support dashboards.
• FAST Search administration database (SharePoint 2010 FAST)—Stores information for FAST Search. FAST Search is a new search technology for SharePoint 2010. This is in addition to the existing SharePoint Search technologies.