A way of achieving SQL Server 2008 High Availability is installing SQL Server on top of Windows Server 2008 failover cluster. Only Database Engine Services and Analysis Services are cluster aware while SQL Server 2008 Reporting Services and shared components (Integration Services, Management Studio, Business Intelligence Development Studio etc.) are not.
According to available documentation installing Reporting Services in already clustered SQL Server instance is not supported. If you want the Reporting Services on the other nodes you must install them in separate SQL Server instances. If you want the highly available Reporting Services they should be deployed in a farm which provides both availability and load balancing. For details see Planning and Architecture (Reporting Services)
What I needed was High Availability, but I didn’t have additional servers to create the farm (with this scenario you need at least 2 additional servers for farm or NLB). Also I didn’t want to manage multiple SQL Server instances side by side. Multiple instances mean a lot of administration because each instance must be handled separately. There are scenarios when you should or want to use multiple instances, but not in my case. Use of existing Windows/SQL failover cluster seemed logical. Described solution is tested and deployed using both SQL Server 2008 and R2 Enterprise Edition
. Standard Edition of SQL Server does not support Scale-Out deployment. For details see Planning for Scale-Out Deployment
Setup of SQL Server 2008 failover clustering has changed in this release. To install or upgrade a SQL Server failover cluster, you must run the Setup on each node of the failover cluster. You create a failover cluster by running Setup on first node and selecting all desired components. As I already mentioned only the Database Engine and Analysis Services support failover clustering, while other components run as a stand-alone feature without failover capability. Shared components are installed on all other nodes while adding those nodes to the existing failover cluster. To install the Reporting Services on other nodes, after adding those nodes to the existing SQL Server failover cluster, you must run setup once again.
Running setup on the second node and selecting the Reporting Services caused setup to fail with following error:
Checks if the selected instance name is already used by an existing
cluster-prepared or clustered instance on any cluster node.Failed – The instance selected for installation is already installed and
clustered on computer . To continue, select a different instance to
The Solution is to run setup and skip this check to install the Reporting Services in already clustered instance.
Run the following command at the command prompt to start setup and add Reporting Services on other nodes:
Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install
This will run Setup in full interaction GUI mode while skipping cluster instance check. Just follow setup to install Reporting Services in SQL Server clustered instance.
General steps for installing SQL Server failover cluster with Reporting Services on each node are:
1. Create SQL Server failover cluster by installing first node. Start setup and select option “New SQL Server failover cluster installation”. Make sure you select Reporting Services while installing first node.
2. Install SQL Server failover clustering on other nodes. Start setup and select option “Add node to a SQL Server failover cluster”. You can not chose any features. You will add Reporting Services later.
3. Install Reporting Services on all nodes except first node in SQL Server clustered instance. Run setup from command prompt using command mentioned above.
4. Configure Reporting Services on each node. Run “Reporting Services Configuration tool” on each node.
After installing Reporting Services on failover cluster please keep in mind:
- Reporting Services running on an Active-Passive cluster handle requests on each cluster node on which the service is deployed.
- Report server must be configured to use SQL failover cluster virtual name to connect to the report server database. This is because it is hosted on a SQL Server that is part of a failover cluster. If not, the report server will be unable to connect to the report server database if a failover occurs.
This solution provides the highly available Reporting Services with default SQL server instance and uses already deployed hardware. It is not substitute for a true Scale-out deployment of the Reporting Services, but a way of achieving high availability (we just used the existing high availability platform). Scale-out enables you to increase the number of users who can concurrently access/invoke reports and improves the availability of the report server.
Obtained from this link