Export (0) Print
Expand All

Overview of SQL Server in a SharePoint environment (SharePoint 2013)

SharePoint 2013
 

Applies to: SharePoint Server 2013 Standard, SharePoint Server 2013 Enterprise

Topic Last Modified: 2014-04-29

Summary: Learn about the SharePoint Server 2013 relationship with SQL Server 2008 R2 with Service Pack 1 (SP1) and SQL Server 2012 and how you can interact with the databases.

SharePoint 2013 supports several versions of SQL Server. Depending on the installed version, you can use specific features of SQL Server, such as reporting and business intelligence (BI).

NoteNote:
SharePoint Foundation 2013 does not support BI features, which require SharePoint Server 2013.

The minimum requirements for a database server in SharePoint 2013 are SQL Server 2008 R2 with Service Pack 1 (SP1) or SQL Server 2012, 64-bit versions. Note that to use the business intelligence (BI) tools in SharePoint Server 2013 you must install SQL Server 2012 with Service Pack 1 (SP1), 64-bit version. For more information, see Hardware and software requirements for SharePoint 2013 and Software requirements for business intelligence in SharePoint Server 2013.

In this article:

The SharePoint 2013 application is built on the SQL Server database engine. Most content and settings in SQL Server 2008 R2 with Service Pack 1 (SP1) and SQL Server 2012 are stored in relational databases. The following table shows the databases that SharePoint 2013 uses.

 

Database type Description

Configuration

The Configuration database and Central Administration content database are called configuration databases. They contain data about farm settings such as the databases that are used, Internet Information Services (IIS) web sites or web applications, solutions, Web Part packages, site templates, default quota, and blocked file types. A farm can only have one set of configuration databases.

Content

Content databases store all site content:

  • Site documents, such as files in document libraries

  • List data

  • Web Part properties

  • Data for apps for SharePoint

  • User names and permissions

Each web application can contain many content databases. Each site collection can be associated with only one content database, although a content database can be associated with many site collections.

Service application

Databases for service applications store the data that service applications use.

For a full list of all of the databases that support SharePoint 2013, see Database types and descriptions (SharePoint 2013). For a graphical representation of the databases that support SharePoint 2013, see Databases that support SharePoint 20113.

The databases that support SharePoint Server 2013 are either created automatically with the SharePoint Products Configuration Wizard or by database administrators when they manually configure SharePoint Server.

Microsoft does not support directly querying or modifying the databases that support SharePoint Server 2013. In SharePoint Server 2013 the Usage and Health Data Collection database does support schema modifications.

The SQL Server databases that support SharePoint Server 2013 are subject to sizing limitations and to configuration recommendations that are not standard for SQL Server. For more information, see Storage and SQL Server capacity planning and configuration (SharePoint Server 2013).

SQL Server 2008 R2 introduced Power Pivot for SharePoint and Power Pivot for Excel 2010 for SharePoint business intelligence capability through integration with SharePoint Server 2010. Both SQL Server Analysis Services and SQL Server Reporting Services can run in the same SharePoint Server 2013 farm. SQL Server 2008 R2 with Service Pack 1 (SP1) introduced various new features and fixed many SQL Server 2008 R2 issues. For more information, see “1.0 What's New in Service Pack 1” in Microsoft SQL Server 2008 R2 SP1 Release Notes.

SQL Server 2008 R2 Reporting Services supports two types of SharePoint integration. Full integration relies on the SharePoint integrated mode. Partial integration relies on two Web Parts, Report Explorer and Report Viewer, which you must install on a SharePoint site and point to a remote report server instance. For more information, see Overview of Reporting Services and SharePoint Technology Integration and Planning for SharePoint Integration.

NoteNote:
Reporting Services supports SharePoint integrated mode using SharePoint Server 2013 only.

When you setup Reporting Services with SharePoint Server 2013 you create a report server. The report server is the central component of Reporting Services. This component contains two processing engines and a set of unique extensions that handle authentication, data processing, rendering, and delivery operations. When you configure a report server to run with SharePoint Server 2013 in integrated mode you must install the SQL Server 2008 R2 Reporting Services add-in on a SharePoint front-end web server. The following levels of integration are provided when you run a report server in integrated mode with SharePoint Server 2013.

  • Shared storage

  • Shared security

  • Same site access for all business documents such as reports, report models, and shared data sources

When Reporting Services runs in SharePoint integrated mode, both the SharePoint content and report server databases store content and metadata. The following table shows the report server data that each database stores.

 

Name of database Report server data

SharePoint content

Primary storage for the following data:

  • Published reports

  • Report models

  • Shared data sources

  • Resources

  • Properties

  • Permissions

SharePoint configuration

All report server configuration settings that you make in Central Administration including:

  • Report server URL

  • Report server Reporting Services account information

  • Information about the authentication provider that is used on the server

  • Site-level settings that limit or enable report history and logging

Report server

Internal copies of report content and metadata, which are also stored in the SharePoint content database, and the following report data:

  • Schedules

  • Subscriptions

  • Snapshots for report history or report execution

Report server Temp

Temporary data, including the following:

  • Session data

  • Temporary snapshots created for subscription processing, interactive reporting, or report caching as a performance improvement

Reporting Services data alerts are available to inform recipients of changes in report data.

For more information, see Storing and Synchronizing Report Server Content With SharePoint Databases

SQL Server 2012 with SP1 provide business intelligence solutions for SharePoint Server 2013. The new SharePoint mode of SQL Server 2012 provides features for SQL Server Analysis Services and SQL Server Reporting Services. In addition, the SharePoint mode provides SQL Server BI features in SharePoint Server 2013.

NoteNote:
SharePoint Foundation 2013 does not support SQL Server BI features.

We recommend AlwaysOn Availability Groups for high availability in SQL Server 2012 Reporting Services. Other high availability solutions are AlwaysOn Failover Cluster Instances, database mirroring, and log shipping. Both AlwaysOn Availability Groups and Failover Cluster Instances solutions require and use Windows Server Failover Clustering (WSFC).

NoteNote:
We recommend that you use AlwaysOn Availability Groups instead of database mirroring for your high availability solution with SQL Server 2012 and SharePoint Server 2013. For more information, see Overview of SQL Server High-Availability Solutions.

For more information, see AlwaysOn Availability Groups (SQL Server), and Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

SharePoint mode in SQL Server 2012 Reporting Services is a SharePoint 2013 shared service that you configure in either the SharePoint Central Administration website or by using Reporting Services SharePoint mode Windows PowerShell cmdlets. For more information, see PowerShell cmdlets (Reporting Services SharePoint Mode). SharePoint mode supports SharePoint 2013 backup and restore for SQL Server Reporting Services service application and Unified Logging Service (ULS) trace logs. SharePoint mode also supports claims-based authentication. For more information, see the "SharePoint Mode" section of What's New (Reporting Services). For more information about the SharePoint Windows PowerShell cmdlets for ULS, see Logging and events cmdlets in SharePoint 2013.

SharePoint mode requires that a report server component of Reporting Services must run within a SharePoint Server farm. This means that a SharePoint application server must exist with the Reporting Services shared service installed and at least one Reporting Services service application.

For more information, see Reporting Services Report Server (SSRS) and Reporting Service Report Server (SharePoint Mode).

NoteNote:
SharePoint Foundation 2013 does not support BI features, which require SharePoint Server 2013.

When you install SQL Server 2012 Analysis Services (SSAS) and SQL Server 2012 Reporting Services (SSRS) in a SharePoint Server 2013 farm the following business intelligence features are enabled:

  • SQL Server 2012 Power Pivot for SharePoint 2013

  • Power View for SharePoint 2013

  • Reporting Services interactive report designer that runs on Power Pivot or Analysis Services tabular data models

The xVelocity in-memory analytics engine in SQL Server 2012 supports both self-service BI and corporate BI. For more information, see xVelocity in SQL Server 2012.

For more information, see Plan for business intelligence in SharePoint Server 2013, Guidance for Using SQL Server BI Features in a SharePoint Farm, and Install SQL Server BI Features with SharePoint 2013 (SQL Server 2012 SP1).

SQL Server 2012 with SP1 is required to deploy Power Pivot for SharePoint 2013. Power Pivot for SharePoint 2013 is a SharePoint Server service application that becomes available when Analysis Services runs in SharePoint mode. This provides a server that hosts Power Pivot data in a SharePoint farm. SQL Server 2012 Analysis Services provides three modes for analysis, Multidimensional, Tabular, and Power Pivot for SharePoint. Note that each server mode is independent of the others, and each supports a type of analytical database that only runs in that modality. For more information, see Analysis Services. The server that hosts Power Pivot for SharePoint 2013 can be outside a SharePoint 2013 farm.

To configure Power Pivot for SharePoint you can use the SharePoint Central Administration website, the Power Pivot for SharePoint 2013 Configuration tool, or Windows PowerShell cmdlets. The following table lists each method and describes the process:

 

Power Pivot for SharePoint Configuration method Description

SharePoint 2013 Central Administration

Provides all available options to configure the Power Pivot for SharePoint service application.

Power Pivot for SharePoint 2013 Configuration Tool

Evaluates an existing installation and determines what needs to be configured in the SharePoint farm and Power Pivot for SharePoint and then configures everything required.

Windows PowerShell cmdlets

Provides cmdlets that you can use to build Windows PowerShell script files (.ps1) and automate the configuration process for Power Pivot for SharePoint.

The Power Pivot for SharePoint 2013 add-in enables PowerPivot Gallery, Schedule Data Refresh, and the PowerPivot Management Dashboard in Central Administration. For more information, see PowerPivot for SharePoint (SSAS) and Software requirements for business intelligence in SharePoint Server 2013.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft