SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Clustering Reporting Services

By Zach Mattson,

Installation for SQL Reporting Services on a Windows 2003 Cluster

By Tom Lodermeier and Zach Mattson


Installing SQL Reporting Services on a single machine is a relatively easy task. There are several excellent articles that take you step-by-step through the process of installing Reporting Services on a single machine. However, what happens when that reporting server picks a work day to call in sick? Will you be there to setup a new machine and get your reports up and running? This is the reason that Microsoft has provided us with a failover cluster.

Setting up RS to handle failure is a bit more complicated than a single machine install. The generally accepted and documented method for handling failure is to utilize a load balanced web farm to house the reporting service (IIS and ASP.NET) and keep the reporting databases on a failover cluster. The beauty of the generally accepted solution is that you have to buy additional SQL licenses for your web farm servers (it’s beautiful if you are a Microsoft stockholder). Note: Microsoft licensing can be complex, the reporting services licensing is explained here. Given our division’s tightening budget, and our desire to forge new frontiers in undocumented processes, we wanted to install the reporting services (IIS, ASP.NET) and the databases on the same cluster. I don’t recommend this approach if you are using URL access to your reports from the internet, as your database could be exposed. We are using SOAP to get our reports in PDF format to our application server so the cluster is protected from the outside world.

Tools and Setup

  • SQL Server 2000 SP4
  • Windows 2003 Server
  • SQL Reporting Services 2000
  • Windows 2003 Enterprise Cluster
  • SAN space for the cluster
  • MSDTS service must be started
  • MDAC 2.8 SP2 (installed with Windows 2003 SP1)
  • IIS with ASP.NET (for the reporting service)
  • .Net Framework 1.1 (installed with Windows 2003 SP1)

The Install

Installation procedures for the cluster are well documented. Essentially you install the OS on the active and passive nodes; then setup the cluster administrator on the virtual server. After this has been completed, install SQL Server and get your database files setup on the SAN. Once your SQL Server has been installed, go to the active node and start up the Reporting Services installation executable. The installation is straightforward on the active node. Be sure to put the databases on your clustered SQL Server and files on the SAN drive.

Note: For clarity, the “active node” is the first node you install on the cluster, the subsequent node, is referred to as the “passive node”. Typically, in a clustered environment, the node that is handling the work is the active node, and the node standing by is the passive node.

To install the passive node’s reporting service, first fail the cluster (simply rebooting the active node will initiate the failover by the cluster) so the passive node becomes the active node (thus allowing the node to see the SQL Server and SAN drives). The passive node’s installation is pretty much the same as the active node. The caveat is upon pointing the reporting databases to the clustered SQL Server, the Web Farm Setup screen appears (figure 1 below). The key to making this installation successful is entering the active node’s machine name (the first node you installed in the cluster) where it says Report Server. The reason why you need the name of the active node is due to the encryption of the DSN name in the RSReportServer.config and the matching key that resides in the ReportServer database. By giving the active node’s name, the passive node can recognize the reporting databases located on the clustered SQL Server. The rest of the installation is normal.

Figure 1


The benefits of this installation are that you have failover built into your reporting solution and do not have to incur the costs of additional licenses setup in the web farm method. In our tests, the failover only took about 15 seconds to bring up the passive node and start serving reports. It sure beats hurrying an installation of Reporting Services under heavy pressure from management and users!

There are a few downfalls of our method. First, you won’t see the performance or scalability of a load balanced web farm. Second, the time delay that it takes for the failover to switch will also not exist in a web farm solution and finally, your database cluster will have additional workload serving the reports so database performance could decrease.

The failover cluster all-in-one solution may be just the ticket for the small to mid-size shops that have a reasonable number of reports and users.

Happy “no failure” Reporting!

Total article views: 11229 | Views in the last 30 days: 5
Related Articles

A Guide to Installing SQL Server 2008 R2 Reporting Services

This document shows how to install a Microsoft SQL Server 2008 R2 Reporting Services instance.


Reporting Services Installation Problem - help requested

SQL Server 2005 Reporting Services installation help


Install Reporting Services after Engine

Installing reporting services after installing sql


Installing reporting services on SQL server default instance

Installing reporting services on SQL server default instance


Configure Reporting Service in a SQL Server 2005 Cluster

How to Configure Reporting Service in a Active/Passive SQL Server 2005 Cluster?