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.
- 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)
- 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)
- Add the remaining servers in the newly created Reporting Services server
- Configure the web.config and RsReportServer.config files
- 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.
- Installing the Reporting Services binaries in SharePoint-integrated mode
- Installing the Reporting Services Add-In for SharePoint
- 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.
Hey Edwin,
Another option for a HA SSRS deployment in native mode is to use the listener name in your url instead of having to put everything behind a load balancer. This requires that SSRS be installed on the same nodes as your database servers which utilize Availability Groups. With this approach, you perform steps 1 – 4 the same as you have listed, but instead of the load balancer step, you can now use the listner name (which will be needed) within the Reports and ReportsServer urls and it will redirect http traffic to the “primary” server that’s also hosting the database as the listener is really only acting as a DNS alias for SSRS url requests. Again, this only works for SSRS in native mode because that utilizes http.sys instead of IIS, but I’ve found it works out quite well for basic deployments that require HA capability. If you get more complicated reports that run off of schedules, etc, you need to adjust jobs manually so that they only run on the primary node within the AG group, but configuring SSRS in this way doesn’t require additional software outside of the SQL Server product family to get working.
Thanks,
John
Thanks for the comment, John. That actually works for native mode deployments of SSRS and leveraging the licensing you already paid for if you decide to use Availability Groups. There are tons of different possible configuration without necessarily breaking the bank. The only challenge there is that your application layer and your database layer are now on the same machine and the machine becomes the single point of failure for both. But, then, the business has to make that decision and balance the need to provide HA with the cost associated with the solution.