Zero-Cost Highly Available Reporting Services within a Failover Cluster

This solution is for anyone that has a SQL failover cluster and wishes Reporting Service to be highly available and have failover capabilities. The solution I implemented seems the most natural and how Reporting Services should work right out of the box – but doesn’t. This solution makes Reporting Services highly available (HA) on both cluster nodes with failover capability with zero cost (no additional servers or licenses). Although, it is an uncomplicated procedure there was much learned from the experience along with some interesting load distribution options.

I have implemented this solution on both:
Windows 2008R2 Failover Cluster with SQL Server 2008R2
Windows 2012R2 Failover Cluster with SQL Server 2012

These were active/passive clusters, but no reason why it wouldn’t also work with others.

NOTE: This solution provides high availability but low scalability. As soon as your Reporting Services demands exceed the power of your cluster server/node, you should consider a traditional scale-out deployment with multiple servers using Network Load Balancing (NLB).

Problem

As you know, if you install Reporting Services on a cluster, whenever the cluster fails over to the second node, Reporting Services will be down.  When SSRS is down the reporting URL will not be accessible and any missed scheduled report deliveries may have to be reprocessed manually after everything was failed back to the primary node. This probably doesn’t happen often, but when it does it is a pain and it’s frustrating that Reporting Services doesn’t work within a cluster like we expect. This happens because as Microsoft states SSRS is not cluster aware component of SQL. When you setup a cluster with SSRS and then install the second node SSRS will not get installed and if you attempt to install and add SSRS manually on the second node the installer will not allow you.

I was migrating an older SQL 2005 cluster to a brand new SQL 2012 cluster and took the opportunity to research and test a more highly available solution. I discovered all usual posts stating SSRS is not cluster-aware and you can’t make it HA without deploying multiple servers in a scale-out deployment. But I didn’t want to deploy multiple Report Servers and greatly increase our costs. I just wanted Reporting Services to work in my cluster how we expect it should.

Options

Selecting an optimum Reporting Server solution involves weighing costs (for SQL license and servers) along with performance demands and scalability. I found various solutions available to make Reporting Services HA, each with its own pros and cons. Depending upon your needs one of these other solutions may be a better fit for your environment.

HIGH-COST WITH HIGH SCALABILITY
Since SQL 2008, Microsoft has recommended a scale-out deployment – installing Reporting Service instances on separate servers creating a web farm sharing a common Reporting Services database (on the cluster) and adding network load-balancing. This is a common Microsoft recommendation that allows great scalability but requires SQL Enterprise edition for each reporting server you add, which can get expensive.

MEDIUM-COST WITH MEDIUM SCALABILITY
Some people recommend installing a new instance of SQL just for Reporting Services on the second cluster node and join it to the primary node in a scale-out deployment, which also requires an additional SQL Enterprise edition license. Using any combination of SQL Standard edition servers will not allow you share a common reporting database and will present an error that scale-out deployment is not supported (this is why we pay for the expensive version).

I also discovered some creative, less travelled, solutions that provide lower costs. One solution involved dividing the workload between two additional SQL Standard edition Reporting Servers by running only scheduled report distribution on one server and interactive report generation on another (done by setting options in RSReportServer.config on each server). With this solution you can dedicate the resources of two individual servers sized for your workload of your Reporting Services tasks. Another solution involves using two or more stand-alone SQL Standard reporting servers and using merge replication and a few tricks to keep the reporting databases in sync along with NLB. Neither of these solutions scale well beyond two servers.

ZER-COST WITH LOW SCALABILITY
This solution is the focus of this article as it most closely resembles how SSRS should work out-of-the-box on a cluster. This scenario involves installing SSRS on both nodes of the existing cluster and creates HA Reporting Services that runs automatically on whichever node becomes active. However, by default the SQL installer won’t let you install SSRS on the second node unless you follow the instructions below.

Automatic failover works with the reporting URL because, when the cluster fails over the active node will automatically take over virtual database name which is the same URL that reporting services will always point. For example, we have an active/passive cluster with computers named CLUSTER01 and CLUSTER02 and the virtual SQL database name of SQLCLUSTER. When CLUSTER01 is the active node and you access SSRS via the URL http://SQLCLUSTER/Reports the request is handled by the active node. During a failover the SQLCLUSTER virtual database name is moved to the new active node and SSRS on this node is now servicing all the reporting URL requests.

Automatic failover for the report scheduling engine works because we have joined the two nodes in a scale-out deployment sharing a common reporting database. We can choose to let Windows Failover Cluster manage the SSRS service failover or run the SSRS engine on both nodes. This option creates some interesting load distribution scenarios which can be found below under the section LOAD DISTRIBUTION OPTIONS.

Instructions

It is assumed you are either starting from a point where you have an installed SQL cluster that never had SSRS installed or installed SQL cluster with SSRS that you wish to add SSRS to the second node. In order to make Reporting Services HA, you need to install Reporting Services on both nodes and configure them to use the same reporting database using scale-out deployment.

ADD SSRS TO INSTANCE
We first need to add SSRS to the existing instance. However, when you attempt to run the wizard to install SSRS you run into a fatal cluster validation failure that stops you. According to Microsoft they do not support adding or removing features to a SQL failover cluster. If you forget to install a feature, Microsoft recommends you uninstall the SQL cluster instance and start over again or install the feature by creating a new instance, but no explanation is provided. Neither of these options are what we want.

Wizard Rule Check Error

• Rule “Existing clustered or cluster-prepared instance” failed. The instance specified for installation is already installed on clustered computer. To continue, select a different instance to cluster.
• StandaloneInstall_HasClusteredOrPreparedInstanceCheck – Checks if the selected instance name is already used by an existing cluster-prepared or clustered instance on any cluster node.

HOW TO INSTALL SSRS ON EXISTING CLUSTER
In order to install SSRS onto an existing cluster, we need to run SQL setup from the command line using a switch to bypass the cluster rule check that the wizard was complaining about.

Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install

1. Run SQL setup from the command line (as shown)
2. The wizard will appear and now you can proceed to perform the install adding the SSRS feature
3. Repeat this for each node that doesn’t already have SSRS installed

NOTE: This procedure has been performed by others for many years to add components to an installed cluster and I found no reported problems.

CONFIGURE REPORTING SERVICES
Now we need configure SSRS on both nodes and join them in a scale-out deployment. For the most part, the standard directions for configuring SSRS scale-out deployment are valid. However, note that this reference assumes you are installing SSRS on separate stand-alone non-clustered servers.

1. If SSRS is not yet configured on the FIRST node, open Reporting Services Configuration Manager and configure all the settings
2. Backup encryption key after FIRST node is working correctly
3. On the SECOND node, now open Reporting Services Configuration Manager and select the local computer name for that node
4. Select Database and choose an existing report server database and select the same ReportServer database configured on the first node (your shared cluster storage)
5. Configure the Report Server web service URL
6. Configure the Report Manager URL
7. Restore encryption key on SECOND node from backup on FIRST node (important)
8. Re-open the Reporting Services Configuration Manager connecting to the FIRST reporting server node and select Scale-out deployment where you should see the second node in here with a status of Waiting to Join (you must connect to the first report server to join additional report servers)
9. Select this server and click Add Server

SSRS Configuration Manager Scale-out deployment

CONFIGURE GENERIC SERVICE
This optional step allows the cluster manager to ensure that SSRS is only running on the currently active node. If you wish to run SSRS concurrently on both nodes see section on load distribution options and important notes on SQL licensing.

Within Cluster Failover Manager, create a Generic Service by right-clicking Role select Create Empty Role

1. Right-click the new role and select Add Resource a Generic Service and scroll down to select SQL Server Reporting Services
2. Right-click the newly created service and select More Actions à Assign to Another Role to move this resource to your existing SQL Role.
3. Select your existing SQL Role and right-click the newly moved SSRS resource and select properties, go to the dependencies tab and add SQL Server (to ensure this service isn’t started until after the database is available)

Cluster Failover Manager should look something like this:

VERIFY

1. Open the report services URL using the SQL virtual database name in your client browser and verify it works
2. Force a failover of SQL to the other node
3. Refresh your browser to test that the reporting URL still works using the same URL and verify that the second node is now servicing all requests
4. Confirm scheduled report delivery works on both nodes
5. If you configured the cluster to manage SSRS as a Generic Service, verify the service is running with manual startup and was successfully started on the active node and stopped on the other node

LOAD DISTRIBUTION OPTIONS

• If you skip the configuring generic service step, you can optionally allow SSRS to run automatically at startup on both nodes. With this scenario all SSRS URL requests will be directed only to the currently active node but all scheduled report processing will be processed by both nodes.
• When both nodes are running SSRS, you may think that only the active node will service all scheduled report request because it is the only node running SQL Agent to process the scheduled jobs. However this is not true. The way SSRS works when the SQL Job schedule kicks off, it will create a record in the Event table and let the Reporting Service pick up these requests and process them. In a scale-out deployment any SSRS server can process the requests offering a default form of load distribution.
• Adding a NLB would allow both interactive reporting and scheduled report processing to be distributed.
• Running any SQL services concurrently on both nodes may require a license for the passive node (see important notes).
• If running SSRS concurrently on both nodes causes you any difficulty then you can configure the cluster manager to failover SSRS, ensuring it is only running on the currently active node (see Configuring Generic Service).

IMPORTANT NOTES

Interactive Reporting:

• When both nodes are running SSRS, only the active node will service the URL request WHEN users are accessing URL via the SQL virtual name (recommended).
• If users access the URL using the node/computer name the SSRS running on that node will service the request (as long as the service is running on that node). This can be a form of ad hoc load balancing, however having users pointing directly to one node will make the URL inaccessible if that node goes down (not recommended).

Service Accounts:

• The database credentials on both nodes must use a SQL login or domain account so Reporting Service can always access the database on the remote node.
• The Reporting Server service can be run with either domain or built in network service account, however if you run into Kerberos authentication issues at the URL see this document.

Troubleshooting:

• If the two scale-out nodes are not identically configured (database or service accounts, email config, etc.) this might result in intermittent failures with report deliveries and the true cause may not be obvious. To troubleshoot subscription problems, you can verify which node is processing requests by querying the ExecutionLog and note the InstanceName that processed the request. You can also exam the SSRS Trace Log files for authentication or other errors.

SQL Licensing

• Licensing is an ever changing subject so you should consult with your license provider for your particular situation
• Normally if you have an active/passive cluster, you only need a license for the active server, however, running any SQL services on both nodes may require a license for the passive node
• SQL Server 2014 Licensing Changes  state that cluster passive nodes must be covered under SA

HELPFUL RESOURCES

Scale Out SQL Server 2008 R2 Reporting Services Farm using NLB
https://www.mssqltips.com/sqlservertip/2335/scale-out-sql-server-2008-r2-reporting-services-farm-using-nlb-part-1/

How to: Configure a Report Server on a Network Load Balancing Cluster
https://msdn.microsoft.com/en-us/library/cc281307(v=sql.105).aspx

How to setup SSRS high availability with Standard Edition
http://pietervanhove.azurewebsites.net/?p=513

Configure a Native Mode Report Server Scale-Out Deployment
https://msdn.microsoft.com/en-us/library/ms159114(v=sql.110).aspx