How to add Reporting Services to an existing SQL Server Clustered Instance

By:   |   Comments (30)   |   Related: > Reporting Services Installation


Problem

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.

Solution

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.

Step 1

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.

Installation Rule Check Error

Step 2

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.

Get Detailed report about this Installation Rule

Step 3

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

CMD to run sql server setup through cmd promot

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.

Installation Windows

Step 4

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.

Installation Rule successful

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.

Step 5

Here we can see the "Next" button is enabled, so click on Next to install SQL Server Reporting services on the active node.

Step 6

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.

Step 7

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.

Report Manager Error

Step 8

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.

Launch Report Manager
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, March 6, 2019 - 3:41:59 PM - Tom Back To Top (79201)

This works for me for the most part until I reach the installation portion.  It'll start the installation and then stop with a error stating:

SQL Server Setup has encountered the following error:

Instance name "xxxxxxx" is already in use. To continue, specify a unique instance name.

Error code 0x858C0014

Wednesday, October 10, 2018 - 7:50:42 AM - B Moyno Back To Top (77913)

This tip worked perfectly previously, but unfortunately does not appear to work for SQL Server 2016. All documentation appears to suggest you create a 2nd instance of SQL Server with SSRS installed on the second node of the cluster in addtition to the default instance.

You then have to set up an NLB to manage interaction between the 2 SSRS nodes. This is easy to type on a document but not for those who have no networking experience and have to implement this solution.

We've had SSRS working on a 2 node SQL Server 2008 R2 cluster for years (no failover support as such but configured so that it didn't matter what node was active).

It's kinda madness to have to say that Microsoft no longer support this sensible approach and now we have to install additonal instances (which have to be managed of course) just to support SSRS on both cluster nodes! Do Microsoft want people to move away from SSRS as fast as possible?

Wednesday, April 4, 2018 - 1:07:06 PM - Danny Back To Top (75609)

fantastic solution, thank you. 

 

Wednesday, February 14, 2018 - 3:34:23 AM - Bodo Back To Top (75210)

This is no longer working in SQL 2016. Someone got a solution for that version? 

Monday, October 16, 2017 - 10:56:50 AM - dave hilling Back To Top (68437)

 I would add a note to this that you will need to export the encryption key from the first node and import it into srss in subsequent nodes. At least thats what we had to do. 

 

Thursday, October 12, 2017 - 10:21:26 AM - Aleks Back To Top (67239)

Getting an issue on sql server 2016. "Instance name 'MSSQLServer' is already in use. 

 

Monday, September 25, 2017 - 12:35:23 PM - Siddhesh Back To Top (66595)

 

Hi.. Anyone resolve this issue in SQL 2016. please give solution.

 

 

Wednesday, September 13, 2017 - 4:27:43 AM - Suresh Back To Top (66227)

 

 Hi Manvendra,

Question, I tried what you have mentioned in the article but i was expecting SSRS service would be added to Failover Cluster and when ever the SQL Cluster services move then SSRS service also will be moved. But after install i see SQL Cluster name as web link instead of indiviual node names. And for SSRS to be installed on Cluster (install SSRS on both the nodes as if doing standlaone install) we need ENT edition (for FARM or scale out deployment). So is the above setup as same setup which we do for FARM? or different?

Wednesday, May 24, 2017 - 4:30:41 PM - santosh bagwe Back To Top (56019)

Thank you so much for your help.... 

 

Tuesday, April 11, 2017 - 6:03:56 AM - chaitanya Back To Top (54646)

 

 

Hello all,

I have made two node cluster and i have also configure the reporting services on both the servers.
When one node fails the application and Database is getting shifted to other node and i can use my application but my reporting service is not getting shifted.

My reports are not opening on second node when one node fails its giving me following error.

"The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. The request failed with HTTP status 404: Not Found solution"



Please provide me the detailed solution how to configure the reporting services in Cluster environment.

Please treat very urgent..

Thursday, May 19, 2016 - 6:49:51 PM - Ann Back To Top (41521)

 

 I can't get this to work.  I used the sql server network name in every field where the server name is in the configuration on both nodes and connected to the ReportServer database using the sql server network name.  When the database is on node 1 it works, but when the database is on node 2 I get the 'Report Server is not Initialized Error'.  I have been over my configurations many times and they are configured exactly the same.  Please help, any ideas are appreciated.  Thanks!

Thursday, May 12, 2016 - 2:24:48 PM - Frank Back To Top (41473)

   This is really helpful, thanks.

 

Sunday, December 13, 2015 - 4:47:52 AM - Mohammed Faisal Back To Top (40242)

Dear All,

I have sql cluster server on windows 2012 server in default instance there is multiple databases with failover cluster.

Now i want to add a new SCOM database in New Instance so kindly provide me the solution please with the steps and proper procedure.

 

Thanks!

Friday, June 19, 2015 - 1:11:18 AM - Sunil Back To Top (37957)

Wonderful !!,
this applies for sql 2012 too

Got confused with Rule Display name for 2012

was looking for the rule name for 2012, rule name "StandaloneInstall_HasClusteredOrPreparedInstanceCheck" was the same - 

 

 

Sunday, March 1, 2015 - 9:15:19 PM - Govindarajan Back To Top (36393)

Hi Manvendra,

 

Great article. 

I installed SSRS on a SQL 2012 Ent Edition cluster environment.  When I failed over to another node, the report server service is not working.  It is throwing error "not initialized".  I checked the settings in report services configuration manager.  It is fine.  I also get the login failed entries in sql server log for the reporting services from the server i.e. Login failed for computer01$.

 

Kindly suggest how to resolve this.

 

Regards

Govind

Tuesday, February 24, 2015 - 4:06:45 AM - ashraf mahadin Back To Top (36328)

Thank you very very much, you saved my day.

Thursday, July 31, 2014 - 6:13:39 AM - Venkata Peri Back To Top (33955)

Hi Manvendra,

 

great article, even though if we pay licenses on both nodes, SSRS Cluster set up is awesome, learned why MS is not giving this option due to limitations. Its really great if MS could have provided NLB in Cluster for SSRS.

Friday, July 25, 2014 - 11:15:14 AM - Tim Back To Top (32885)

this was very helpful Manvendra, thank you for sharing with the world

Thursday, July 24, 2014 - 8:13:52 PM - mak Back To Top (32871)

Do you need IIS for this? I happened to have problems with node 2 to use the same ReportServer DB, it procs it states the The report server installation is not initialized. Thank you for the reply. I really need this today.

Friday, February 14, 2014 - 6:12:12 AM - Govind Back To Top (29447)

Hi,

We have a cluster with three instances.  I need to install SSRS for all three instances.  Is it possible to configure SSRS on cluster for three instances ?  Will it conflict or is it instance based ?

Await your inputs asap.  Thanks in advance.

Regards

Govind

Tuesday, October 15, 2013 - 3:59:48 AM - Kastella Back To Top (27151)

Hi,

it doesn't work... I have a Sql 2012 ent. ed. cluster but even if I use the cluster name to configure RS on both node, when it switch I get "The report server installation is not initialized".

 

Monday, September 23, 2013 - 8:52:24 PM - Chris Back To Top (26905)

All,

 

I've followed this article to the letter and have noticed an issue with the SSRS configuration. I ran both setups on node1 then node2. now, node2 is the only node that will see the SSRS instance or I get 404 errors when I fail over to node1 and expect to see SSRS.

 

SQL Server Std edition 2012 is what I am using with the default MSSQLSERVER instance ID. It shows that node2 is part of a scaled out deployment and is in Native mode. I'm not a hard core SQL person by any means. Any help is appreciated.

 

Friday, April 19, 2013 - 4:08:42 PM - Razmik Back To Top (23449)

Hi Manvendra. I have been reading multiple articles similar to yours. I like that many people try to push thet technical boundries and think out side of many boxes, but just like a few others who commented here I also have concerns about licensing, load balancing and upgrade.

1- If you install the SSRS on two or more nodes (local install referencing a clustered database) the SSRS Service will be running on two or more servers. You would need to license these nodes separately from the clustered database.

2- If you install the SSRS on two or more nodes but want to run only one SSRS server at a time, you need to do some "hack" work to make the local SSRS service to depend on a resource within the cluster's database group. This way when the clustered database fails over on to a node, that node alone would be starting the local SSRS service. The problems here are that SSRS is not going to be supported by Microsoft and you only have one SSRS server at a time (not a load balanced). This maybe OK for a development or testing lab, but I'm not sure if it is OK for a production environment.

My main point however is not so much on the technology or your method. Knowing that you must license the nodes (or servers) that run SSRS separately from the database server, by installing the SSRS on cluster nodes you are only avoiding to setup one or two Web servers in the environment (the web servers could be virtual servers with small footprints). I'm not sure if avoiding the two web servers justifies installing an unsupported reporting system.

Thank you for posting interesting ideas

Thursday, April 4, 2013 - 11:52:38 PM - SA Back To Top (23178)

I believe the guidance given on licensing is incorrect. Once you install SSRS on both nodes and the SSRS service is running on both nodes, you have just created two instances on two separate servers and they are both active (farm deployment). This requires two licenses.

I also don't understand why going through this trouble is necessary. It's much easier to set up SSRS on the nodes in its own instance. Because SSRS is not cluster aware, you have to consider what will happen when the cluster needs to be upgraded. Will it be supported, etc.

Thursday, December 27, 2012 - 3:29:34 AM - Manvendra Back To Top (21146)

@akter,

Yes because SSRS in cluster is locally installed because its not cluster aware. You can stop this service on passive node and restart it whenever any failover happen.

@J Richards,

No, cluster resources will not be failover to any node before adding any node to the cluster setup.

Wednesday, December 26, 2012 - 11:34:47 AM - J Richards Back To Top (21138)

 

Step 6 Reads:

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.

Does this mean that the Cluster Resources should be failed over to each of the other nodes in the cluster before running SETUP to add Reporting Services to an existing node?

Thursday, December 13, 2012 - 11:54:45 AM - akter Back To Top (20940)

HI,

 

Akter I installed SSRS on existing Active passive sql cluster , it shows me that Reporting sercie are started on both cluster Node.

why is that?

Saturday, October 6, 2012 - 1:37:43 AM - Manvendra Deo Singh Back To Top (19807)

Thx Ignacio,

Extra licenses will not be required because this installation will be done on existing Instance, which is already a part of cluster and one node will be in passive state always.

 

Jason,

I have fully tested this on Enterprise edition. In case of failover nothing will be impacted because you will be using SQL Server network name during report manager configuration so whichever node will be up the network name will redirect to that active node.

Wednesday, October 3, 2012 - 11:47:56 AM - Jason Back To Top (19781)

Have you fully tested this?  Are you running SQL Server Enterprise Edition or Standard Edition?  The reason I ask, is that you can't have two SSRS installs connecting to the same Reporting Services database unless you're using Enterprise Edition.  I'm wondering what happens when you failover from one node to the other.  SSRS should blow up with an invalid encryption key.

Wednesday, October 3, 2012 - 10:12:23 AM - Ignacio Salom Back To Top (19776)

Great article. You mentioned that the installation was done on an Active/ Passive Cluster. How do you do with the licenses as the SSRS will be running on both nodes. Do you need extra licenses?














get free sql tips
agree to terms