Installation for SQL Reporting Services on a Windows 2003 Cluster

By Tom Lodermeier and Zach Mattson

Introduction

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

Conclusions

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!