Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Deployment Topologies for SQL Server BI Features in SharePoint

 

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:

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.

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.

Arrow icon used with Back to Top link

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

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.

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.

Arrow icon used with Back to Top link

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 "http://go.microsoft.com/fwlink/?LinkID=296473" \t "_blank" http://go.microsoft.com/fwlink/?LinkID=296473).

 

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.

Arrow icon used with Back to Top link

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.

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.

Arrow icon used with Back to Top link

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

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.

Arrow icon used with Back to Top link

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.

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.

Arrow icon used with Back to Top link

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.

Logical architecture diagram

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.

Arrow icon used with Back to Top link

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.

Arrow icon used with Back to Top link

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.

3-server toplogy

Arrow icon used with Back to Top link

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).

5-server toplogy

Arrow icon used with Back to Top link

Community Additions

ADD
Show:
© 2015 Microsoft