Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availibility

I was working on project, where there is only a one cluster box which is being used for Database Services to meet high availability requirements.

Customer, understood some reporting Services feature so asked developers to build some required reports, which was being done. Now things need to be pushed to production so a requirement for installing and configuring the Reporting Services in a Clustered environment to meet the high availability requirements.

As per the customer, things were pretty straight forward as he assumed that Reporting Services are also cluster aware as SQL Server Database engine. So customer asked me to asked a install reporting Services on the clustered environment to meet the high availability, one of major business requirement.

When I shared the activities / project plan to customer, he was shocked to see one of the design slide, which I pasted below, he never thought that there will be a Scale OUT solution to meet the high availability requirement.

Customer was not aware that “Reporting Services is not cluster-aware”, YES SQL Server Reporting Services are not cluster aware.

So let me summaries the entire activity / project with detailed description to install and configure reporting services on a clustered environment to meet high availability requirement.

In short a reporting services should be available as one name / url for user to access report server, regardless of fact on which node it is running, a kind of virtual url for report server

Broad Steps / Activites

1. Install first Reporting Services Instance on NODE 1 of clustered environment, choose Install but do not configure server option  during Report Server Installation Options page
2. Configure Reporting Services using Reporting Services Configuration tool
   • Report Server Web service URL,
   • Report Manager URL,
   • Report Server database
   • Report server temp database etc.
3. Install second Reporting Services Instance on NODE 2 of clustered environment, choose Install but do not configure server option  during Report Server Installation Options page.
4. Configure Reporting Services using Reporting Services Configuration tool and Connect report server to the same database you used for the first report server instance. (Instead of creating a new database, opt for Choose an existing report server database)
5. Configure the Report Server Web service URL, which would be definitely a different than the first as this would be set up on NODE 2
6. Join second report server instance to the scale-out deployment.
   • Open the Reporting Services Configuration tool, and reconnect to the first report server instance which is running on NODE 1 and click on Scale-out Deployment to open the Scale-out Deployment page.
   • You should see two entries, one for each report server instance that is connected to the report server database. The first report server instance should be joined. The second report server should display a status of "Waiting to join". If you do not see similar entries for your deployment, verify you are connected to the first report server that is already configured and initialized to use the report server database.
   • On the Scale-out Deployment page, select the report server instance that is waiting to join the deployment, and click Add Server.
7. Till now we have configured the Reporting Services Scale out solution and both individual reporting services instances access common reporting services database. As this point, we should be verify that both reporting services instances are working fine.
   • in my case, was able to verify the first instance with url “http://NODE1/reportserver” and
     “http://NODE2/reportserver”, second reporting instance
   • Now I need to do a configuration that a single url can be used to access services from the both url.
8. Configure view state validation by editing the Web.config file for Report Manager for both report server instance.
   • Generate a validation key. You can use the autogenerate functionality provided by the .NET Framework or you can create the key some other way. Do not use the AutoGenerate option when setting the validationKey attribute.
   • Generate a decryption key. For the decryption key, you can create an explicit value or set decryptionKey to AutoGenerate
   • Open the Web.config file for Report Manager and set the <machineKey> element. You must specify the validation key, decryption key, and the type of encryption used for validation of data. Example <machineKey validationKey="43ajhk3ebc97uj826a7b3v37k903a9d5dy65" decryptionKey="86165h77a9d588a9" validation="SHA1"/>
9. Configure a virtual Server name to access reporting Services. We need to do this for
   1. Report Manager (configuration file RSWebApplication.config), set the <ReportServerUrl> to the virtual server name and remove the entry for <ReportServerVirtualDirectory>. This step ensures that all requests coming through Report Manager are load-balanced to the report servers that are running in the scale-out deployment. The following example illustrates the syntax you should use; it specifies the virtual server name and report server virtual directory: example

      <ReportServerUrl>https://MyReportPortal/reportserver</ReportServerUrl>
      <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

      
      
   
   
   
   2. Report Server Web service (configuration file RSReportServer.config), Set the <UrlRoot> to the virtual server address. This step ensures that all hyperlinks in reports point back to the scale-out deployment and are load-balanced accordingly. This setting is also used to complete report delivery. The following example illustrates the syntax you should use: example
      
      

      <ReportServerUrl>https://MyReportPortal/reportserver</ReportServerUrl>

      
      
   
   
   
   3. This files can be found on each node at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
   
   
   
   



10. Now ask your network administrator to create a new DNS record for this new virtual url (report server url) and point that to IP addresses of cluster nodes, so that if client request for that virtual url, the request should reach to any of the node on the cluster and also request if can can built a system to verify the availability of all associated IP addresses, if none of them in not available then do the resolution only with available IP addresses.



11. Once that is being done, you are good to go.

So as part solution your reporting services will be running of both the nodes simultaneously, in case a any one node fail, reporting services will still be available on second node, and will be accessible to users.

It is recommended to use Load balancer, or web farm to build this solution, but none of the option was available in my case, so I worked with this workaround.