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
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)
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.
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.
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
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
Happy “no failure” Reporting!