Overview of SQL Server in a SharePoint environment (SharePoint 2013)
Applies to: SharePoint Server 2013 Enterprise, SharePoint Server 2013 Standard
Topic Last Modified: 2015-01-27
Summary: Learn about the SharePoint Server 2013 relationship with SQL Server 2008 R2 with Service Pack 1 (SP1), SQL Server 2012, and SQL Server 2014 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).
|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, or SQL Server 2014 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) or SQL Server 2014, 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), SQL Server 2012, and SQL Server 2014 are stored in relational databases. The following table shows the databases that SharePoint 2013 uses.
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 databases store all site content:
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.
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.
|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 2012 Reporting Services add-in or later on a SharePoint front-end web server.|
SQL Server 2008 R2 is the minimum version and is not supported when you use SQL Server 2012 Reporting Services or SQL Server 2014 Reporting Services.
For more information, see Supported Combinations of SharePoint and Reporting Services Components. The following levels of integration are provided when you run a report server in integrated mode with SharePoint Server 2013.
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|
Primary storage for the following data:
All report server configuration settings that you make in Central Administration including:
Internal copies of report content and metadata, which are also stored in the SharePoint content database, and the following report data:
Report server Temp
Temporary data, including the following:
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 and SQL Server 2014 provide business intelligence solutions for SharePoint Server 2013. The 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. For more information, see Features Supported by the Editions of SQL Server 2012 and Features Supported by the Editions of SQL Server 2014.
|SharePoint Foundation 2013 does not support SQL Server BI features.|
We recommend AlwaysOn Availability Groups for high availability in SQL Server 2012 Reporting Services and SQL Server 2014 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).
|We recommend that you use AlwaysOn Availability Groups instead of database mirroring for your high availability solution with SQL Server 2012 or SQL Server 2014 and SharePoint Server 2013. For more information, see Overview of SQL Server High-Availability Solutions.|
SharePoint mode in SQL Server 2012 Reporting Services and SQL Server 2014 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 Services Report Server (SharePoint Mode).
|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, Install SQL Server BI Features with SharePoint 2013 (SQL Server 2012 SP1), and Install SQL Server BI Features with SharePoint (PowerPivot and Reporting Services).
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 about SQL Server 2012 Analysis Services (SSAS), see Analysis Services. For more information about SQL Server 2014 Analysis Services, 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.