Install And Configure SQL Server Reporting Services In SharePoint Server 2016

SQL Server Reporting Services can be installed in SharePoint 2016 by installing it as a Custom Mini Role on a separate Server. Though we can install it in a single server installation mode, upon restart, it will stop functioning. So, when planning to install SSRS in integrated mode with SharePoint 2016, we have to make sure that we create a SharePoint Server with Custom Role. Once it is created, we will install the SQL Server Reporting Services as an add on.


Image Source: TechNet

The scope of this article will be.

• Create a SharePoint Server in Custom Mini Role
• Install Reporting Services in SharePoint Integrated Mode

Setup Custom Mini Role Server

SharePoint 2016 mandates that SSRS be installed on a server that is set as Custom Mini Role. In order to do that, we will need to have a dedicated server that has SharePoint. Currently, I am setting up the farm in Azure. I have already created a multi-server farm. So, let’s create a new VM that will serve as the SSRS Custom Role and add it to the existing Farm .If you are installing the SSRS Services on an On Premise Server, you can skip this step as the Server is already setup for use.



I am selecting the image "Windows Server 2012" as the base OS.



Specify the Virtual Machine name and the amount of resources that have to be dedicated to the VM. I am going ahead with D2 that provides dual core and 7 GB Memory. Specify the admin credentials for the Server.



In the next page, select the Virtual Network where you have set up the Farm. I have already created the SharePoint Farm in the Virtual Network – “Azure-SharePoint 2016 Virtual Network”.



Select ‘ Install the VM Agent’ and proceed.



This will start provisioning the VM and the status can be viewed from the dashboard. As you can see, I have already set up the other VMs that make up the SharePoint Farm.



Once the VM is up and running, log into the machine using the credentials specified during the provisioning of the VM.



The created VM has to be joined to the domain of the Active Directory. Select properties by right clicking ‘This PC’.



By default, it is joined to the WorkGroup. Click on Change Settings.



Add it to the Domain by specifying the "Full Qualified Domain Name" and specify the "AD" admin credentials when asked for it.



This will add the computer to the Domain.



Restart the computer to update the changes.



Install SharePoint in the Custom Mini Role Server

Once the computer has restarted, install SharePoint Server 2016. The details of the installation procedure can be found here. Once the Installation is completed, it will run the configuration wizard. This will add the current server to the SharePoint Farm and will assign the Mini Role – Custom to the server.



Click on Next.



Select the radio button ‘Connect to an existing farm’.



Specify the database server name and the configuration database name which is SharePoint_Config, by default. Click on Next.



When the SharePoint farm was created for the first time, we would have entered the passphrase which needs to be entered in the next window.



Assign Custom Mini Role

Now, we have to specify the Server Role in the next step. Select ‘Custom’ radio button.



The configuration wizard will list out the configuration settings for one final time, before starting configuring the server. Check for accuracy. Click on Advanced Settings, if you want to set up central administration in the custom mini role. By default, it is not installed. In this set up, I am going to add the central administration to the current Server Role as well.



Select ‘Use this machine to host the central administration’, if you plan to set up central admin.



Click on "Next" to start configuring the Custom Mini Role.



Thus, SharePoint Server for Custom Mini Role is now being configured and would take some time.



The configuration has completed.



Get SQL Server Installation Files

The SQL Server installation files are required to add the SSRS add-ins to the SharePoint Server. In case the SQL Server Set up files used for SQL Installation are missing in the server, you can get it from here



Select the package as ISO and click on Download.



This will download the installation media files.





Install Reporting Services in Custom Mini Role Server

Now, we have to install SSRS bits in the Custom Mini Role. In order to do that, spin up SQL Server Installation Center.



Browse for the Installation file location.



Select the radio button ‘Add features to an existing instance of SQL Server 2016’ and click on Next.



Select the features that have to be installed as part of the reporting services installation.

• Reporting Services – SharePoint
• Reporting Services Add-in for SharePoint Products.

Select ‘Install only’ radio button from ‘Reporting Services SharePoint Integrated Mode’.



This will install two SSRS components required for the successful installation of reporting services. Click on Install to start the installation of Reporting Services in the Custom Mini Role.



This will start the installation of the reporting services components.



Finally, the reporting service installation has completed. A Server Restart is required to reflect the updates in the Custom Mini Role.



Register and start the Reporting Services SharePoint Service

This section is required to be done only if we had installed the Reporting Services in a Server that does not have SharePoint installed (where SharePoint was installed after installing Reporting Services). Currently, the demo server we are using has SharePoint installed, so it is not necessary as it has already been done automatically in the previous steps. So, in case you have installed SharePoint after Reporting Services installation, run the below steps to register Reporting Services SharePoint Service. Spin up SharePoint 2016 Management Shell as Administrator.



Run the below commands to install Reporting Services and Reporting Services Proxy Service.

Install-SPRSService
Install-SPRSServiceProxy



Install Reporting Services Add-in in SharePoint Front End Servers

The next step of setting up SSRS in the multi farm environment is to install ‘Reporting Services add-in for SharePoint’ in all the Front end SharePoint servers. If you have load balancers, it is advised to install this component in all of them. In order to install the Reporting Services add-in, make sure that you have the SQL Server set up files copied to the server. Just like we installed the reporting services in the Custom Mini Role Server, spin up SQL Server 2016 setup wizard. As you proceed through the wizard (just like we saw in Custom Mini Role above), When the feature selection window comes, select only ‘Reporting services add-in for SharePoint’. Do not select ‘Reporting Services- SharePoint’.



This will install the reporting services add-in to the front end servers.

Create SSRS Service Application

Now, we have to configure SSRS Service application in the Custom Mini Role Server (Or wherever Central Administration has been configured). This was one of the reasons why we had configured Central Administration in the Custom Mini Role server. Spin up Central Administration and from the service applications page, select ‘SQL Server Reporting Services Service Application.



Specify the Application Pool, Service Account, Data base name, and back-end data base Server that will be used by the service application. Click on OK.



This will start the provisioning of the SSRS Service Application.



Finally, the service application has been created.



Test SSRS installation

In order to ensure that SSRS has been installed and configured successfully, we can go to a document library and try to convert it into a Report library by adding the SSRS Content Types. From the Add Content Types page, add the below SSRS Content Types to the library. The fact that these Content types are visible, is a proof of successful SSRS configuration.



Once it is added, you will be able to create SSRS reports from the library by launching the Report Builder Tool.



You can read more about creating the SSRS report in SharePoint Server 2016 from here.



Summary

Thus, we saw how to install and configure SSRS in SharePoint Server 2016.