skip to Main Content
SSRS Native Configuration In A SQL Always-On Cluster

SSRS Native Configuration in a SQL Always-On Cluster

Contents

Introduction

Configure the Web Service URL

Configure the Report manager URL

Configure the Service Account

Configure the Database

Add the ReportServer & ReportServerTempDB DB’s to the AO Cluster

Add the second SSRS Server and configure it

Scale-out Deployment

Introduction

SQL Server Reporting Services (SSRS) is installed in to each SQL instance. The other method of deployment uses SharePoint and is a shared resource on the SQL Server. Each instance that needs SSRS native, will need to have it installed individually either at the instance setup or as an additional feature later on.

NOTE: When installing Reporting Services, it is important to choose Install but do not configure server.

This document assumes that the SSRS feature is already installed and you need to configure it. On the primary SQL server in the Availability Group (See SQL Management Studio – or RDP to the Listener name) open “Reporting Services Configuration Manager” from the start menu.

Note this is being done on SQL 2014, but the process is the same in SQL 2016!

Configure the Web Service URL

In Reporting Services Configuration Manager, Web service URLs and Report Manager URLs are used to access the Report Server Web service and Report Manager. By default, Reporting Services provides default URLs as shown below but you can change them as per your requirement using the Advanced button. Click the Advanced Button.

Click the Add button:

Enter the Listener name under host header:

Back to the advance screen, note the extra line and click OK:

Results. Note the URL for the local server and the listener are created:

Configure the Report manager URL

In Reporting Services, Report Manager URLs are used to access the Report Server Web service and Report Manager. By default, Reporting Services provides default URLs but you can change them as per your requirement by clicking the Advanced button. Click the Advanced Button.

This is exactly as adding the Host Header for the Web Service URL! Click the Add button:

Enter the Listener name under host header:

Back to the advance screen, note the extra line and click OK:

Results. Note the URL for the local server and the listener are created:

Configure the Service Account

By default a reporting sets up a service account to manage the service, but I have a dedicated SQL account for this instance and want to use that instead. Click Apply.

Configure the Database

Configure the Database to use the Listener name. Click Change Database:

Create a new Report Server Database:

Enter the Listener name under Server Name and I left the authentication to me. Make sure to test the connection before proceeding…

Change the language if you want…

Under Credentials, note the service credentials are using the service account configured earlier.

Review the Summary, then click Next:

Click Finish:

Add the ReportServer & ReportServerTempDB DB’s to the AO Cluster

Refer to the SQL Always On Documentation for this information.

Add the second SSRS Server and configure it

Configure SSRS on the secondary server just as you did above for the first server apart from when you get to the DB configuration, you need to do the following to add the configuration to an existing report server database…

Enter the Listener name under Server Name and I left the authentication to me. Make sure to test the connection before proceeding…

Change the language if you want…

Under Credentials, note the service credentials are using the service account configured earlier.

Review the Summary, then click Next:

Click Finish:

Scale-out Deployment

Back on the original server (The Primary), go to Scale-out Deployment. Note that the new server is there, but waiting to join. Click Add Server

After a short while, the server Scale-out Deployment details will show in the secondary server SSRS configuration. That’s it!

NOTE: One thing this does not account for is scheduled reports. Those are setup as SQL Server Agent jobs on the SQL instance hosting the database at the time they are created! You have been warned! It may be worth investigating copying them to the secondary by scheduled task and enabling them if needed when the server flips over, but that’s a story for another day. Goodnight IT land!

 

Download the full guide

[vcex_button url=”/?ddownload=905″ title=”Download guide” style=”graphical” align=”left” color=”blue” size=”large” target=”self” rel=”none”]Download the full guide here[/vcex_button]

Colin Parke

Colin is an IT architect and leader with 19 years’ experience primarily in Global IT infrastructure and management roles in a variety of sectors including education, employment & manufacturing. He has architected infrastructure solutions and led teams across many distinct geographical regions, including the UK, USA, Tasmania and Saudi Arabia.

“I consistently strive to provide innovative ways of working for the end users that enhance their experience and productivity, whilst keeping to budget.”

Colin is Wintel specialist and a subject matter expert across most of the Microsoft product range. He has been an advisor to Citrix development teams and has been an active member of the VMWare User Group.

This Post Has 4 Comments
  1. Thank you so much for this article! Can I get further clarification: Does this means that the Reporting Service (the web URL) is installed in the same server with AG? The recommended architecture for SSRS is to have the Reporting Services on different VMs? Can I have my Reporting Services sits on the same box with the AG servers as I only have 2 VMs and I need to have an AG with 2 nodes. Thanks in advance!

    1. Hi, it can be installed on the same server(s) as the AG, but it doesn’t have to be and as you said, it is advised to put it on a separate server(s). It’s all down to available resources and how much the SQL is going to be hammered. I’ve done installs directly on the same servers as the AG servers where resources were tight and SQL wasn’t getting pushed without issue and I’ve done it as dedicated reporting servers installs. If you’re installing on the AG servers, just make sure you configure SQL max mem to give the OS enough memory to deal with the reporting service. Bear in mind though, if your report writers rewrite a bad report (select * from *, for example) that will have a negative impact on the SQL as it tries to run.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top
×Close search
Search