Search This Blog

Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts

Sunday, June 7, 2015

Install and configure Reporting Services 2014 for SharePoint 2013

SQL Server Reporting Services feature can be installed on SharePoint farm. In this article I will describe required steps to install and configure SQL Server Reporting Services 2014 on SharePoint 2013.
There are many combinations of SharePoint and Reporting Services version. Full table is available on MSDN site – Supported Combinations of SharePoint and Reporting Service and Add-in.
I will describe the latest version of SQL Server Reporting Services (2014) and SharePoint 2013.

Three main steps:
  1. Installing the Reporting Services in SharePoint integrated mode
  2. Installing the Reporting Services Add-In for SharePoint Products
  3. Create and configure SharePoint Service Application
Installing the Reporting Services in SharePoint integrated mode.
This installation must be made on every SharePoint Application server that will run Reporting Services Service Application in your farm.
  1. Mount SQL Server 2014 media and run setup. Choose Installation from the left menu.
  2. Launch a wizard by clicking New SQL Server stand-alone installation or add features to an existing installation.
    sqlrs_1
  3. Enter your product key and click Next.
  4. Accept license terms and click Next.
  5. If your server is connected to the Internet you can use Microsoft Updates to check for available updates. Click Next.
    sqlrs_2
  6. The Install Rules page validates system prerequisites. Check if there are any errors and fix them. Click Next.
    sqlrs_3
  7. Choose SQL Server Feature Installation from Setup Role page. Click Next.sqlrs_4
  8. Check Reporting Services – SharePoint, change feature directory if required. Click Next.
    sqlrs_5
  9. Review the Reporting Services Configuration – Install only option in Reporting Services SharePoint Integrated Mode must be selected.
    sqlrs_6
  10. Validate Feature Configuration Rules and fix any errors. Click Next.
  11. Review configurations and click Install.
  12. Click Close on the Complete page.
    sqlrs_7
Installing Reporting Service Add-Inn for SharePoint Products.
The second part of prerequisites is the Reporting Services Add-In. You can install it from SQL Server 2014 media disk or download it from Microsoft Download Center.
This installation must be made on every Front-End server in your farm.
Installation steps:
  1. Run rsSharePoint.msi file
  2. Click Next on Welcome page.
  3. Accept terms and click Next.
  4. Click Install.
Create and configure SharePoint Service Application
  1. Run SharePoint 2013 Management Shell and execute cmdlet Install-SPRSService to install the reporting service.
  2. Run Install-SPRSServiceProxy to install the service proxy.
  3. Go to Central Administration -> System Settings -> Manage services on server and start SQL Server Reporting Services Service, or run this PowershellGet-SPServiceInstance -All | where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
  4. Go to Central Administration -> Application Management -> Manage service applications and create the new SQL Server Reporting services Service Application.
    sqlrs_9
Now we can go and prepare our first report and deploy it to the SharePoint farm.

Installing SQL 2012 Server for Reporting Server

This installation is similar to SQL Server installation: you select the same options you did for the SQL Server, and then select the reporting service. Once the service is installed, you’ll configure it.
Open a session with Administrator on MTLRS01 Server (or whatever name you’ve given to your server) and add the OMAdmins group in the Administrators local group of the server. You can also add any other groups or user that you would like to give admin rights to.
Administrators Properties - OMAdmins
Close the session and open a new one with the OMAdmin user or any user that is an administrator of the reporting server. Click Setup and Installation. Once you’re in the SQL Server Installation Center, click New SQL Server stand-alone installation or add…
New SQL Server stand-alone installation
In Setup Support Rules, click Show Details. Validate that all statuses are passed and click OK.
Setup Support Rules - show details
If you have an evaluation or a serial number, select Specify a free edition, then select Evaluation or Serial Number. Then select Enter the product key, type your product key and click Next. Select I accept the licence terms. If you want to send usage data to Microsoft, also select Send feature usage data to Microsoft and click Next.
Product Key
If you want to install the product update, select Include SQL Server Setup Update. The update will automatically download and install. Again, click Show Details to ensure that all statuses are passed before you click Next.
Product Updates
In the Setup Role section, select SQL Server Feature Installation and click Next.
This server will be used only by the Operations Manager Reporting Database (data warehouse) and is the long-term database. Select the following: Database Engine Services, Full-Text and Extraction…, Reporting Services – Native, Management Tools Basic and Management Tools – Basic. This will install all the tools required to manage the SQL Server. Replace the C: path by D: or any other disks and click Next.
Feature Selection
Click Show Details and validate all statuses are passed before you click Next.
 Installation Rules
In the Instance Configuration section, enter OpsMgrRS in the Name instance field. You can also install SQL without any instance. Change the Path to D: and click Next. You’ll then access the Disk Space Requirements window. Click Next.
Instance Configuraion
For both SQL Service Agent and SQL Server Database Engine, select SRVCSQL user. This is only a domain user and it has no Administrators right on the SQL Server. Make sure to set the Startup Type to Automatic for both services. For the SQL Reporting Services, select OMDWR user. Then select the Collation tab.
Server Configuration
You can use the default which is SQL_Latin1_General_CP1_CI_AS, or choose another supported collation. Then click Next.
Note: The Collation cannot be changed after SQL installation: it‘s very important to select the collation that is supported.
In the Database Engine Configuration window, select Windows Authentication Mode click Add and select the OMAdmins group or admin user needed to install the reporting server. Click Next.
Database Engine Configuration
In Reporting Services Configuration, select Install Only and click Next. Click Next Again in Error Reporting until you get to the Installation Configuration Rules. Then select Show details. Once you’ve ensured that all statuses are passed, click Next.
Installation Configuration Rules
When you get to the Ready to Install section, click Install and wait until all statuses read Succeeded before you click Close.
SQL Server Completion

Validate that SQL is working

On MTLRS01 server, your session is open with OMAdmin user. Press the Windows Key and select SQL Server Management Studio.  Then select Server Name MTLSQL01\OPSMGRRS and click Connect.
Pop-up window - Connect to server
Once you are connected, expand the Server, Databases and System Database. Then validate that the 4 databases are created and mounted. The SQL Reporting Server Database are not created yet.
Expand Server, Databases and System Database

Configuring SQL reporting Services

In this section, we’ll configure and create the SQL reporting Service Database, which will be used to save all OpsMgr Reports.
On MTLRS01 Server, your session is open with OMAdmin user or any other user you used to install the Reporting Server. Press the Windows Key and select Reporting Services. If the Reporting Services is not there, ensure you pin it to the Start menu.
Start menu
Connect to the server and click Service Account. Validate that the current domain user is an administrator of the Reporting Server.. Click Web Service URL to accept the default URL and click Apply. Then validate that the URL was created and click Database and Change Database.
Web Source URL
Select the Create a new report Database and click Next. Double-check the Server Name field corresponds to MTLRS01 \OpsMgrRS and click Test Connection. You will get the confirmation message Test Connection Succeeded to confirm the connection is working properly. Click OK.
Report Database Configuration WizardDouble-check that the Server Name still reads: MTLRS01\OpsMgrRS and click Next. Accept the Default and click Next. Click Next again and you will access the Summary window. Validate that everything is OK and click Next.
Summary
In the Progress and Finish window, ensure that all statuses are Success before you click Finish. Select Report Manager URL and accept the default by clicking Apply.
Report Manager URL
To validate that the URL was created, double-click on the URL: http://MTLRS01:80/Reports OPSMGRRS01.
SQL Reporting Services - Home
The SQL Server Reporting Services Home will open. Return to Web Service URL and click on URL http://MTLRS01:80/ReportServer_OPSMGRRS. The Microsoft SQL Server Reporting Services will open, validating that the SQL Reporting Services is working.
Web Service URL
In the Encryption Keys section, click Backup. In the pop-up window, type in the File Location of the Encryption Key: C:\Sources\RS Encrypted. Enter a password and confirm it. Ensure that this file is taken by your backup and click OK
Backup Encryption Key
Start by opening SQL Server 2012 Management Studio, and ensure that the server name and the instance are there. Here it’s MTLRS01\OpsMgrRS.
Connect to server - Pop-up window
Expand MTLRS01 and expand Databases. Validate that both ReportServer and ReportServerTempDB databases were created as we can see below. Install the Last Service pack and Cumulative roll up for SQL 2012 before you install OpsMgr 2012.
Expand MTLRS01 and Databases
Congrats, your SQL Reporting Server is now installed!

SSRS 2008 installation in Sharepoint 2010 Integrated Mode

Configure SSRS 2008 In Order To Deploy Reporting Services Reports In Microsoft Dynamics GP

The post following this will cover deploying SSRS reports within Microsoft Dynamics GP but I thought I’d cover configuring SSRS as a spearate post rather than assuming everyone has it already configured. A number of the clients I deal with have had GP for a while and never installed the SSRS reports but are now doing so.
When you install SQL Server Reporting Services at the same time as SQL Server you have the option to automatically configure SSRS with the default settings. In the case of someone who has SQL already installed but didn’t install SSRS at the same time, this is not an option.
This How To starts after SSRS has been added to the installation on the client machine at the point where you would start to configure it; if you’d like a walk through adding SSRS to an existing installation of SQL Server, let me know and I’ll sort one out.
To do the initial configuration of SSRS, or to change the configuration of an existing installation, you need to start the Reporting Services Configuration Manager (Start menu >> Programs >> Microsoft SQL Server 2008 R2 ?? Configuration Tools >> Reporting Services Configuration Manager);
Reporting Services Configuration Manager - Report Server Status
The Configuration Manager can largely be treated as a wizard as you move down the tabs on the left side of the window. The first step is to set the Service Account.I am installing this on a demo laptop so I’m just going for the easy option of picking the Local System account but for a production server I would strongly recommend using a Domain User account which is only used for SSRS. Once you’ve made you selection and entered logon details click the Apply button;
Reporting Services Configuration Manager - Service Account
Move to the Web Service URL and either accept the default or change the Virtual Directory and Web Service Site identification details (make sure you make a note of the Report Server Web Service URL as you will need this when configuring SSRS within Microsoft Dynamics GP);
Reporting Services Configuration Manager - Web Service URL
After you’ve clicked Apply, the Results section will confirm the Virtual Directory has been created and the URL reserved. If any issues are highlighted you’ll need to resolve them before proceeding;
Reporting Services Configuration Manager - Web Service URL
Click the Database Tab to select, or create a new, database for use with SSRS;
Reporting Services Configuration Manager - Report Server Database
Click the Change Database to open the Report Server Database Configuration Wizard. I’m leaving the default option of Create a new report server database and click Next;
Reporting Services Configuration Manager - Report Server Database Configuration Wizard - Action
Enter the Server Name and connection details for the SQL server which will hold the SSRS database and click Next;
Reporting Services Configuration Manager - Report Server Database Configuration Wizard - Database Server
Enter the Database and Temp Database Names, pick your language (unfortunately we’re restricted to only English (United States) without an option for English and pick whether SSRS should be in Native or SharePoint Integration Mode. For simplicity I have chosen Native Mode;
Reporting Services Configuration Manager - Report Server Database Configuration Wizard - Database
Enter the log on credentials. Again I have chosen Service Credentials but would recommend a Domain User account for a production environment;
Reporting Services Configuration Manager - Report Server Database Configuration Wizard - Credentials
Click Next and you’ll be presented with a summary of your selections. Review them and click Previous to make any changes or click Next to proceed;
Reporting Services Configuration Manager - Report Server Database Configuration Wizard - Summary
The Wizard will verify the configuration and present confirmation where you need to click Next;
Reporting Services Configuration Manager - Report Server Database Configuration Wizard - Progress and Finish
The Database tab of the main Reporting Services Configuration Manager window will be updated with the database details. Review them and click Apply;
Reporting Services Configuration Manager - Database
Move to the Report Manager URL tab and enter the Virtual Directory you want to use (I left this at the default option but in hindsight I’d rather have changed this to ReportManager_SQL2008R2);
Reporting Services Configuration Manager - Report Manager URL
After clicking Apply, as with the Web Service URL tab, you’ll be presented with confirmation that the virtual directory has been created and the URL reserved;
Reporting Services Configuration Manager - Report Manager URL
Click E-mail Settings and configure the SMTP settings if you want to use Report Server e-mail and click Apply;
Reporting Services Configuration Manager - E-mail Settings
On the Execution Account tab enter the details for accessing remote servers that store external images (make sure you use a domain user account with minimal read-only permissions) and click Apply;
Reporting Services Configuration Manager - Execution Account
Click onto the Encryption Keys tab. Whenever I see a Backup button I feel the need to do so. In the case of a demo system this isn’t especially important, but on a production environment I’d recommend you back up the Encryption Key in case of a need to recover or move the SSRS database;
Reporting Services Configuration Manager - Encryption Keys
Enter a password which will be used to lock and unlock the Encryption Keys file (make sure you pick a password that you will remember);
Backup Encryption Key
Once done the Backup of the keys will be confirmed;
Reporting Services Configuration Manager -
The final step is to scale out the Deployment should you wish;
Reporting Services Configuration Manager -
When done click close and SSRS will be available for use within Microsoft Dynamics GP