The Right Database Monitoring Tools Make All the Difference
Tuesday, June 26, 2018 - click here to learn more and to register
I recently had a request to install SQL Server Reporting Services (SSRS) on to one of our existing SQL Server clusters. As you may know SSRS is not cluster aware, so adding this feature is not very straight forward. In this tip I will explain step by step how to add SSRS onto an existing clustered environment.
Recently I deployed a SQL Server Multi-Instance cluster in an Active-Passive clustered environment with just the SQL Server database engine. After a few weeks, the application team requested I install SSRS as well to use SQL Server Reporting Services functionality on the same SQL Server instance. As you may know, SSRS is not a cluster aware feature, so the install requires some extra steps. Also, if you want high availability for SQL Server Reporting Services you should deploy it in a farm which provides both availability and load balancing.
Adding SSRS on an installed SQL Server Cluster Instance is not so straight forward. When we try to add SSRS on an existing SQL Server cluster instance, the install does not allow us to proceed after the Installation rule check page because one of the rules fails during the rule check process. One solution is to install SSRS as a separate instance, but since this configuration was already a multi-instance cluster I was not in favor of increasing the number of instances by installing SSRS as a separate instance. Below I will explain the step by step method to configure SSRS on an existing SQL Server cluster instance without deploying a farm.
To add/install SSRS on an installed SQL Server Instance, we need to run setup again on each cluster node. First run SQL Server setup on your active node (Node 1). Follow all necessary steps in the setup windows. Make sure to run this setup to add SSRS on your existing instance rather than creating a new instance. Choose Reporting Services on the feature selection page of the setup window. After we start the install, we will get an error because one of the rules will fail as shown below.
When you get the above error, you cannot proceed with the install because the "Next" button will be disabled due to this failed rule.
To get a detailed report about this failed rule, click on "view detailed report" shown in the above screenshot and you will get details about this rule as shown below.
As we already saw that we cannot add SSRS to an existing SQL Server cluster, the solution is to run setup and skip the installation rules to install SQL Server Reporting Services in an existing clustered instance.
Run the below command at the Windows command prompt to start SQL Server setup on the active node. Make sure to run this command after changing the root directory of the command prompt to the location where you have placed the SQL Server setup files.
Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install
Once you press enter to run the command, the SQL Server product version will display on the command prompt, as shown above, and an installation window named "Program Compatibility Assistant" will appear. Now click on "Run program" to proceed with this installation.
Now follow the same process which you normally do in an installation. Again choose the existing instance to add SSRS and select Reporting Services in the feature selection page which we need to install.
Now this time you can see the installation rule check passes without an error, because we skipped the installation rule process to make this installation possible.
Here we can see the "Next" button is enabled, so click on Next to install SQL Server Reporting services on the active node.
Once you are done with the installation on the active node (Node 1), follow the same process on each of the other nodes in the cluster.
Now that each of the nodes has Reporting Services installed, it is time to configure Reporting Services on each node using "Reporting Services Configuration Manager" which can be found under configuration tools.
Here the configurations would be the same as you would have for any Reporting Services configuration. One thing will change though, we will use the SQL Server failover cluster network/virtual name while making a connection to the report database server. If we use node names in place of the failover cluster network name, the report server will be unable to connect to the report server database in case failover occurs.
Configure Reporting Services on each node in the same way with SQL Server failover cluster network name otherwise you will get the below Report Manager error after failover when an instance is online from another node.
Now you are done with your SSRS configuration. Go ahead and launch Report Manager to check whether SSRS is configured properly. You can also test your Report Manager accessibility after failover and failback to verify whether it's providing the necessary failover functionality.
- Implement this solution if there are requirements to add SSRS on to an existing clustered instance.
- Read more tips on SQL Server Clustering and SQL Server Reporting Services Administration
Last Update: 2012-10-03
About the author
View all my tips