SSRS with failover sharing the same clustered database ?

Question

• Here's the scenario: 3 servers ( 2 for clustered sql server database, 1 to sharepoint)

  I have a Sharepoint 2010 server (SPS1) and the configuration database, content database, etc.. are located on a clustered SQL 2008 R2 instance on another set of machines (SQL1 and SQL2 servers - node instanced name SQL\SQL).

• I have installed the Reporting Services on the server SQL1 by setting SSRS database to the Cluster Node (SQL\SQL).

• The Reporting Services is working on Sharepoint integrated mode.

• If the SQL1 Server stops working, how can I ensure the SQL2 can assume the Reporting Services services?

Do I have to install SSRS in the SQL2 by using the existing database from the SQL\SQL node created when I installed it on the SQL1?

In the Sharepoint do I have to integrate the SSRS to a NBL service which should be configured to work with SQL1 and SQL2 ? If so,  do I need to have another server to create NBL service?

Am I right installing the SSRS in a server which does part of a clustered sql server?

How can I configure the SSRS in order to have failover in my scenario?

Thanks a lot for your help.
Alex Berenguer 

Answer 1

Hello Alex,

Thank you to post your question on TechNet forum.

First, I need mention that SSRS is not cluster-aware. If you install SQL2, then it means that you are having two independent reporting service. If the SQL2 connects to the same database installed on SQL1, it is called as scale-out deployment. That means, you can access the SSRS reporting service via both SQL1 and SQL2. For more information about this, please refer to the following article.

http://msdn.microsoft.com/en-us/library/bb522745.aspx

NBL service on reporting service is optional. If you want to configure the SQL1 and SQL2 under NBL, you can follow the next instruction to establish the environment.

http://msdn.microsoft.com/en-us/library/cc281307.aspx

If you have further questions about this issue, please tell us freely.

Regards,

Edward

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Edward Zhu

TechNet Community Support

Answer 2

Hi Edward , thank you for your help.

We decided to reinstall the SQL Server Reporting Services in the SPS1 for Dev and Quality environment.

For the production environment, we decided to up a new virtual server to host only SSRS for best performance.

All environment will have ssrs databases in the SSED clustered.

Please, do you know if getting the SSRS in a separed (without sharepoint server) and dedicated server is the best to do in my scenario? Its a Virtual Server with 16GB RAM.

Also, do you know if in the ssrs virtual server goes fine with unicast connection instead of multicast connection (this is a infraestructure case from VMWare team)

Best regards,

Alex Berenguer

---

Alex Berenguer

Answer 3

Hello Alex,

Thanks for your response.

I agree that installing SSRS on a separated server is a good solution. It is a common scenario to configure the SharePoint Integrated SSRS service. Many people use this architecture. Therefore, you can use it confidently.

Actually, I am not familiar to the unicast connection. However, I have concern to use the unicast. As we know, the reporting service need authentication method, and most authentication method need connect to the remote server. I am not sure if the unicast can solve this kind of issue. I hope you can discuss with VMWare team about this scenario.

If you have any questions about this issue, please tell us freely.

Regards,

Edward

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Edward Zhu

TechNet Community Support