How to Configure High Availability SQL Reporting Service Scale-Out Deployment Clustering

Posted in: Blog, MS CRM, MS CRM 2011, MS CRM 2013, SharePoint 2007, SharePoint 2010, SharePoint 2013 | Feb 28, 2014 at 3:57 PM

No comments

INTRODUCTION

You probably have read my post on configuring SQL Clustering for SharePoint. Now I need to also configure SQL Reporting Service to be highly available (HA). In Reporting Service term this is called Scale-Out Deployment.

For this exercise I have 2 SQL Servers cluster namely SQL01 and SQL02. The cluster node is called SQLCL. Please note that in my case, all of these servers are under the same domain Active Directory.

This post is also related to How to Install Microsoft Dynamics CRM Reporting Extension SSRS in SQL Report Cluster Scale-Out Deployment.

STEP-BY-STEP INSTRUCTIONS

1. Upon installing SQL Cluster for your main node (eg. SQL01) you may have selected SQL Reporting Service as one of the SQL features you decide to turn on. If this is the case then simply follow the next step. Otherwise, you will have to run the wizard again. Should you decide to run the wizard again please be mindful that reporting service can only be installed using the “Standalone Installation“. Therefore, when you re-run the wizard, select Installation > New SQL Server stand-alone installation or add features to an existing installation.

Now, very important thing. To re-run the wizard you need to use command prompt rather than double-clicking setup.exe. The reason is because you will encounter the error below during rule validation:

Therefore, to avoid this problem we need to make sure that that particular rule validation is not run during install. To do this, on command prompt run the following:

C:\Downloads\SQLInstallFolder\Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install

Then, the wizard will start and make sure you select Installation > New SQL Server stand-alone installation or add features to an existing installation. Then select Add features to an existing instance of SQL Server 2012 then simply select the existing SQL cluster instance and click Next.

Upon Feature Selection select the Reporting Service – Native then Next.
On Server Configuration specify the service account to run the Report service. I suggest use an Active Directory account and make sure it’s the same for all of the cluster nodes.
On Reporting Services Configuration select Install Only.

2. Do the same installation steps for SQL02.

3. Now it’s time to configure the Report Service cluster. Go to SQL01 and run Reporting Service Configuration Manager.
– Click on Web Service URL and click Apply. This will give you URL something like http://sql01:80/ReportServer.
– Click on Report Manager URL and click Apply. This will give you URL something like http://sql01:80/Reports.
– Click on Database and select Change Database. On the Change Database wizard select Create a new report server database. On Database Server make sure you specify the cluster instance ie. SQLCL. Then specify the database name, in my case it’s ReportServer and the Temp Database name is automatically set to ReportServerTemp. Specify the credentials then continue to finish off the wizard. I suggest that you use a proper domain service account eg. DOMAIN\sqlreport. In fact, use the same account as the one you specified during install previously.
– Click on Encryption Keys and Backup. This will create you a *.snk file. Have the *.snk file handy. We will need to transfer it to SQL02 later on for completing the process.

4. Now go to SQL02 and run Reporting Service Configuration Manager.
– Click on Web Service URL and click Apply. This will give you URL something like http://sql02:80/ReportServer.
– Click on Report Manager URL and click Apply. This will give you URL something like http://sql02:80/Reports.
– Click on Database and select Change Database. On the Change Database wizard select Choose an existing report server database. Select the ReportServer database you’ve created previously on step 3. Make sure you also use the same service account which in this case is DOMAIN\sqlreport.
– Click on Encryption Keys and click Restore. Now you want to use the *.snk file from step 3! Very important or else your second server will not be recognisable.

5. Then go back to SQL01 > Reporting Service Configuration Manager.
– Click on Scale-out Deployment and you will see SQL02 displayed in there with status “Waiting to join”.
– Simply select the SQL02 server and click Add Server.

That’s it!

Now instead of going to an individual server to access the reports ie. http://sql01/reportserver, you can go to your cluster node name http://sqlcl/reportserver.

You now have High Availability for your SQL Reporting Service.

Hope this helps,

Tommy

No Comments Yet.