Configuring Reporting Services SQL Server 2005 in a failover cluster Windows Server 2003

Question

I have the following scenario:

Two machines, say NODE1 and NODE2, have Windows Server 2003 installed in a failover cluster, say CLUSTER. On this cluster SQL Server 2005 was installed successfully, say the virtual server is DATABASE. Then, SSRS 2005 was installed on NODE1.

The problem is that the application that's going to use this setup assumes that the reporting services runs on http://DATABASE/reports but, given the only possible installation (as far as I could tell) you can only get http://NODE1/reports.

The application cannot be modified, what would the proper way be?

I'm thinking about setting IIS up on the cluster (and have it run as the virtual database server) and install the reports and reportserver webapps on it, making the problem to go away, but I'm not sure if this is really feasible nor if this is the best option.

Another option I've thought about is to install a reverse proxy, but then again I'm not sure about how to make it answer on http://DATABASE/reports without at least having to install IIS first anyway (and take additional steps.)

Advice?

Answer 1

The thing I didn't realize yesterday is that DATABASE also resolves to the currently active node, not only CLUSTER:

Say DATABASE is the cluster name and NODE1 and NODE2 are the nodes, then DATABASE resolves to the currently active node, so if NODE1 is the active node, then DATABASE resolves to NODE1.

So, the thing to do is install reporting services in both nodes having them both pointing to the same ReportServer database in the database cluster.

Let's assume you already have the cluster and have SQL Server installed in clustered mode already (requirements.) Always use the same version (same SP between reporting services in every node.)

Then, these are the steps to follow:

1. Connect to the active node (NODE1) and install and configure reporting services on it, creating a ReportServer database on DATABASE (via the Database Setup, provide proper credentials to the DB), you then initialize the database in the configuration as well.

2. Then connect to, install and configure reporting services on the passive node (NODE2), set up the database to the existing database on DATABASE (the one you already created, providing the same credentials to connect to the DB), but DO NOT INITIALIZE IT NOW (ON THE PASSIVE NODE).

3. Connect back to the active node and run the reporting services configuration again, now in the initialization you'll see the passive node appearing, you can then initialize it from there.

Then you'll have two instances of Reporting services, one on each node, and pointing to the same database. And now, when the nodes failover you'll have the other instance working.

The only pain I've found so far is that if you don't expose the logs nor have them accessible via \DATABASE\C$\Program Files\MSSQL...\ReportServer\Logs, you have to remember which one's the active node to go see the logs there.

This is called "scaling out Reporting Services" and is supported only on Enterprise edition (plus developer and evaluation)

References:

http://technet.microsoft.com/en-us/library/ms156453(SQL.90).aspx

http://technet.microsoft.com/en-us/library/ms159114(SQL.90).aspx

To upgrade both report servers: http://technet.microsoft.com/en-us/library/ms143735(SQL.90).aspx