Introduction to the Microsoft SharePoint SharePoint 2010 Database Layer [UPDATED]

Microsoft SharePoint Server 2010 introduces both new databases and databases whose distribution and purpose differs over previous versions of Microsoft SharePoint Products and Technologies. This post details the changes in the Microsoft SharePoint Server 2010 database layer. This section provides information about Shared Service Applications that have a database dependency and is not an exhaustive list of all Shared Service Applications available in Microsoft SharePoint Server 2010.  At the time of publication this is not 100% complete.

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.

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 Microsoft Office SharePoint Server 2007 and Windows SharePoint Services 3.0 the SiteMap 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 SiteMap 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

See also 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

A Microsoft Office SharePoint Server 2007 Shared Services Provider when upgraded will result in a new Search, User Profile, Excel Services, Application Registry Backwards Compatability, 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.

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 WebParts, in the Web service, or to create rule based groups or roles.

  1. Properties
  2. Profiles
  3. Multiple values
  4. Vocabularies
  5. Colleagues
  6. Memberships
  7. 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.

Step-by-Step: Provisioning the Search Service Application

Contents

Provisioning the Search Service Application

Moving Query Components

Creating Mirror Query Components

Creating Query Components

Creating Crawl Components

Creating Crawl Databases

Creating Property Databases

Provisioning the Search Service Application

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

Select New | Search Service Application on the ribbon user interface.

CA

On the Create Search Service Application dialog specify the name for the new Search Service Application or accept the default name, usually Search Service Application 1.

Provide a name for the new Search Administration Web Service Application Pool or use an existing Application Pool.

Provide a name for the new Search Administration Site Settings and Query Web Service or use an existing Application Pool.

CA2

Click OK on the new Create New Search Service Application dialog to provision the new service application

Once the Search Service Application has been successfully provisioned on the server farm you will have a 1x1x1 topology or otherwise 1 Search Administration, 1 Crawl, and 1 Query component on the machine hosting SharePoint 2010 Central Administration and all associated databases on the default database server.

Topology

NOTES

The Search administration (Admin) topology does not scale out – there can be on one (1) search administration component and one (1) search administration database per Search Service Application.

The Crawl topology can be scaled out by adding Crawl Components or Crawl Databases.  Crawl Components can have a many-to-one relationships with Crawl Databases.

The Query topology can be scaled out by adding Property Databases or by adding Query Components.  Index Partitions subdivide the full-text index.   A new Query Component can either be the first component in a new partition (see above illustration (Query Component 0)) or an additional component in an existing partition.

In the public beta, Index Partitions have a many-to-one relationship with Property Databases.

Moving Query Components

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

On the Services Applications page, select the Search Service Application.

On the Search Administration page, locate the Search Application Topology section and click Modify.

On the Topology for Search Service Application: Search Service Application page, locate the Index Partition category. (The default Query Component is typically named Query Component 0). Click Query Component 0 and then click Edit Properties.

On the Edit Query Component page, select a server in the topology from the Server drop-down list and then click OK.  This will move the Query Component to the selected server.

EditQueryComponent

Creating Mirror Query Components

When you create a Mirror Query Component, you create a replica of the Index Partition on another server.  You will typically create new Mirror Query Components when you need to increase throughput or availability.

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

On the Services Applications page, select the Search Service Application.

On the Search Administration page, locate the Search Application Topology section and click Modify.

On the Topology for Search Service Application: Search Service Application page, locate the Index Partition category. (The default Query Component is typically named Query Component 0). Click Query Component 0 and then click Add Mirror.

AddMIrror

On the Add mirror query component dialog, select a server in the topology from the Server drop-down list and then click OK.

AddMirrorComponent 

Repeat the steps for each server in the topology as required.

Creating Query Components

When you create a new Query Component, you create a new Index Partition which subdivides the full-text index.  You will typically create new Query Components and Index Partitions when the total number of items in your Index exceed the recommend scale for a single Index Partition, or when you need to increase throughput or availability.

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

On the Services Applications page, select the Search Service Application.

On the Search Administration page, locate the Search Application Topology section and click Modify.

On the Topology for Search Service Application:  Search Service Application 1, select New | Index Partition and Query Component.

Topology2

On the Add Query Component dialog, select a server in the topology from the Server drop-down list, Property Database, and specify the location of the Index Partition.

AddQueryComponent

Click OK on the Add Query Component dialog to save the changes and create the new Query Component.

Creating Crawl Components

You will typically create new Crawl Components to improve the overall crawl speed and subsequently freshness of the content and to improve availability.

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

On the Services Applications page, select the Search Service Application.

On the Search Administration page, locate the Search Application Topology section and click Modify.

On the Topology for Search Service Application:  Search Service Application 1, select New | Crawl Component.

Topology2

On the Add Crawl Component dialog specify the server where the Crawl Component will be hosted, the Crawl Database to which the Crawl Component will be associated, and the temporary location on the Index.

AddCrawlComponent

Click OK on the Add Crawl Component dialog to save the changes and create the new Crawl Component.

Creating Crawl Databases

You will typically create new Crawl Databases to improve the overall crawl speed and subsequently freshness of the content and in correlation to the creation of new Crawl Components.

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

On the Services Applications page, select the Search Service Application.

On the Search Administration page, locate the Search Application Topology section and click Modify.

On the Topology for Search Service Application:  Search Service Application 1, select New | Crawl Database.

Topology2

On the Add Crawl Database dialog specify the database server where the Crawl Database will reside, the database name, and optionally the select whether the Crawl Database will be dedicated to hosts specified in Host Distribution Rules.

Host Distribution Rules are useful in specifying:

1. A particular host that is processed by a one or more Crawler Databases.
2. A particular host is processed by only one or more Crawler Database.

Host Distribution Rules are commonly used to support large and complex content corpuses that require horizontal scale (scale out) topologies.

AddCrawlDB

Click OK on the Add Crawl Database dialog to save the changes and create the new Crawl Database.

Creating Property Databases

You will typically create new Property Databases to support the horizontal scale (scale out) of the Query Component(s).

Open SharePoint 2010 Central Administration.

Select Managed service applications under Application Management.

On the Services Applications page, select the Search Service Application.

On the Search Administration page, locate the Search Application Topology section and click Modify.

On the Topology for Search Service Application:  Search Service Application 1, select New | Property Database.

Topology2

On the Add Property Database dialog specify the database server where the Property Database will reside and the database name.

AddPropertyDatabase

Click OK on the Add Property Database dialog to save the changes and create the new Property Database.

Step-by-Step: Provisioning the Web Analytics Service Application on Microsoft SharePoint Server 2010 Beta

Prerequisites

Usage and Health Data Collection and the Session State Service Application have been provisioned on the farm.

Provisioning the Web Analytics Service Application

Open SharePoint 2010 Central Administration.

Select Manage service applications under Application Management.

Select New | Web Analytics Service Application on the ribbon user interface.

CA

On the Create Web Analytics Service Application dialog specify the name for the new Web Analytics Service Application or accept the default name, usually Web Analytics Service Application 1.

Provide a name for the new Application Pool.

Provide the name of the default database server where the Web Analytics reporting and staging databases will be hosted and specify the desired retention period.

A2_thumb

Click OK on the new Create Web Analytics Service Application dialog to provision the new service application.

On a single server deployment select System Settings from SharePoint 2010 Central Administration and then click Services on Server.

From the list of available services start the Web Analytics Data Processing Service and Web Analytics Web Service. 

Untitled picture

Data is logged into .usage files on the front-end Web servers where it is processed into the staging database created in the previous steps through the Timer Job infrastructure.  The data in the staging database is retained for 30 days and transitioned into the reporting database for longer term retention as specified in the retention period when the service application was created.  The information is subsequently surfaced through a variety of Web Parts by the Web Analytics Web Service.

NOTE

If you have installed Microsoft SharePoint Server 2010 in a server farm environment with one or more application servers start the Web Analytics Data Processing Service and Web Analytics Web Service on the application server where the service will run.

Following the successful completion of the steps above you will having a running instance of the Web Analytics Service Application.

To confirm Web Analytics is running, select Monitoring from SharePoint 2010 Central Administration and then select View Web Analytics reports under Reporting.  You should see a blue bar with the text Date Range 10/21/2009 – <current date -1> (UTC <time zone specifics> Change Settings.

Welcome to the Developer Dashboard

About the Developer Dashboard

The Developer Dashboard is an instrumentation framework  new to Microsoft SharePoint Foundation and Server 2010 that can help diagnose particularly, classes of bugs that are easy to introduce through custom code, but often difficult to isolate by providing information about the request execution time, the number and callstack of each SPRequest allocation, the number, callstack, and query text of WCF calls and more.

The Developer Dashboard appears in a frame on the bottom of each page and can exist in one of three (3) possible modes – On, Off, OnDemand.  When the Developer Dashboard is in the ‘On’ mode, it is always displayed in a frame on the bottom of the page for each request, conversely, when the Developer Dashboard is in the ‘Off’ mode, it is not displayed, and finally when the Developer Dashboard is in the ‘OnDemand’ mode, it can be displayed or hidden by selecting an icon on the upper right corner of a page.  (see illustrations).

Developer Dashboard

DeveloperDashboard

OnDemand Mode Icon

ShowHideMe 

Using the Developer Dashboard

The Developer Dashboard makes it easy for IT professionals and developers to identify common issues, for example, if a value exceeds acceptable ranges it will be displayed in red and by hovering your mouse over any value you can receive additional information about that value or how common methods to resolve it depending on the situation.

For developers you can monitor any piece of code by wrapping it in the SPMonitoredScope or even create custom monitors for your own resources through implementing  ISPScopedPerformanceMonitor and adding the monitor to the SPMonitoredScope.

Enabling the Developer Dashboard

The Developer Dashboard can be enabled and disabled through the SharePoint Administration Tool (STSADM) or through Windows PowerShell.  The following examples illustrate each method:

STSADM

‘On’ Mode

STSADM –o setproperty –pn developer-dashboard –pv On

‘Off’ Mode

STSADM –o setproperty –pn developer-dashboard –pv Off

‘OnDemand’ Mode

STSADM –o setproperty –pn developer-dashboard –pv OnDemand

But wait there’s more…

Suppose you’d like to only display the developer dashboard if one or more counters (acceptable values) are exceeded, there’s a way to do that too by running:

STSADM –o setproperty –pn developer-dashboard –pv expensiveoperationsonly

Windows PowerShell

See below.

Scripted (Windows PowerShell)

Optionally you can script the configuration of the Developer Dashboard – to do so copy the following script and save it to somefile.ps1.

Param ([String]$mode)

function Main()
{
  $dashboard = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.DeveloperDashboardSettings;
  $dashboard.DisplayLevel = $mode;
  $dashboard.RequiredPermissions =’EmptyMask’;
  $dashboard.TraceEnabled = $true;
  $dashboard.Update()

  Write-Host "Configured Developer Dashboard with mode $mode."
}

Open the Microsoft SharePoint 2010 Shell and change directories to where you saved somefile.ps1 and run ./somefile.ps1 OnDemand (or optionally On or Off).

Installation notice for the SharePoint Server Public Beta on Microsoft Windows Server 2008 R2 and Microsoft Windows 7

If you will be installing the SharePoint Server Public Beta on Microsoft Windows Server 2008 R2 or Microsoft Windows 7 you will need to download and install an update from http://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=23806  to resolve an issue that occurs in Microsoft SharePoint Server 2010 when provisioning Service Applications or when accessing pages that make service calls.  These operations will result in an error "System.Configuration.ConfigurationErrorsException: Unrecognized attribute ‘allowInsecureTransport’. Note that attribute names are case-sensitive. (C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14WebClients<Service Area>client.config line <Line Number>)". 

If you have already installed Microsoft SharePoint Server 2010 on a server running Microsoft Windows Server 2008 R2 or Microsoft Windows 7, Microsoft SharePoint Server 2010 does not need to be reinstalled when the update becomes available; however, Service Applications that have been successfully provisioned without the update installed may need to be removed and re-provisioned once the update has been successfully applied.

Bill Baer, Technical Product Manager – US-SharePoint