Guidance for Using SQL Server BI Features in a SharePoint 2010 Farm
This topic summarizes feature availability based on the versions and editions of the software you are using. It also explains SharePoint 2010 installation requirements for using specific SQL Server features. For information related to SharePoint 2013, see Deployment Topologies for SQL Server BI Features in SharePoint.
In this topic:
SharePoint 2010 products are 64-bit only. If you have a 32-bit installation of a previous version of SharePoint and Reporting Services installed in SharePoint integrated mode, you will not be able to upgrade to SharePoint 2010. For more information, review the SharePoint documentation.
Reporting Services includes an add-in for SharePoint products. Supported configurations for the add-in and report server are available at a more granular level than what is indicated here. For more information, see Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2014).
SharePoint developer tools only support a SharePoint standalone configuration. For more information, see the SharePoint documentation: Requirements for Developing SharePoint Solutions.
Some SQL Server 2014 Business Intelligence features are only supported on specific editions of SharePoint products.
For more information, see Features Supported by the Editions of SQL Server 2012.
It is recommended you update your SharePoint 2010 installation to SharePoint 2010 Service Pack 1 (SP1). SharePoint SP1 is required in the following cases:
You want use SQL Server 2012 version of the database engine for the SharePoint content databases or Reporting Services catalog databases.
You want to use PowerPivot and the PowerPivot configuration tool.
One of the primary reasons SP1 is required for SharePoint installations running with SQL Server 2012 is the database engine feature sp_dboption, that was deprecated in a previous release, is discontinued in the SQL Server 2012 release. For more information, see Discontinued Database Engine Functionality in SQL Server 2014
Download SharePoint Server 2010 SP1 and apply it on all servers in the farm.
On an existing farm, you will need to use one of the following additional steps to complete the SharePoint SP1 upgrade. For more information, see Known issues when you install Office 2010 SP1 and SharePoint 2010 SP1 and Description of SharePoint Server 2010 SP1:
SharePoint Products Configuration Wizard: Run the wizard to complete the SP1 upgrade and configuration.
Complete the upgrade with psconfig: Run the command psconfig –upgrade to complete the SP1 upgrade
For more information, see the “upgrade” section of (SharePoint Server 2010) and Resource center: Updates for SharePoint 2010 Products
The SharePoint Products Preparation Tool enables server roles in the operating system and installs other prerequisite software required by a SharePoint installation. The following table identifies additional steps to configure your server for SQL Server 2014.
Reporting Services Add-in
The SharePoint 2010 Products Preparation tool installs the SQL Server 2008 R2 version of the Reporting Services add-in. SQL Server 2014 includes a newer version of the add-in that is required for SQL Server 2014 features. The add-in can be installed using the SQL Server installation wizard as well as downloaded from MSDN. For more information on where to get the current version of the add-in and how to install it, see Where to find the Reporting Services add-in for SharePoint Products (SharePoint 2010 and SharePoint 2013) and Install or Uninstall the Reporting Services Add-in for SharePoint (SharePoint 2010 and SharePoint 2013).
Analysis Services OLE DB Provider (MSOLAP)
SharePoint 2010 installs the SQL Server 2008 version of the OLE DB provider as part of an Excel Services deployment. This version does not support PowerPivot data access. You should install later versions of the provider on SharePoint servers that support PowerPivot data connections. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers
SharePoint 2010 lists ADO.NET services in the list of prerequisites, but the Prerequisite installer does not install it. To add ADO.NET services, you must install it manually. Installing ADO.NET services is required if you want to use SharePoint lists as data feeds to PowerPivot workbooks or Reporting Services reports. For instructions, see Install ADO.NET Data Services to support data feed exports of SharePoint lists.
Which SQL Server 2014 features you install and the installation order of those features will determine what levels of integration with SharePoint are possible. For example, while some level of feature integration is available through Reporting Services on a SharePoint server that uses a built-in database, most feature integration scenarios require a farm installation of SharePoint, because only a farm installation provides the infrastructure required for some BI features.
A SharePoint farm can be a single server or multiple servers. What sets a farm apart from a standalone installation is the presence of additional infrastructure, such as the Claims to Windows Token Service.
A farm is enabled when you choose the Server Farm option in SharePoint setup. You must have a farm installation if you want to use PowerPivot for SharePoint or Power View. The Standalone SharePoint installation is supported and common for development environments of Reporting Services. However a SharePoint farm installation is recommended for a production environment.
A complete server installation is also required for PowerPivot for SharePoint or the Reporting Services shared service.
The last page of the setup wizard prompts you to configure the farm and optionally configure a default web application and root site collection. Most setup users follow this installation path. If for some reason you do not configure the farm, you can use the farm configuration option in the PowerPivot Configuration Tool to configure the farm.
In previous releases, leaving the farm un-configured was recommended for installing PowerPivot because it reduced the steps needed when if you used SQL Server installation options to install PowerPivot for SharePoint in a ready-to-use state. In this release, it is no longer important. You can use the PowerPivot Configuration Tool to tune an existing farm so that it uses settings recommended for a PowerPivot for SharePoint installation.
Reporting Services can be installed into an existing SharePoint farm. You can install Reporting Services or SharePoint in any order, but the configuration of Reporting Services will vary depending on the order. For more information, see Add an Additional Report Server to a Farm (SSRS Scale-out) and Install Reporting Services SharePoint Mode for SharePoint 2010