Deployment Topologies for SQL Server BI Features

This topic describes common topologies for installing SQL Server features in a SharePoint environment.

All topologies assume the same domain or trusted domain, with all servers running in the same farm.

Application Distribution in a Farm

Applications in a SharePoint farm are designed as distributed applications that divide the overall functionality across specific tiers. You can run all tiers on a single computer, or you can use multiple servers for each tier. The result is a scalable platform that lets you build up capacity in the areas where you need it most: connections on the front-end, processing on an application server, or data storage on the back end.

In this release, both Reporting Services and PowerPivot for SharePoint are implemented as distributed applications, with services that run on both the web front-end and application tiers. Application data is stored on the data tier, in service application databases that are created when you install and configure the feature.

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 PowerPivot for SharePoint or Reporting Services deployment in SharePoint. Additionally, the MSOLAP OLE DB providers and ADO.NET Services are required for some PowerPivot 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.

Standalone 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, PowerPivot 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 PowerPivot for SharePoint sequentially, on the same physical server. For instructions on a standalone server configuration, see Deployment Checklist: Reporting Services, Power View, and PowerPivot 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 PowerPivot 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 PowerPivot 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 PowerPivot 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 PowerPivot 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).