One of the most common question I get asked is, “Can I use Windows Server Failover Clustering (WSFC) for SQL Server Reporting Services?” The primary intent behind this question is high availability. And when high availability is a requirement for a Microsoft product, the first thing that comes to mind is a WSFC.

SQL Server Reporting Services is a server-based reporting platform that comes with the SQL Server product suite. Unfortunately, it is not supported to run on top of a WSFC.  I just wished they would change the wordings on that SQL Server Books Online article since WSFC is no longer called Microsoft Cluster Services.

When I found out that Reporting Services is not supported to run on top of a WSFC, the first thing that came to mind was that it probably has a different underlying architecture than that of the database engine. Indeed, it does. I’d like to think of Reporting Services as an ASP.NET web application that does not require IIS. And because I consider it a web application, providing high availability would mean using a network load balancing solution.

It Works as a Web Application

There are two modes available when running Reporting Services – native mode and SharePoint-integrated. In native mode, you are running Reporting Services on its own. In SharePoint-integrated mode, you are running it side-by-side with SharePoint and using it as the gateway to Reporting Services. Note that, in both cases, you will be accessing Reporting Services reports via a browser. So, when you decide to provide high availability to Reporting Services, you really have to think of it in terms of network load balancing solution, not WSFC. Which also means we can call the deployment as a farm instead of a cluster. The underlying Reporting Services databases – ReportServer and ReportServerTempDB – would need to be in a WSFC because they require a database engine. You can think of the configuration in terms of separating the compute resource (the Reporting Services engine) and storage resource (the Reporting Services databases where you store your report definitions, connection strings, etc.) It’s no different from the typical “cloud” deployments nowadays.

Scaling Out a Reporting Services Server in Native Mode

I’ll be honest. The main reason why I wrote a series of articles on MSSQLTips about how to scale-out a Reporting Services 2008 server is because the documentation on Books Online is confusing and hard to follow. Despite the upcoming SQL Server 2016 version, the documentation remains the same. But to simplify the process, it involves the following high-level tasks.

1. Install the Reporting Services binaries on all of the servers that you want to include in the farm (see, I used the term “farm” instead of a cluster)
2. Use the Reporting Services Configuration Manager to configure a native mode Reporting Services server using one of the servers (we’re assuming that you already have a highly available database engine to store the databases)
3. Add the remaining servers in the newly created Reporting Services server
4. Configure the web.config and RsReportServer.config files
5. Convince your network administrator to configure your new Reporting Services farm on the load balancer

Here’s the series of articles I wrote on scaling out a Reporting Services 2008 in native mode. The concepts still apply up to SQL Server 2014.

• Scale Out SQL Server 2008 R2 Reporting Services Farm using NLB Part 1
• Scale Out SSRS R2 Farm using Windows Network Load Balancing Part 2
• Build a Scale Out SQL 2008 R2 Reporting Services Farm using Windows NLB Part 3
• Building a Scale Out SSRS 2008 R2 Farm using Windows NLB Part 4
• Highly Available, Scale-Out SQL Server 2008 R2 Reporting Services Farm

Scaling Out a Reporting Services Server in SharePoint-integrated Mode

Unfortunately, the official documentation for deploying a Reporting Services server in SharePoint-integrated mode is no different from the native-mode: it’s confusing. Plus, because of the changes between versions of SQL Server, integrating Reporting Services in SharePoint evolved from just pointing SharePoint to a Reporting Services URL to having Reporting Services run inside of the SharePoint service application architecture. With SQL Server 2012 and later versions, the Reporting Services instance runs as a SharePoint service application. This means that the high availability of the Reporting Services instance is dependent on the SharePoint farm. Also, SharePoint does its own load balancing prior to sending the user requests to the Reporting Services instances. The installation and configuration of a SharePoint-integrated Reporting Services server involves the following high-level tasks. This assumes that you already have a SharePoint farm available and are just adding the Reporting Services integration. It also assumes that the Reporting Services servers are joined to the SharePoint farm.

1. Installing the Reporting Services binaries in SharePoint-integrated mode
2. Installing the Reporting Services Add-In for SharePoint
3. Configuring the Reporting Services service application in SharePoint

Here’s the series of articles I wrote on scaling out a Reporting Services 2012 in SharePoint-integrated mode. The concepts still apply up to SQL Server 2014.

• Install and Configure SQL Server Reporting Services 2012 SP1 and 2014 in SharePoint Integrated Mode
• Installing and Configuring Reporting Services 2012 SP1 or 2014 in SharePoint-Integrated Mode Part 2

The Role of the Load Balancer and High Availability

The load balancer can either be a hardware appliance like that of F5’s BigIP LTM, Barracuda’s Load Balancer ADC, etc. or software-based like that of Windows Network Load Balancer. It sits in front of the farm and waiting for client requests. Depending on the load balancer algorithm, the client requests will be redirected to any of the servers defined in the farm (the SharePoint internal load balancer is independent of how the external load balancer behaves.) Which also means that the availability of the Reporting Services server – be it in native mode or SharePoint-integrated – is dependent on the availability of the load balancer regardless of how many servers you have in your farm. And whether we like it or not, our job as SQL Server professionals are now highly dependent on someone else.

Summary

These are the ways to provide high availability to your Reporting Services servers. You can have a number of possible deployment and configuration combinations depending on your goals and architecture. I’m not saying its straightforward and easy. But at least this gives you a starting point on how to design and implement high availability for your Reporting Services deployments.