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.
Applies to: SharePoint 2013 | SharePoint 2010 |
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.
.jpeg "SSAS and SSRS SharePoint mode 3 Server Deployment")
(1) |
Excel Service Application. The service application is created as part of the SharePoint installation. |
(2) |
Power Pivot Service Application. Default name is Default Power Pivot Service Application. |
(3) |
Reporting Services service application. |
(4) |
Install the reporting services add-in for SharePoint from either the SQL Server 2016 installation media or the SQL Server 2016 feature pack. |
(5) |
Run the spPowerPivot.msi to install data providers, the Power Pivot configuration tool, Power Pivot Gallery, and schedule data refresh. |
(6) |
Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
(7) |
The SharePoint content, configuration, and service application databases. |
.jpeg "SharePoint Settings")
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.
.jpeg "PowerPivot for SharePoint Single Server Deployment")
(1) |
Excel Service Application. The service application is created as part of the SharePoint installation. |
(2) |
Power Pivot Service Application. Default name is Default Power Pivot Service Application. |
(3) |
The SharePoint content, configuration, and service application databases. |
(4) |
An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
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).
.jpeg "SSAS PowerPivot Mode 2 Server Deployment")
(1) |
Excel Service Application. The service application is created as part of the SharePoint installation. |
(2) |
Power Pivot Service Application. Default name is Default Power Pivot Service Application. |
(3) |
RUN the spPowerPivot.msi to install data providers, the Power Pivot configuration tool, Power Pivot Gallery, and schedule data refresh. |
(4) |
An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
(5) |
The SharePoint content, configuration, and service application databases. |
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.
.jpeg "AS PowerPivot Mode3 Server Deployment")
(1) |
Excel Service Application. The service application is created as part of the SharePoint installation. |
(2) |
Power Pivot Service Application. Default name is Default Power Pivot Service Application. |
(3) |
RUN the spPowerPivot.msi to install data providers, the Power Pivot configuration tool, Power Pivot Gallery, and schedule data refresh. |
(4) |
An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
(5) |
The SharePoint content, configuration, and service application databases. |
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.
.jpeg "SSAS and SSRS SharePoint mode 1 Server Deployment")
(1) |
Excel Service Application. The service application is created as part of the SharePoint installation. |
(2) |
Power Pivot Service Application. Default name is Default Power Pivot Service Application. |
(3) |
Reporting Services service application. |
(4) |
Install the reporting services add-in for SharePoint from either the SQL Server 2016 installation media or the SQL Server 2016 feature pack. |
(5) |
The SharePoint content, configuration, and service application databases. |
(6) |
Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
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.
.jpeg "SSAS and SSRS SharePoint Mode 2 Server Deployment")
(1) |
Excel Service Application. The service application is created as part of the SharePoint installation. |
(2) |
Power Pivot Service Application. Default name is Default Power Pivot Service Application. |
(3) |
Reporting Services service application. |
(4) |
Install the reporting services add-in for SharePoint from either the SQL Server 2016 installation media or the SQL Server 2016 feature pack. |
(5) |
RUN the spPowerPivot.msi to install data providers, the Power Pivot configuration tool, Power Pivot Gallery, and schedule data refresh. |
(6) |
Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server. |
(7) |
The SharePoint content, configuration, and service application databases. |
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.
.jpeg "Logical architecture diagram")
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.
.jpeg "3-server toplogy")
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).
.jpeg "5-server toplogy")
See Also
Install Reporting Services SharePoint Mode
Install Analysis Services in Power Pivot Mode
Power Pivot for SharePoint 2010 Installation