• Configuration Database

The configuration database handles all administration of the deployment, directing requests to the appropriate database, and managing load-balancing for the back-end databases. When a front-end Web server receives a request for a page in a particular site, it checks the configuration database to determine which content database holds the site's data. You can run the configuration database on the same computer as a Web server or on a remote computer running Microsoft SQL Server. The configuration database concepts are relatively unchanged in Microsoft SharePoint Server 2010. In MOSS 2007 and Windows SharePoint Services 3.0 the Site Map table was stored in the configuration database which provided information about which content database contains data for a given site. When Windows SharePoint Services or Microsoft Office SharePoint Server 2007 received the URL of a request, settings in this database determine which content database contains data for the site. In Microsoft SharePoint Server 2010 and Windows SharePoint Services 4.0 the Site Map is serialized to disk to improve performance and reduce database callback operations that could result in contention when serving requests on large server farm deployments.

• Content Database

The back-end content database stores all site content, including site documents or files in document libraries, list data, and Web Part properties, as well as user names and rights. All the data for a specific site resides in one content database on only one computer.

• Central Administration Content Database

As the Content Database.

• Shared Services Provider (SSP)

The Shared Services Provider layer is obsolete in Microsoft SharePoint Server 2010 replaced with Shared Services Applications, to understand changes in database design associated with the Shared Services Provider database, see also Search and People in this section.

NOTE

MOSS 2007 SSPs when upgraded will result in a new Search, User Profile, Excel Services, Application Registry Backwards Compatibility, and Managed Metadata Service shared services applications. New databases will be created as required to support the upgrade and Web application settings are preserved through establishing a proxy for each service application.

• Usage and Health Data Collection Service

The Usage and Health Data Collection Service collects and logs SharePoint health indicators and usage metrics for analysis and reporting purposes.

Logging Database

The logging database is the Microsoft SQL Server, MSDE, or WMSDE database that stores health monitoring and usage data temporarily, and can be used for reporting and diagnostics.

• Search Service

Administration Database

The Administration Database is what the Shared Services Provider database was in Microsoft Office SharePoint Server 2007 and is instantiated once per Search application aligning with the Administration Component. The Administration Database hosts the Search application configuration and access control list (ACL) for the content crawl.

Property Database

The Property Database stores crawled properties associated with the crawled data to include properties, history data, crawl queues, etc.

Crawl Database

The Crawl Databases host the crawled data and drives crawl - the Crawl Database is what the Search database was in Microsoft Office SharePoint Server 2007.

• Web Analytics Service

The Web Analytics Service provides rich analytics giving you insights into your web traffic, search, and SharePoint assets enabling you to better understand your user and deployments. With SharePoint Web Analytics, you’ll be able to tailor the system to meet the needs of your users, optimize how they use and discover information, and create targeted content for your sites.

Staging Database

The Web Analytics Staging database is the working database that stores un-aggregated Fact Data, asset metadata, queued batch data, and provides short term retention of this content.

Reporting Database

The Web Analytics Reporting database stores aggregated standard report tables, Fact Data aggregated by Site Group, date, and asset metadata in addition to diagnostics information.

• User Profile Service

The User Profile Service encompasses user profiles and My Sites.

Profile Database

The user profile database is a flexible database that stores and manages user and associated information. The database allows for a flexible schema that supports multiple data types. It can be queried and it can be updated. For example, a company can define the attributes of an employee record in the profile database. Then for each record, an employee object will be created and saved. This information is now usable in a number of ways, such as in Web Parts, in the Web service, or to create rule based groups or roles.

1. Properties

1. Profiles
2. Multiple values
3. Vocabularies

1. Colleagues

1. Memberships

1. Change Log

Synchronization Database

The synchronization database is used to store configuration and staging data for synchronization of profile data from external sources such as Active Directory.

Social Tagging Database

The social tagging database stores social tagging records and their respective Url which are coupled with information from the profile and taxonomy databases at the front-end layer at execution/request. This database is used to store social tags and notes created by users.

• Managed Metadata Service

The Managed Metadata Service publishes a term store and, optionally, a set of content types.

Term Store Database

A database in which managed metadata is stored. The Web front end public APIs interact with the data layer to get or set data. The data layer talks to the term store directly if the shared service is local to the farm, or it talks to a backend Web service on an application server if the shared service is not local. The backend Web service then interacts with the data layer on the application server to get to the term store.

• State Service

The State Service maintains temporary state information for InfoPath Forms Services.

State Database

The state database maintains temporary state information for InfoPath Forms Services.

• Business Data Connectivity Service

The Business Database Connectivity Service provides a means for storing, securing, and administering external content types and related objects.

Database

Stores external content types and related objects.

• Secure Store Service

The Secure Store Service replaces the Single Sign On Service in previous versions of the product.

This service provides storage and mapping of credentials such as account names and passwords. Portal site–based applications can retrieve information from third-party applications and back-end systems such as Enterprise Resource Planning (ERP) and Customer Relations Management (CRM) systems.

The use of Secure Store functionality enables users to authenticate without asking the user multiple times for the credentials needed to authenticate in that system.

Store Database

Provides storage and mapping of credentials such as account names and passwords.

You can define a database name for every content database. This effectively allows SharePoint 2010 will automatically be aware of database mirroring. If the primary SQL server dies, SharePoint will automatically re-connect to the failover database.

For example; you have content database SQLDB1 and failover database SQLDB2. By default SharePoint will communicate with SQLDB1. But in case SQLDB1 dies or SharePoint unable to communicate with SQLDB1 it will automatically retry connection to failover database and start communication with SQLDB2. SQLDB2 is a mirror to the SQLDB1. The default timeout to connect to failover database is 15 seconds.

It will retry automatically, so you have no need to worry about any manual process to change the database.

However, You will have to configure the database mirroring independent of SharePoint. SharePoint does not configure the mirroring in SQL for you. SharePoint will however verify the instance you specify is valid. The values for the failover instances for the databases an be set through the Central Admin UI except for the Configuration database which needs to done through PowerShell.

To make SharePoint Server 2010 aware that failover mirrored databases exist, perform the following procedure for all configuration and content databases.

Note :  It is recommended to use Windows PowerShell cmdlets to set failover database values. Although you can use the Central Administration Web site to set some failover database values, you cannot use

   it for all databases like the Configuration Database.

To configure SharePoint 2010 Products to be aware of mirrored databases by using Windows PowerShell

1. Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
2. On the Start menu, click All Programs.
3. Click Microsoft SharePoint 2010 Products.
4. Click SharePoint 2010 Management Shell.
5. At the Windows PowerShell command prompt, type the following commands, and then press ENTER

$db = get-spdatabase | where {$_.Name -eq "database name "}
$db.AddFailoverServiceInstance("mirror server")
$db.Update()

For more information, see Get-SPDatabase.

After running these cmdlets when you look at the connection strings for the databases, they will look something like following

DatabaseConnectionString :  Data Source=<primary SQL instance>;Failover Partner=<mirror SQL instance>;
Initial Catalog=<SharePoint_Config>;Integrated Security=True;Enlist=False;Connect Timeout=15

LegacyDatabaseConnectionString  : =<primary SQL instance>;Database=<SharePoint_Config>;Trusted_Connection=yes;
App=Microsoft SharePoint Foundation;Timeout=15;Failover Partner=<mirror SQL instance>

Test the fail over capability

Test the automatic failover capability of the mirror by shutting down the principal server. You will notice that the mirror server will become the principal server while continuing to host database operations. Power on the principal server. Any database changes made on the mirror server will be restored to the principal server.

Next, shut down the mirror server so the principal server can once again become the primary server. Power on the mirror server.

To configure a failover instance for all databases (except for the Logging database):

Get-SPDatabase | ? {$_.Type -ne “Microsoft.SharePoint.Administration.SPUsageDatabase”} | ? {!$_.FailoverServiceInstance} | ForEach ($_) {$_.AddFailoverServiceInstance(“SERVERNAME\INSTANCENAME”); $_.Update()}

The supported topologies include mirroring all content databases, the configuration database, the Central Administration content database, and the service application databases except for the Web Analytics Staging database and the User Profile Synchronization database.

Note:
We do not recommend that you mirror the Usage and Health Data Collection Logging database. A SharePoint environment can continue to run if this database fails, and this data can be quickly regenerated.

Avoid topologies that do not have matching principal server and database instances and mirror server and database instances. Also, keep the configuration database and the administration content database on the same server.

The information about which databases would be better to mirror is available at http://technet.microsoft.com/en-us/library/ff628961.aspx

Also, you can run the following command to get list of all databases and failover instances (if set)

Get-SPDatabase | SELECT Farm, Name, Type, FailoverServer | Sort-Object Name

Alternatively, you can export the output to a csv file using the following command:

Get-SPDatabase | SELECT Farm, Name, Type, FailoverServer | Sort-Object Name | Export-CSV Database_List_$env:computername.csv

New Databases

SharePoint Shared Services became deprecated in SharePoint 2010, and instead we have new Services architecture, where all services are independent from each other. Such architecture affected the way information and configuration are stored and each services has it's own database.

Previously, with SharePoint 2007 we had at least 6 databases for each installation

1. Central Administration - [SharePoint_AdminContent<GUID>]
2. Configuration - [SharePoint_Config]
3. SSP - 3 databases for SSP settings, MySites, and Search
4. Web Application - custom database

With SharePoint 2010 model we still have Central Administration and other databases, but instead of point 3 we end up with the separate database for each Service, for example [Access Services], [Metadata Services], [WebAnalytics Reporting] and etc.

User Profiles use 3 databases: [Profiles] - for the actual profile content, [Synchronization] to keep configurations of the external data and [Social Tagging] to store tags and noted created by users.

This model describes the SQL Server databases that are integral to SharePoint 2010 Products. The databases used in a specific environment are determined by the product, version, edition, and features that are running.

Databases that support SharePoint 2010 Products

Will try to cover some additional details regarding the databases used in SharePoint 2010 in upcoming posts.