SharePoint Databases Knowledge Base
SharePoint depends on databases for everything: without a healthy SQL Server installation, SharePoint will not work at all. As part of it’s dependence on SQL Server, SharePoint creates multiple databases when it is installed and when new service applications are created, more databases are created and finally, SharePoint Administrator’s can create new databases to store content in.
The characteristics of these databases vary quite dramatically, based on many different criteria including the following:
- Location and speed of storage within SQL Server
- Usage of the SharePoint farm
- Approach taken towards High Availability and Disaster Recovery for SharePoint
- Approach taken towards High Availability and Disaster Recovery for SQL Server
A SharePoint farm is not an isolated entity, it is an ecosystem of different products related to infrastructure and software and as a result, there are databases required for multiple different elements. This list of databases relates to the following products:
- SharePoint 2013
- SQL Server
- SQL Server Reporting Services
- Project Server 2013
- Workflow Manager
- Office Web Apps
Each of the Databases has a page that provides information specific to the database.
I have collated the guidance that Microsoft provide from both SQL and SharePoint perspectives to have a page per database that provides the following information:
- Description of the purpose of the database
- Guidance for sizing and growth
- Disaster and High Availability recommendations
- Links to PowerShell for creating the databases with tidy names
- Other information useful to planning the size of a database
| Product | Service Application | Default DB Name |
|---|---|---|
| SharePoint | SharePoint_Config | |
| SharePoint | SharePoint_AdminContent_<GUID> | |
| SharePoint | WSS_Content | |
| SharePoint | App Management | AppManagement |
| SharePoint | Business Connectivity Services | Bdc_Service_DB_<GUID> |
| SharePoint | Managed Metadata Service | Managed Metadata Service Application_Metadata_<GUID> |
| SharePoint | PerformancePoint Services | PerformancePoint Service _<GUID> |
| SharePoint | PowerPivot | DefaultPowerPivotServiceApplicationDB_<GUID> |
| SharePoint | Project Server | ProjectWebApp |
| SharePoint | Search | Search_Service_Application_DB_<GUID> |
| SharePoint | Search | Search_Service_Application_ AnalyticsReportingStoreDB_<GUID> |
| SharePoint | Search | Search_Service_Application_CrawlStoreDB_<GUID> |
| SharePoint | Search | Search_Service_Application_LinkStoreDB_<GUID> |
| SharePoint | Secure Store | Secure_Store_Service_DB_<GUID> |
| SharePoint | State Service | SessionStateService_<GUID> |
| SharePoint | Subscription Settings Service | SettingsServiceDB |
| SharePoint | SharePoint Translation Services | SharePoint Translation Services_<GUID> |
| SharePoint | User Profile Service | User Profile Service Application_ProfileDB_<GUID> |
| SharePoint | User Profile Service | User Profile Service Application_SocialDB_<GUID> |
| SharePoint | User Profile Service | User Profile Service Application_SyncDB_<GUID> |
| SharePoint | Usage and Health | SharePoint_Logging |
| SharePoint | Word Automation Services | WordAutomationServices_<GUID> |
| Workflow Manager | Service Bus | SB_Gateway |
| Workflow Manager | Service Bus | SB_Management |
| Workflow Manager | Service Bus | SB_MessageContainer01 |
| Workflow Manager | WFM_InstanceManagement | |
| Workflow Manager | WFM_Management | |
| Workflow Manager | WFM_ResourceManagement | |
| SQL Server | Reporting Services | ReportingService_<GUID>_TempDB |
| SQL Server | Reporting Services | ReportingService_<GUID> |
| SQL Server | Reporting Services | ReportingService_<GUID>_Alerting |
| SQL Server | Master | |
| SQL Server | MSDB | |
| SQL Server | Model | |
| SQL Server | TempDB | |
| SQL Server | Resource |
Reference Data
SharePoint - Config
Associations
- Application: SharePoint
Description
The configuration database contains data about the following: - SharePoint databases - Internet Information Services (IIS) web sites - Web applications - Trusted solutions - Web Part packages - Site templates - Web applications - The configuration database also contains specific data for SharePoint 2013 farm settings, such as default quota settings and blocked file types.
Details
| Default name | SharePoint_Config |
| Recommended name | SP_[ENV]_Config |
| Location | Must be co-located with the Central Administration database |
| Size | Small |
| Initial size | 2GB |
| Growth | Log files grow rapidly |
| Read/Write | 90:10 |
| Scaling approach | Up |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
PoSh Creation
New-SPConfigurationDatabaseSharePoint - Admin Content
Associations
- Application: SharePoint
Description
The Central Administration content database is considered to be a configuration database. It stores all configuration data for the Central Administration site collection. If SQL Server 2012 PowerPivot for SharePoint 2013 is installed, the Central Administration content database also stores the Excel worksheets and PowerPivot data files used in the PowerPivot Management Dashboard.
Details
| Default name | SharePoint_AdminContent_«GUID» |
| Recommended name | SP_[ENV]_Content_CentralAdmin |
| Location | Must be co-located with the Config database |
| Size | Small |
| Initial size | 1GB |
| Growth | Will grow if PowerPivot for SharePoint is installed with default settings |
| Read/Write | 90:10 |
| Scaling approach | Up |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
PoSh Creation
New-SPConfigurationDatabaseSharePoint - Content
Associations
- Application: SharePoint
Description
Content databases store all content for a site collection. This includes 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 of the files that are stored for a specific site collection are located in one content database on only one server. A content database can be associated with more than one site collection. Content databases also store user data for PowerPivot for SharePoint, if you installed it in your SharePoint Server 2013 environment.
Details
| Default name | WSS_Content |
| Recommended name |
SP_[ENV]Content[Web App or Site Collection Name] The name should be be able to convey details of the content |
| Location | No specific requirements |
| Size | < 200GB (The size depends on the amount of content) |
| Initial size | Should be pre-sized to eliminate autogrowth |
| Growth | The growth depends on the amount of usage |
| Read/Write | The ratio depends on the type of usage |
| Scaling approach | Up or out if the 200GB limit is expected to be reached |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
Additional Notes
Estimated size = ((DV)S) + (10KB(L+(VD)))
- D = Number of documents
- V = Average number of versions (minimum of 1)
- S = Average document size
- L = Estimated number of list items (3*D is a good starting point)
PoSh Creation
New-SPContentDatabaseSharePoint - App Management
Associations
- Application: SharePoint
- Service Application: App Management
Description
The App Management database is used by the App Management Service application. It stores the app licenses and permissions that are downloaded from the SharePoint Store or App Catalog.
Details
| Default name | AppManagement |
| Recommended name | SP_[ENV]_SA_AppManagement |
| Location | No specific requirements |
| Size | Small |
| Initial size | |
| Growth | Slow |
| Read/Write | 80:20 |
| Scaling approach | None |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPAppManagementServiceApplicationSharePoint - Business Connectivity
Associations
- Application: SharePoint
- Service Application: Business Connectivity Services
Description
The Business Data Connectivity service application database stores external content types and related objects.
Details
| Default name | Bdc_Service_DB_«GUID» |
| Recommended name | SP_[ENV]_SA_BusinessConnectivity |
| Location | No specific requirements |
| Size | Small |
| Initial size | |
| Growth | Very slow |
| Read/Write | 90:10 |
| Scaling approach | Up |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPBusinessDataCatalogServiceApplicationSharePoint - Managed Metadata
Associations
- Application: SharePoint
- Service Application: Managed Metadata Services
Description
The Managed Metadata service application database stores managed metadata and syndicated content types.
Details
| Default name | Managed Metadata Service Application_Metadata_«GUID» |
| Recommended name | SP_[ENV]_SA_ManagedMetada |
| Location | No specific requirements |
| Size | Medium |
| Initial size | |
| Growth | Growth factors include the amount of managed metadata |
| Read/Write | 80:20 |
| Scaling approach | Up per service application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPMetadataServiceApplicationSharePoint - PerformancePoint
Associations
- Application: SharePoint
- Service Application: PerformancePoint Services
Description
The PerformancePoint Services database stores temporary objects and persisted user comments and settings.
Details
| Default name | PerformancePointService_«GUID» |
| Recommended name | SP_[ENV]_SA_PerformancePoint |
| Location | No specific requirements |
| Size | Medium |
| Initial size | 1GB |
| Growth | Very slow |
| Read/Write | 80:20 |
| Scaling approach | Up per service application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPPerformancePointServiceApplicationSharePoint - PowerPivot
Associations
- Application: SharePoint
- Service Application: PowerPivot
Description
The PowerPivot Service database stores data refresh schedules, and PowerPivot usage data that is copied from the central usage data collection database. NOTES:
- PowerPivot Stores content in the Central Admin and other Content databases - SQL Server 2012 Analysis Services is required (BI or Enterprise edition)
Details
| Default name | DefaultPowerPivotServiceApplicationDB_«GUID» |
| Recommended name | SP_[ENV]_SA_PowerPivot |
| Location | No specific requirements |
| Size | Small |
| Initial size | |
| Growth | Very slow |
| Read/Write | 80:20 |
| Scaling approach | Up |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes, recommended |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Not tested |
PoSh Creation
The installation of PowerPivot is more complicated than using a single PoSh commandSharePoint - Project Server
Associations
- Application: SharePoint
- Service Application: Project Server
Description
Project Server creates a separate database for each instance of Project Web App. Each Project Web App database contains the following data:
- All Project and Portfolio Management (PPM) data - Time tracking and Timesheet data - Aggregated SharePoint project site data
Details
| Default name | ProjectWebApp |
| Recommended name | SP_[ENV]_SA_ProjectWebApp |
| Location | No specific requirements |
| Size | Small to medium |
| Initial size | |
| Growth | Slow |
| Read/Write | 80:20 |
| Scaling approach | Up per web application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
The installation of Project Server is more complicated than using a single PoSh commandSharePoint - Search (Admin)
Associations
- Application: SharePoint
- Service Application: Search
Description
The Search Administration database hosts the Search application configuration and system access control list (SACL) for the crawl component.
Details
| Default name | Search_Service_Application_DB_«GUID» |
| Recommended name | SP_[ENV]_SA_Search_Admin |
| Location | 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. |
| Size | Medium |
| Initial size | 10GB |
| Growth | 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 how much traffic. |
| Read/Write | 50:50 |
| Scaling approach | Mostly up |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
Additional Notes
- If 10M items are crawled the data will be about 0.4GB in size and the log about 1GB
- If 100M items are crawled the data will be about 1GB in size and the log about 2GB
PoSh Creation
New-SPEnterpriseSearchServiceApplicationSharePoint - Search (Analytics)
Associations
- Application: SharePoint
- Service Application: Search
Description
The Analytics Reporting database stores the results for usage analysis reports and extracts information from the Link database when needed.
Details
| Default name | Search_Service_Application_AnalyticsReportingStoreDB_«GUID» |
| Recommended name | SP_[ENV]_SA_Search_AnalyticsReportingStore |
| Location |
|
| Size | Medium to large |
| Initial size | |
| Growth | Write-intensive during analytics update |
| Read/Write | 90:10 |
| Scaling approach | Scale out by creating additional Analytics Reporting database using a split operation when the main database becomes > 200 GB. |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
PoSh Creation
There is no PoSh for creating a correctly named DB, however the name can be changed at a SQL level and the search application re-targeted at the renamed DBSharePoint - Search (Crawl Store)
Associations
- Application: SharePoint
- Service Application: Search
Description
The Crawl database stores the state of the crawled data and the crawl history.
Details
| Default name | Search_Service_Application_CrawlStoreDB_«GUID» |
| Recommended name | SP_[ENV]_SA_Search_CrawlStore |
| Location | No specific requirements |
| Size | Medium |
| Initial size | |
| Growth | Depends on the growth of the document corpus |
| Read/Write | 90:10 |
| Scaling approach | Scale out by creating additional Crawl database per every 20 million items crawled. |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
Additional Notes
- If 10M items are crawled the data will be about 15GB in size and the log about 2GB
- If 100M items are crawled the data will be about 110GB in size and the log about 50GB
PoSh Creation
New-SPEnterpriseSearchCrawlDatabaseSharePoint - Search (Link Store)
Associations
- Application: SharePoint
- Service Application: Search
Description
The Link database stores the information that is extracted by the content processing component and the click through information.
Details
| Default name | Search_Service_Application_LinkStoreDB_«GUID» |
| Recommended name | SP_[ENV]_SA_Search_LinkStore |
| Location | We recommend that if you have sites that have heavy traffic, the Link database should use separate spindles from other databases |
| Size | Medium to large |
| Initial size | |
| Growth | The Link database grows on disk by 1 GB per 1 million documents fed. The click through data grows linearly with query traffic, 1 GB per million queries. |
| Read/Write | 30:70 |
| Scaling approach | Scale out by creating additional Link database per every 60 million documents crawled. Also add additional Link database per 100 million expected queries per year |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
Additional Notes
- If 10M items are crawled the data will be about 10GB in size and the log about 0.1GB
- If 100M items are crawled the data will be about 80GB in size and the log about 5GB
PoSh Creation
New-SPEnterpriseSearchLinksDatabaseSharePoint - Secure Store
Associations
- Application: SharePoint
- Service Application: Secure Store
Description
The Secure Store Service application database stores and maps credentials, such as account names and passwords.
Details
| Default name | Secure_Store_Service_DB_«GUID» |
| Recommended name | SP_[ENV]_SA_SecureStore |
| Location | A secure database instance |
| Size | Small |
| Initial size | |
| Growth |
Slow, depends on number of target applications, number of credential fields per target application, and the number of users stored in each target application.
|
| Read/Write | 50:50 |
| Scaling approach | Up |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPSecureStoreServiceApplicationSharePoint - State Service
Associations
- Application: SharePoint
- Service Application: State Service
Description
The State Service database stores temporary state information for InfoPath Forms Services, Exchange Server, the chart Web Part, and Visio Services
Details
| Default name | SessionStateService_«GUID» |
| Recommended name | SP_[ENV]_SA_SessionState |
| Location | No specific requirements |
| Size | Medium to large |
| Initial size | 1GB |
| Growth | Depends on usage of other services |
| Read/Write | 80:20 |
| Scaling approach | Depends on usage of other services |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
PoSh Creation
New-SPStateServiceApplicationSharePoint - Subscription Settings
Associations
- Application: SharePoint
- Service Application: Subscription Settings Service
Description
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 or SQL Server.
Details
| Default name | SettingsServiceDB |
| Recommended name | SP_[ENV]_SA_SubscriptionSettings |
| Location | No specific requirements |
| Size | Small |
| Initial size | |
| Growth | Very slow |
| Read/Write | 90:10 |
| Scaling approach | Up per service application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPSubscriptionSettingsServiceApplicationSharePoint - Translation Services
Associations
- Application: SharePoint
- Service Application: Translation Service
Description
The Machine Translation Services stores information about pending and completed batch document translations with file extensions that are enabled.
Details
| Default name | SharePoint_Translation_Service_«GUID» |
| Recommended name | SP_[ENV]_SA_Translation |
| Location | No specific requirements |
| Size | Medium |
| Initial size | |
| Growth | Slow |
| Read/Write | 80:20 |
| Scaling approach | Up per service application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPTranslationServiceApplicationSharePoint - User Profile Service (Profiles)
Associations
- Application: SharePoint
- Service Application: User Profile Service
Description
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.
Details
| Default name | User_Profile_Service_Application_ProfileDB_«GUID» |
| Recommended name | SP_[ENV]_SA_UPS_Profile |
| Location | No specific requirements |
| Size | Medium to large |
| Initial size | |
| Growth |
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 time job deletes the news feed items older than two weeks.
|
| Read/Write | 90:10 |
| Scaling approach | Up per service application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPProfileServiceApplicationSharePoint - User Profile Service (Social)
Associations
- Application: SharePoint
- Service Application: User Profile Service
Description
The Social Tagging database stores social tags and notes created by users, alongside their respective URLs.
Details
| Default name | User_Profile_Service_Application_SocialDB_«GUID» |
| Recommended name | SP_[ENV]_SA_UPS_Social |
| Location | No specific requirements |
| Size | Small to extra large |
| Initial size | |
| Growth |
Growth factors include the number of tags, ratings, and notes that have been created and used.
|
| Read/Write | 80:20 |
| Scaling approach | Up per service application |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPProfileServiceApplicationSharePoint - User Profile Service (Sync)
Associations
- Application: SharePoint
- Service Application: User Profile Service
Description
The Synchronization database stores configuration and staging data for use when profile data is being synchronized with directory services such as Active Directory.
Details
| Default name | User_Profile_Service_Application_SyncDB_«GUID» |
| Recommended name | SP_[ENV]_SA_UPS_Sync |
| Location | No specific requirements |
| Size | Medium to large |
| Initial size | |
| Growth |
Growth factors include the number of users, groups, and the ratio of users to groups.
|
| Read/Write | 50:50 |
| Scaling approach | Up per service application |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
PoSh Creation
New-SPProfileServiceApplicationSharePoint - Usage and Health
Associations
- Application: SharePoint
- Service Application: Usage and Health
Description
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.
Details
| Default name | SharePoint_Logging |
| Recommended name | SP_[ENV]_SA_UsageHealth |
| Location | Very active, therefore dedicated spindle |
| Size | Extra large |
| Initial size | |
| Growth | 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 | 80:20 |
| Scaling approach | Up |
| Default recovery model | Simple |
| AlwaysOn AG synchronous for HA | Yes, but not recommended |
| AlwaysOn AG synchronous for DR | No |
| DC to DC failover | No |
PoSh Creation
New-SPUsageApplicationSharePoint - Word Automation
Associations
- Application: SharePoint
- Service Application: Word Automation Service
Description
The Word Automation Services database stores information about pending and completed document conversions and updates. The Word Automation Services Timer Job processes and distributes this information as queued conversion job items to application servers.
Details
| Default name | WordAutomationServices_«GUID» |
| Recommended name | SP_[ENV]_SA_WordAutomation |
| Location | No specific requirements |
| Size | Small |
| Initial size | 1GB |
| Growth | Slow |
| Read/Write | 80:20 |
| Scaling approach | Up per service application |
| Default recovery model | Full |
| AlwaysOn AG synchronous for HA | Yes |
| AlwaysOn AG synchronous for DR | Yes |
| DC to DC failover | Yes |
PoSh Creation
New-SPWordConversionServiceApplicationReferences
The information that has been used to create this knowledge base can be found in the following pages: