SharePoint Databases Knowledge Base

1 Mar 2015 15-minute read Al Eardley
On-PremData Platform
SharePointSQL Server

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

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

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

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

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

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

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

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

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

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

SharePoint - 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
No specific requirements
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 DB

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

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

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

  • 5MB per 1000 credentials
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-SPSecureStoreServiceApplication

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

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

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

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

  • 1MB per profile
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-SPProfileServiceApplication

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

  • 0.009MB per tag
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-SPProfileServiceApplication

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

  • 630KB per user
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-SPProfileServiceApplication

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

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

References

The information that has been used to create this knowledge base can be found in the following pages:

Microsoft Products – References

SharePoint Databases – References

Table of Contents


Comment on this post: