Database Info

This is just a list of some of the Databases used in most SharePoint 2010 farms

Configuration Database (SharePoint_Config)

The configuration database is a small database (1GB or less) the transaction logs are however likely to grow quickly due to the amount of changes that take place. It needs to be read a lot but does not require to be written to as much. The database contains data about the SharePoint farm; it is what the entire farm relies on for general settings relating to databases, IIS web applications, site templates, web applications, default quotas etc.

Size information Small (1GB). The transaction logs are however likely to grow
Read/Write Read-heavy
Scaling Scale up. Only one Configuration database per farm is allowed.
Default recovery model Full. It’s recommended to set the configuration database to simple recovery to restrict the growth of the log file.

Central Administration Content (Central_Admin_Content)

The Central Administration Content is a small database (1GB or less) if you do however use Power Pivot it will grow more. It stores information about all site content, meaning all documents and files, list data, web part properties. It also host all information about user accounts, service accounts etc. It has varying read/write characteristics meaning that we cannot determine if it’s more read or write intensive.

Size information Small (1GB or less). If Power Pivot is used it will grow larger.
Read/Write Varies
Scaling Scale up. Only one Central admin allowed per farm
Default recovery mode Full

Content Databases (WSS_content)

It is strongly recommended to limit the size of the content databases to 200GB to ensure system performance. Its read/write characteristics will vary depending on the content hosted in the database. In a collaboration site it will be more write-intensive and in a document management environment it will be more read-intensive. In certain cases the content databases can be up to 1TB but only if the site is a repository that archives relatively static data (document centers etc)

Size information Recommended maximum (200GB)
Read/Write Varies
Scaling Any content database which is hosting a site collection must be scaled up as we cannot split a site collection across two databases. It is however recommended to create new site collections in the same web application with their own Databases as the recommended size of a database is 200GB. If a Content database is hosting multiple site collections it is recommended to move the site collections to their own databases.
Default recovery mode Full

Usage and Health Data Collection database (WSS_UsageApplication)

The usage and health database is used to store health monitoring and usage data temporary and is used

Location requirements This is a very active database that should be put on a separate disk if possible.
Size information Extra-large (1TB or more). The size depends on retention time and amount of objects being monitored.
Read/Write Very write heavy
Scaling To usage and health must scale up as only one logging database is allowed per farm.
Default recovery mode Simple

Business Data Connectivity database (BDC_Service_DB)

The BDC database stores external content types and related objects

Size information Small (1GB or less) depends on amount of connections.
Read/Write Very read-heavy
Scaling Must scale up, only one BDC allowed per farm
Default recovery mode Full

State Database (StateService)

The state database stores temporary state information for InfoPath, chart web parts and the Visio service.

Size information Medium-Large (100GB – 1TB). The size is determined by the use of InfoPath and Visio
Read/Write Varies
Scaling Scale out. Add additional state databases using PowerShell
Default recovery mode Full

Web Analytics Staging database

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

Size information Medium (100GB). The size is determined by the number of reports being generated.
Read/Write Varies
Scaling Scale out. Add additional staging databases with the service application instance.
Default recovery mode Full

Web Analytics Reporting database

The reporting database stores the aggregated report tables, fact data from group sites, date and asset metadata, and diagnostics information for the web analytics service.

Size information Extra-Large (1TB or more). The size is determined by the retention policy of the data.
Read/Write Varies
Scaling Scale out. Add additional staging databases with the service application instance.
Default recovery mode Full

Search Service Application Administration Database

This database hosts the search service application configuration and access control list (ACL) and the “best bets” for the crawl component. This database is accessed for every user and administrative action.

Location Requirements The administrative database should fit into the RAM on the server so that it can handle the end-user query load as efficiently as possible. The Administration and Crawl databases should not be located on the same server.
Size information Small – Medium (1GB – 100GB) The size is determined by the amount of best bets, the number of content sources and crawl rules, the security descriptions for the corpus and the amount of traffic.
Read/Write Equal
Scaling The database must be scaled up. Additional instances can be created but each instance can only host one database.
Default recovery mode Simple

Search Service Application Crawl Database

The crawl database stores the state of the crawled data and the crawl history. In large scale environments it is recommended to run the crawl database on a server that is uses SQL 2008 Enterprise edition so that the data compression can be used.

Location Requirements The Crawl database is very I/O intensive and causes the SQL cache to be flushed regularly. The crawl database should not be hosted on the same server as the databases involved in end-user tasks (Property database, content databases etc)
Size information Medum – Large (100GB – 1TB). The number of items in the Corpus determines the size of the database
Read/Write Read-Heavy ration Read 3:1 Write
Scaling Scale out. Associate another crawl database with the service application instance. Multiple Crawl databases can be placed on the same server as long as it can handle the I/O per second required.
Default recovery mode Simple

Search Service Application Property Database

The property database stores information that is associated with the crawled data, including properties, history and crawl queues. In larger deployments it’s recommended to use a SQL 2008 Enterprise server so that data compression can be used.

Location requirements At least one-third of the Property database should fit into the RAM on the Server. In large-scale deployments its recommended to this database is hosted on its own server to achieve faster query results.
Size information Large – Extra Large (1TB or more). The size is determined by the amount of managed properties and the number of documents.
Read/Write Write-heavy. The ration is Read 1:2 Write
Scaling Scale out. Connect another property database with the service application. Each additional property database should be hosted on a different server.
Default recovery model Simple

User Profile Service Application Profile Database

The profile database stores and manages all users and information associated with the users. It also stores information about the user’s social network and memberships for sites and lists.

Size information Medium – Large (100GB – 1TB) Determined by number of users, the use of news feed, retention time.
Read/Write Read-heavy
Scaling Scale up. Additional instances of the service application can be created but generally because of business not size.
Default recovery model Simple

User Profile Service Application Synchronization Database

The database stores the configuration and staging data for use when profile data is being synchronized with the active directory.

Size information Medium – Large (100GB – 1TB). Grows with more users and groups.
Read/Write Equal
Scaling Scale up. Additional instances of the service application can be created but generally because of business not size.
Default recovery model Simple

User Profile Service Application Social Tagging database

The social tagging database stores social tags and notes created by users, as well as their urls.

Size information Small – Large (1GB – 1TB). Grows when additional tags, ratings and notes are created and used.
Read/Write Read-heavy. The ratio is Read 50:1 Write
Scaling Scale up. Additional instances of the service application can be created but generally because of business not size.
Default recovery model Simple

Managed Metadata database

The managed metadata database stores the managed metadata and the syndicated content types.

Size information Medium (100GB). The amount of managed metadata determines the size
Read/Write Read-Heavy. The ration is Read 1000:1 Write
Scaling Scale up. Additional instances of the service application can be created but generally because of business not size.
Default recovery model Simple
Advertisements

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: