Setup SQL Server Reporting Services in SharePoint 2010 environment

Setup SQL Server Reporting Services in SharePoint 2010 environment

SQL Server SP

SQL Server Reporting services let you create and integrate SSRS reports in SharePoint. You can create tabular as well as graphical reports using Report Builder or Visual Studio. For example, in my blog “Dashboard – Speedometer“, I have explained how to create a speedometer like SSRS report. But before delving into report creation, you need to setup and configure reporting services in your SharePoint 2010 environment. Read on for the instructions.

 

Step 1:Install SQL Server Reporting Services SharePoint 2010 Add-on on each WFE and App servers: (The advantage of installing the add-on prior to the SharePoint product is that as new servers are added to the farm, the Reporting Services Add-in will be configured and activated by the SharePoint farm.)
Please note that SP Product and Configuration Wizard installs SQL Server 2008 R2 version of Reporting Services Add-on. SQL Server 2012 includes a new version of the add-in that is required for SQL Server 2012 features. Please refer to this article for more details.Approach 1:

  • The installation wizard: New with SQL Server 2012, the add-in can be installed by the SQL Server installation wizard. Start SQL Server installation and choose Reporting Services – SharePoint and Reporting Services Add-in for SharePoint Products on the Feature Selection page of the wizard.
  • Go through the wizard with default selection. Please note that “Reporting Services SharePoint Integrated Mode” install only is selected.

Approach 2:

  • rsSharepoint.msi: The add-in can be installed directly from the installation media or downloaded and installed. The rsSharepoint.msi supports both a graphical user interface and a command line installation. You must run the .msi with administrator privileges by first opening a command prompt with elevated permissions, and then running the rsSharepoint.msi from the command line. For more information on downloading the add-in, see Where to find the Reporting Services add-in for SharePoint Products.

Step 2:

Activate “Report Server Integration Feature” and “Report Server Central Administration Feature” under CA Site Collection Feature.

Step 3:

Create SQL Server Report Server instance on DB server. Run SQL installation wizard and create a Report Server Instance with No Mode (3rd Option) (Install, but do not configure the report server). Setting up in “No Mode” will give you flexibility to choose Native or SP Integrated Mode while creating Databases at later stage.

Step 4: (This step can be skipped if you want CA Service Application to create Reporting DB in SP Integrated Mode)

Create and setup Database Instance to run in SharePoint Integrated Mode:
Launch Reporting services configuration manager and connect to Report Server instance. Click on Change Database and create a new one in SP Integrated mode.
(Reporting Services Native mode uses two SQL Server relational databases to store report server metadata and objects. One database is used for primary storage, and the second one stores temporary data. The databases are created together and bound by name. With a default SQL Server instance, the databases are named reportserver and reportservertempdb. Collectively, the two databases are referred to as the “report server database” or “report server catalog”.
Reporting Services SharePoint mode includes a third database that is used for data alerting metadata.)

Step 5:

Create a SharePoint service application in SharePoint Central Admin. Go to “Manage Service Applications” in CA and create a New SQL Service Reporting Services Service Application. (If you don’t see this listed then check whether SSRS Add-on is installed properly (Step 1)). Preferably use single SA for all BI-related service application.  Mention DB server name and database name. If Database doesn’t exist, it will be created. ([dbname], [dbname]_TempDB, [dbname]_Alerting)

Step 6:
Post Installation configuration.
Start SQL Server Agent on DB Server.
Go to Service Application and configure “Provision subscription and alerts” (optional)
Please note that there is an option of creating an execution account. Ignore this if you want service application’s service account to be used.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close Menu