Scaling out Reporting services to multiple nodes, in itself, is a relatively simple process. It’s when we come to solve problems and investigate performance that we begin to see there is a lot more going on under the hood that’s not clear through implementation. There are also some foundational elements that have changed in SQL 2016 with next to no guidance from Microsoft on the changes.

Plan your approach

There’s always several ways to do things & that’s no different with scaling out SSRS. In my opinion, the best approach is to have SSRS on 2-3 servers, not sharing any servers with the DB engine or other component (SSAS or SSIS etc.). These would then all sit behind a hardware based Network load balancer (NLB) such as an F5. This gives you one URL that feeds users into all your SSRS “nodes”. The users never have visibility of server names & you can easily scale servers in or out, more so, if you’re using virtual technology.

The problem with this approach and one that is solved easily (as detailed on MSDN) is “View State Validation” which I’ll go into in greater detail later. You can, of course, achieve a scale out deployment without an NLB. You can also have each of your SSRS instances share a server with other applications or SQL components. The number of users you have & the report executions you see will usually dictate the setup you require.

Licensing

The process of setting up a scaled out SSRS deployment is near identical from 2008 R2 through to 2014. So don’t worry if some screenshots look slightly different to your setup. Ill also explain what’s changed in 2016 at the end.

One thing I do need to point out is that the Scale out deployment feature isn’t available to all versions. In both SQL 2012 & 2014 you need to have Enterprise or Business Intelligence Edition.

Features Supported by the Editions of SQL Server 2012

Features Supported by the Editions of SQL Server 2014

In SQL 2008 R2 & 2016 (as the BI edition was scraped) you will need Enterprise edition.

Features Supported by the Editions of SQL Server 2008 R2

For all versions above, Developer & Evaluation are also supported so you can still try these steps out if you can’t implement it in a production environment.

Assumptions

Ill detail the basics of scaling out SSRS & also highlight any pitfalls you may come across before going into more detail with my preferred method above. For this I’m going to use SQL Server 2014 on Windows Server 2012 R2

Ill assume you have a SQL Server engine already set up & running. Im going to use an Active directory account (eg. Domain\SSRSService) as the service account in the SSRS nodes so this should have permissions to create DBs on the SQL engine. (DB01). The SSRS installation process is the same for all nodes & only varies at the point you create the ReportServer DB from the first node. You then simply pick the DB when attaching subsequent nodes.

Starting with our SSRS01 server, follow these steps to install & configure each SSRS server. 2 in our case

Prerequisites

The .NET Framework 3.5.1 feature is required for our SQL installation & must be installed from the Server Manager as it’s not a default feature on Windows Server 2012 R2

You should also add the service account we’ll use as an admin on the server, at least for now, to help with setup & configuration.

Installation Steps

1. Mount or insert your media then run Setup.exe as Administrator
2. From the Installation Tab select New SQL Server stand-alone installation…

   

3. Accept the pre-written default on the Product Key page
4. Select & Accept the License Terms on the next page
5. I select to Use Microsoft Update but this is personal choice (or company dictated)
6. On the Install Rules page, all Rules should show Passed. Rectify anything blocking the install
7. Choose SQL Server Feature Installation on the Setup Role page
8. Select the following features:

   1. Reporting Services – Native
   2. Client Tools Connectivity
   3. Client Tools Backwards Compatibility
   4. Management tools – complete
   5. SQL Client Connectivity SDK

      

9. Accept the Default Instance Configuration for the SQL instance (there should be no other instance installed on the target server). Hit Next
10. At the Server Configuration page enter the service account added to the Server Admin group earlier (eg. Domain\SSRSService).

    

11. There should only be one service listed – SQL Server Reporting Services
12. On the next page select “Install only” – It will be the only selectable choice.
13. Hit Next on the Ready to Install page & proceed through the confirmation pages to the point of installation
14. Once complete Close the setup program.

SSRS Configuration

1. Open Reporting Services Configuration Manager
2. Ensure the target server & its default instance is correct. Click Connect

   

3. Go to the Web Service URL page & Hit Apply to set up the URL with default settings.
4. Click the Database tab and click Change Database
5. This step varies depending on if this is the first SSRS server in a Scale Out deployment:

   1. If no ReportServer DBs exist and this is the first SSRS server – choose Create a new report server database
   2. Else – Choose an existing report server database

6. Enter the SQL Instance name & leave the Auth Type to Current User.
7. Test Connection & hit Next (if successful)
8. Again, the steps vary for connecting to an existing or creating a new database:

   1. Existing – Select the Report server database from the drop down box (default: ReportServer)
   2. New Database – The Database name should default to ReportServer & language should be set to English (United States) (or your chosen language). This is fine, Hit Next

9. On the next page leave the default of Service Credentials as the Authentication Type. Hit Next.
10. A summary page will show all the settings you have set. Hit Next to complete the configuration.
11. All tasks should show as Success. Click Finish.
12. Now move to the Report Manager URL tab and hit Apply again to accept the default settings
13. You can set up the Email Settings tab with a sender address of your choice & your mail server.
14. No Execution Account is required as Im using kerberos authentication for all reports & elements. Im also using stored credentials within specific datasources (in Report manager) for subscriptions.
15. At this point, we can skip the Scale Out Deployment section as we’ll cover that later.
16. From the Encryption Keys tab select Backup to back up a copy of the encryption key. Save this to a secure, backed up location or a version control system.
17. Hit Exit to complete configuration

Configuration file Edits

Here is where we need to make manual changes to configuration files to allow Kerberos authentication to work. (This should be handled within the configuration manager by now. This process has been the same since SQL Server 2008!)

TIP: Kerberos authentication needs to be set up for all elements of the total environment before it will be functional from client machines. You may need to add or open delegation on the AD service account you use to run the SSRS service.

1. Take a backup of the rsreportserver.config file into: (Create folder)
   • C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\Config Backups\
2. After backup, edit:
   • C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config
3. Locate the “AuthenticationTypes” entry and replace <RSWindowsNTLM/> with <RSWindowsNegotiate/>

   

TIP: You can now add users or groups to Report manager, to open this up to others but as long as you are part of the BUILTIN\Administrators group on the SSRS server, we can leave that alone for now, you’ll have all the access you need.

Congratulations! You now have a single node SSRS installation.

Scale Out Deployment Steps

When configuring a scale out deployment behind a load balancer (like an F5), you will need to adjust the report manager web.config file on every SSRS server to work properly in a load balanced namespace.

Assuming you’ve now followed the above steps for 2 or more SSRS Servers, we can now bring them together.

1. Open Reporting Services Configuration Manager on your first server (SSRS01)

   

2. Ensure the target server & its default instance is correct. Click Connect
3. Once loaded go to the Scale-Out Deployment tab. The new server should be listed there with a Status of Waiting to Join.

   

4. Highlight the SSRS02 server and click Add Server. Repeat this for each server you have listed.

We now need to make configuration file changes so that user sessions can travel between servers securely. These steps are for your first SSRS server, however, if you are just adding a server to an existing farm you will just copy the values we set from one server to the next.

1. Backup & then Open the Report Manager Web.config file which is located in the default path

   C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportManager

2. Backup & then Open the Report Manager Web.config file which is located in the default path

   C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer

3. In the Web.config file, locate the <system.web> section.
4. Add the following <machineKey> section to in the <system.web> section:

   <machineKey validationKey=”AutoGenerate,IsolateApps”
   decryptionKey=”AutoGenerate,IsolateApps” validation=”3DES” decryption=”3DES”/>

   NOTE: Im using “3DES” encryption here to get round a FIPS Compliance error detailed HERE. This may not be relevant in your organisation or setup so other encryption algorithms also work

5. Save the Web.config file
6. Take a backup of the rsreportserver.config file, default location is:

   C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer

7. Locate the <Service> section, and add the following information to the configuration file, replacing the Hostname value with the virtual server name for your NLB server:

   <Hostname> reporting.company. com</Hostname>

8. Find the <urlroot> section, and  update it to reflect the url for your report server application, for example:

   <UrlRoot>http:// reporting.company. com/reportserver</UrlRoot>

Note on the ReportServerUrl Property

Do not modify <ReportServerUrl>. If you modify this URL, you will introduce an extra roundtrip through the virtual server each time an internal request is handled. For more information, see URLs in Configuration Files (Reporting Services). For more information about editing the configuration file, see How to: Modify a Reporting Services Configuration File in SQL Server Books Online.

You can now restart the Reporting Services service on each server to ensure the changes you made to the configuration files have taken effect.

You will need to setup the load balanced URL in your Network Load balancer but these steps vary depending on hardware. Also, in the absence of a Network load balancer, look into windows load balancing. There is a great tutorial HERE

With these configuration changes, you can now navigate to SSRS Report Manager using http://reporting.company. com/Reports or using the individual server names (http://SSRS01/Reports). Both will work, although when using the server names you are actually being redirected through the load balanced URL without knowing it. You can see this if you disable or remove the load balanced URL. You will no longer be able to render SSRS using the server URLs.

SSRS will also load balance its subscriptions across all servers too which helps reduce the impact on users if you have a lot of report schedules.

How SQL Server 2016 is different…

I go into a lot of detail from discovering the issue to getting confirmation of the solution (or changes in SQL 2016 under the hood) in a post on my site CraigPorteous.com. The TL;DR version of that story is that setting up as SSRS 2016 Scale out deployment no longer requires us to configure “View State validation” for the front end Report Manager. You still need to ass your machine keys (as above) to the ReportServer Web.config file but you won’t find one in the Report Manager folder. Its also worth mentioning that the documentation Microsoft provide does not reflect this change for 2016. Handy 🙂

References

View State Validation

• Configure a Report Server on a Network Load Balancing Cluster

2016 version of documentation

• Configure a Report Server on a Network Load Balancing Cluster

Edition Changes

• Features Supported by the Editions of SQL Server 2012
• Features Supported by the Editions of SQL Server 2014
• Features Supported by the Editions of SQL Server 2008 R2

FIPS Algorithm Usage

• SSRS 2008 FIPS error

SSRS Configuration changes

• URLs in Configuration Files (SSRS Configuration Manager)
• Modify a Reporting Services Configuration File (RSreportserver.config)

Windows Load Balancing

• Load Balancing Web Servers with Windows Server 2012 R2

CraigPorteous.com

• SOLVED: SSRS 2016 behind a Network Load Balancer

• Author
• Recent Posts

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous

Latest posts by Craig Porteous (see all)

• How to secure Reporting Services with Group Managed Service Accounts (GMSA) - November 7, 2018
• Contribute, contribute, contribute! - June 20, 2018
• Top 10 things you must document in SQL Server Reporting Services (SSRS) - February 26, 2018