Deployment Topologies for SQL Server BI Features in SharePoint

Applies To: SQL Server 2016 Preview

This topic describes common topologies for installing the SQL Server Business Intelligence features Reporting Services and Power Pivot for SharePoint 2013 in SharePoint 2010 and SharePoint 2013 environments. For example single server and three tier installations.

In this topic:

• SharePoint 2013 Example Deployment Topologies

  • Power Pivot for SharePoint 2013 and Reporting Services Three Server Deployment

  • Power Pivot for SharePoint 2013 Single Server Deployment

  • Power Pivot for SharePoint 2013 Two Server Deployment

  • Power Pivot for SharePoint 2013 Three Server Deployment

  • Power Pivot for SharePoint 2013 and Reporting Services Single Server Deployment

  • Power Pivot for SharePoint 2013 and Reporting Services Two Server Deployment

• SharePoint 2010 Example Deployment Topologies

  • Single Server Deployments

  • Two-Tier Deployment

  • Three-Tier Deployment

  • Three-Tier Scale-Out Deployment

SharePoint 2013 Example Deployment Topologies

The SQL Server setup option Power Pivot for SharePoint has no dependencies on SharePoint. It does not use the SharePoint object model or interfaces to support integration. Therefore, Analysis Services can be installed on any computer running Windows Server 2008 R2 or later version. It can be but does not have to be an application server in a SharePoint farm. One of the configuration steps is to point Excel Services to the server running Analysis Services. For load balancing and fault tolerance, it is recommended to install and register multiple Analysis Services servers running in SharePoint mode.

Reporting Services SharePoint mode requires SharePoint server 2013 and utilizes the SharePoint Service Application architecture.

The following sections illustrate typical deployment topologies:

Power Pivot for SharePoint 2013 and Reporting Services Three Server Deployment

In the following three-server deployment, the SQL Server Database Engine, Analysis Services server running in SharePoint mode, and SharePoint, each run on a separate server. The Power Pivot for SharePoint 2013 2013 installer package (spPowerPivot.msi) must be run on the SharePoint server.

 Submit feedback and contact information through Microsoft SQL Server Connect (https://connect.microsoft.com/SQLServer/Feedback).

Power Pivot for SharePoint 2013 Single Server Deployment

A single server deployment is useful for testing purposes but it is not recommended for production deployments.

The following diagram illustrates the components that are part of a single server Analysis Services deployment.

Power Pivot for SharePoint 2013 Two Server Deployment

In the following two-server deployment, the SQL Server Database Engine and Analysis Services in SharePoint mode run on a separate server than SharePoint. For SharePoint 2013, the Microsoft SQL Server 2016 Power Pivot for SharePoint 2013 installer package (spPowerPivot.msi) is installed on the SharePoint server.

Power Pivot for SharePoint 2013 extends SharePoint Server 2013 to add server-side data refresh processing, data providers, Power Pivot Gallery, and management support for Power Pivot workbooks and Excel workbooks with advanced data models.

The installer package is available as part of the SQL Server 2016 feature pack. The feature pack can be downloaded from the Microsoft download center at Microsoft® SQL Server® 2014 Power Pivot® for Microsoft® SharePoint® ( HYPERLINK "https://go.microsoft.com/fwlink/?LinkID=296473" \t "_blank" https://go.microsoft.com/fwlink/?LinkID=296473).

Power Pivot for SharePoint 2013 Three Server Deployment

In the following three-server deployment, the SQL Server Database Engine, Analysis Services server running in SharePoint mode, and SharePoint, each run on a separate server. The Power Pivot for SharePoint 2013 installer package (spPowerPivot.msi) must be installed on the SharePoint server.

Power Pivot for SharePoint 2013 and Reporting Services Single Server Deployment

A single server deployment is useful for testing purposes but it is not recommended for production deployments.

Power Pivot for SharePoint 2013 and Reporting Services Two Server Deployment

In the following two-server deployment, the SQL Server Database Engine and Analysis Services server running in SharePoint mode run on a separate server from SharePoint. The Power Pivot for SharePoint 2013 installer package (spPowerPivot.msi) must be run on the SharePoint server.

SharePoint 2010 Example Deployment Topologies

The following diagram shows which services and providers run on each tier. Notice that the diagram includes several built-in services; these services are required for some SQL Server BI scenarios. Excel Services, Secure Store Services, and the Claims to Windows Token Service are either required by or recommended for a Power Pivot for SharePoint or Reporting Services deployment in SharePoint. Additionally, the MSOLAP OLE DB providers and ADO.NET Services are required for some Power Pivot data access scenarios. Optionally, you can install Analysis Services on the data tier, if you want to build Power View reports based on tabular model databases that are hosted outside of SharePoint.

Single Server Deployments

You can install all server components, including the data tier, on a single computer. This deployment configuration is useful if you are evaluating the software or developing custom applications that include Reporting Services in SharePoint mode. This deployment is the simplest to configure. Because all the components are installed on the same computer, it also uses the least amount of licenses. Reporting Services, Power Pivot for SharePoint, and the Database Engine are installed as a single licensed copy of SQL Server.

To install all features on a single server, you install Reporting Services and Power Pivot for SharePoint sequentially, on the same physical server. For instructions on a standalone server configuration, see Deployment Checklist: Reporting Services, Power View, and Power Pivot for SharePoint.

Two-Tier Deployment

A two-tier deployment is typically SharePoint Server 2010 on one computer and the SQL Server Database Engine on the second computer. Moving the data tier to a dedicated server is the most common configuration for a 2-computer farm. In a two-tier farm, you install both Reporting Services and Power Pivot for SharePoint on the SharePoint server. All web services on the front-end and shared services in the application tier run on the same physical server. Installation steps for a 2-tier deployment are very similar to a standalone deployment, in that you install Reporting Services and Power Pivot for SharePoint sequentially, on the same physical server.

Three-Tier Deployment

A three-tier deployment typically separates web front-end services from processing or memory-intensive applications. On this topology, you install Reporting Services and Power Pivot for SharePoint on just the application server. Web services that run on the web front-end are installed via solutions that are deployed to applications in the farm, during server configuration, as a post-installation task. The following diagram illustrates a 3-tier deployment.

Three-Tier Scale-Out Deployment

This topology illustrates a scale-out deployment that runs the same shared service on multiple servers, servicing a larger volume of requests and providing greater processing power for Power Pivot data or Reporting Services reports. In the diagram below, there are three application server clusters, each running a different combination of shared services. In a SharePoint environment, service discovery and availability is built into the farm. Load balancing across multiple physical servers running the same shared service application is part of the shared service architecture.

When deploying a multi-server farm, be sure to follow the instructions in this SharePoint article: Multiple Servers for a three-tier farm (SharePoint Server 2010).

See Also

Install Reporting Services SharePoint Mode
Install Analysis Services in Power Pivot Mode
Power Pivot for SharePoint 2010 Installation