CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Reporting Services, Scale Out and Clusters…

Reporting Services, Scale Out and Clusters…

  • Comments 3

Every once in a while, I get asked the question about deploying Reporting Services on a Cluster. Usually it is tied to a scale out deployment, sometimes it is not. I just was asked the question again by an engineer in our group. So, I figured I should put this out there to have some reference.

For starters, in regards to Scale Out Deployment with Reporting Services, there are two references that I recommend you read. One is in Books Online and the other is a Scale Out Best Practices doc from the SQL CAT team.

Configuring Reporting Services for Scale-Out Deployment (BOL)

Reporting Services Scale-Out Deployment Best Practices (SQL CAT)

However, neither really touches on installing RS alongside of a SQL Cluster. There are references to an NLB Cluster but that is something different.

The Scenario is that you want to install a SQL Cluster (we’ll say 2 node for the purposes of this example), and you install Reporting Services along with SQL for that instance. The question that usually comes to me is something of this nature: Can I install Reporting Services as part of my cluster, and what are recommendations for setting something like that up?

Let me start with the recommendation part as I think that is the more important piece of this. Please remember that this is my personal recommendation based on experience and dealing with these types of issues. Also, I have heard this from some other source, but in general these are my thoughts.

Recommendation:

Do I recommend that you install Reporting Services as part of a SQL Cluster install? No, I do not.

Think about the scenario. Why are you setting up a cluster to being with? It is probably because you want to make sure that your SQL Database Server is up and running reliably. My general thought on that are, if that is your goal, why are you introducing another service that will now be competing for resources on the same box. Who gets precedence?

When I think about setting up a SQL Cluster, I don’t want anything else running on that box. I realize that for some deployments, that may not be a reality because of resource constraints (money, hardware, licenses, etc…) which leads into the technical piece down below. So, if you have to go this route, be aware of the competing resources aspect of this and plan accordingly. Know how you are going to deal with issues when SQL decides to fail over, or of one node crashes and now a single machine is hosting all SQL Traffic and RS Traffic.

Technical:

From a technical aspect, we will let you configure an RS Instance that is part of the same Instance that the SQL Cluster is a part of. In my case, I set this up with an instance name of SQLALL. This included SQL, RS and AS. SQL and AS were clustered.

The biggest thing to realize when you do this is that RS itself is not cluster aware. RS at that point is just a standalone instance even though it resides on a cluster node. From a failover cluster perspective, the cluster has no knowledge of Reporting Services.

clip_image001

Notice that from a cluster perspective, we can see that we have AS, SQL and SQL Agent. Reporting Services is not listed.

From Reporting Services Configuration Manager, we can see that it is there and part of the same instance:

clip_image002

We can also see that when you install Reporting Services in this type of setup that Reporting Services will be running on both Cluster Nodes, even though SQL is only Active on one node. This is because SQL is controlled by the Failover Clustering feature of Windows.

clip_image003

When a Failover occurs, SQL will move from Node 1 to Node 2, but RS will not change its status at all. In this case, the load for Reporting Services should be balanced from the Load Balancer.


clip_image001[5]

However, if Node 1 goes down all together, all Reporting Services traffic will be pushed to Node 2 along with the SQL Traffic. This could cause a resource issue on Node 2 as you may start receiving unexpected load on the one machine. The Load Balancer will redirect all traffic to Node 2 as this is the only node available.

clip_image002[5]

Summary:

The big thing to take into account when you go to install Reporting Services on Clustered Nodes is to think about your load and available resources. Understand what your goals are and plan accordingly taking all scenarios into account to know how you will react. It is technically possible to install Reporting Services onto a Cluster Node, but it will not be a clustered service and will compete for resources with SQL Server.

Adam W. Saxton | Microsoft SQL Server Escalation Services
http://twitter.com/awsaxton

Leave a Comment
  • Please add 4 and 4 and type the answer here:
  • Post
  • This is yet another thing that bugs me about the SQL 2008 installer.  Why give the option to install Reporting Services when selecting the "New SQL Server failover cluster installation" option if Reporting Services isn't even cluster aware?  That's just plain confusing and completely unnecessary.  The main advantage of making the Reporting Server service cluster aware is so you don't have to deal with the extra step of load-balancing if your only goal is redundancy and not scalability... which in all honestly is the main driver behind failover clustering to begin with.

  • Can you install the NLB Cluster on the 2 nodes of your Failover Cluster or will that cuase the Failover Cluster to not work?

  • Sam - you cannot create an NLB cluster with any nodes that participate in the failover cluster instance.

    I've been toying around with this, as we have exactly this type of requirement, and believe I've come up with a workaround to "Cluster" the Reporting services instance.  I've put cluster in brackets as it's still two seperate instances of SRS on each failover cluster node, however instead of leveraging an external load balancer, it's leveraging the failover cluster services for the network traffic (Seperate resource group, IP Address, and Name).  Aside from the additional failover cluster configuration, the back end config for reporting services seems to be similar to that of configuring an NLB cluster - from a reporting service perspective both cluster nodes are present in the scale out deployment.

    So far my testing has produced pretty reliable results, however I can't speak to the supportability of this method from MS....

Page 1 of 1 (3 items)