Ignite for SQL Server is designed specifically to solve and prevent the toughest performance problems. It's not a general purpose monitor - server health tools do that. Ignite goes where conventional tools cannot. With easy to understand graphical displays, and automatically emailed graphic reports, Ignite is the one tool that DBAs, developers, and managers can use to collaborate.
No Agents. No Tracing. Less than 1% load on monitored servers makes Ignite the lightest high performance monitoring tool available.
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.
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.
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.
Saturday, October 06, 2012 - 1:37:43 AM - Manvendra Deo Singh
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.
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.
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.
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.
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.