Overview of SQL Server in a SharePoint Server 2016 environment

Summary: Learn about the SharePoint Server 2016 relationship with SQL Server and how you can interact with the databases.

The minimum requirements for a database server in a SharePoint Server 2016 farm are as follows:

  • 64-bit edition of Microsoft SQL Server 2014 with Service Pack 1 (SP1)

  • Microsoft SQL Server 2016

  • Microsoft SQL Server 2017 RTM

Note

SQL Server Express is not supported with SharePoint Server 2016.

Note

SQL Server 2017 on Linux is not supported with SharePoint Server 2016.

Depending on the installed version, you can use specific features of SQL Server, such as reporting and business intelligence (BI). For more information, see Hardware and software requirements for SharePoint Server 2016.

SharePoint Server 2016 supports the following:

  • SQL Server 2016 Reporting Services (SSRS)

  • SQL Server 2016 Analysis Services (SSAS)

Note

If you want to use Microsoft SQL Server Power Pivot for SharePoint or Microsoft Power View for SharePoint for BI solutions you must install the Power Pivot or Power View add-ins for SQL Server 2016 RTM. The SQL Server 2014 (SP1) Power Pivot for SharePoint and Power View for SharePoint BI solutions do not work with SharePoint Server 2016.

SharePoint Server 2016 and the SQL Server database engine

The SharePoint Server 2016 application is built on the SQL Server database engine. Most content and settings in SQL Server 2014 (SP1), SQL Server 2016, and SQL Server 2017 RTM are stored in relational databases. The following table shows the databases that SharePoint Server 2016 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
Data and objects for Project Server 2016
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 Server 2016, see Database types and descriptions in SharePoint Server. For a graphical representation of the databases that support SharePoint Server 2016, see Quick reference guide: SharePoint Server 2016 databases. You can also download this SharePoint Server 2016 database poster, as either a PDF or Visio file.

Working with the SQL Server databases that support SharePoint Server 2016

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

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

The SQL Server databases that support SharePoint Server 2016 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).

SQL Server 2014 with Service Pack 1 (SP1)

SQL Server 2014 (SP1) provides greater performance, availability, and manageability with SharePoint Server 2016 than SQL Server 2014. While you can't use SQL Server Power Pivot for SharePoint or Power View for SharePoint with SQL Server 2014 (SP1), you can use some business intelligence solutions with SharePoint Server 2016. For example, you can install Office Online Server to use Excel Online.

For more information, see Features Supported by the Editions of SQL Server 2014. For detailed information about Office Online Server, see Configure Office Online Server for SharePoint Server 2016.

High Availability Solutions

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

Note

We recommend that you use AlwaysOn Availability Groups instead of database mirroring for your high availability solution with SQL Server 2014 (SP1), SQL Server 2016, and SQL Server 2017 RTM for SharePoint Server 2016. 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).For information about high availability for SQL Server Reporting Services, see High Availability (Reporting Services).

Log Shipping

SQL Server Log shipping provides a disaster recovery solution for single primary databases and multiple secondary databases where each are located on separate instances of SQL Server. Log shipping backs up the transaction log on the production server, copies the log to the backup or secondary instances, and is then available to restore the log backup. You can then configure alerts to notify you when the production server fails. Then you can fail over from the production server to the backup servers so if the production server fails one of the backup or secondary servers can be brought online to act as the production server. For more information, see About Log Shipping (SQL Server).

Reporting Services SharePoint mode

When you setup Reporting Services with SharePoint Server 2016 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.

For more information, see Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016). The following levels of integration are provided when you run a report server in integrated mode with SharePoint Server 2016.

  • 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

SharePoint mode in SQL Server 2016 is a SharePoint shared service that you configure in either the SharePoint Central Administration website or by using Reporting Services SharePoint mode, Microsoft PowerShell cmdlets. SharePoint mode supports SharePoint Server 2016 backup and restore for SQL Server Reporting Services service application and Unified Logging Service (ULS) trace logs. SharePoint mode also supports claims-based authentication.

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 (SharePoint Mode), Reporting Services Report Server, and PowerShell cmdlets for Reporting Services SharePoint Mode.

SQL Server 2016

SQL Server 2016 provides business intelligence solutions for SharePoint Server 2016. The SharePoint mode of SQL Server 2016 provides features for SQL Server Analysis Services and SQL Server Reporting Services. For more information, see Features Supported by the Editions of SQL Server 2016.

When you install SQL Server 2016 Analysis Services (SSAS) and SQL Server 2016 Reporting Services (SSRS) in a SharePoint Server 2016 farm the following business intelligence solutions are available:

  • SQL Server 2016 Power Pivot

  • SQL Server 2016 Power View

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

The following SharePoint Server 2016 business intelligence features are available when you upgrade to SQL Server 2016 RTM:

  • Power Pivot Gallery

  • Scheduled Data Refresh

  • Workbooks as a Data Source

  • Power Pivot Management Dashboard

  • Power View reports

  • Power View Subscriptions

  • Report Alerting

For more information, download the new Deploying SQL Server 2016 PowerPivot and Power View in SharePoint 2016 white paper. For details about configuring and deploying business intelligence in a multiple server SharePoint Server 2016 farm, download Deploying SQL Server 2016 PowerPivot and Power View in a Multi-Tier SharePoint 2016 Farm.

For more information, see Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016) and Install SQL Server 2016 Business Intelligence Features.

Power Pivot for SharePoint

SQL Server 2016 RTM is required to deploy Power Pivot for SharePoint 2016. Power Pivot for SharePoint 2016 is an add-in that is available in the SQL Server 2016 RTM Feature Pack. SQL Server 2016 Analysis Services must be run in SharePoint mode. This provides a server that hosts Power Pivot data in a SharePoint farm. For more information, see Install Analysis Services in Power Pivot Mode. The server that hosts Power Pivot for SharePoint 2016 can be outside a SharePoint Server 2016 farm.

SQL Server 2016 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 2016 Analysis Services, see Analysis Services.

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

Power Pivot for SharePoint Configuration method Description
Power Pivot for SharePoint 2016 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.
SharePoint Server 2016 Central Administration
Central Administration provides the SQL Server Power Pivot Service Application that you create to access the Power Pivot Management Dashboard for your BI farm.
Microsoft PowerShell cmdlets
Provides cmdlets that you can use to build PowerShell script files (.ps1) and automate the configuration process for Power Pivot for SharePoint.

Power View for SharePoint

Power View is a feature included with Microsoft SQL Server 2016 Reporting Services Add-in for Microsoft SharePoint. Install SQL Server 2016 Reporting Services Add-in for SharePoint, and then configure the servers for integration. When you deploy Power View for SharePoint you can create and interact with views of data from data models that are based on Power Pivot workbooks that are published in a Power Pivot Gallery, or tabular models that are deployed to SSAS. You can also create and view reports from SSRS on SharePoint document libraries. All Power View reports provide multiple views that feature tiles, slicers, chart filters, and visualizations. For more information, see What's New in Reporting Services (SSRS).

See also

Other Resources

Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016)

What's New in SQL Server 2016

Deprecated and Discontinued SQL Server Features in SQL Server 2016

What's New (Analysis Services)

Analysis Services

Features Supported by the Editions of SQL Server 2014

Deprecated Database Engine Features in SQL Server 2014