3

I need some guidance on best practices and setup for SSRS. I have a SQLServer 2012 setup with data source on 1 instance and SSRS on a separate server. There is a need to setup HA and I understand what is involved with the setup for the data source server. This is a large instance with many databases.

The SSRS instance is very small (350 MB). Should I setup AOAG for the report server db? OR configure a scale-out deployment? The goal is for smooth transition should one server go down. I am not very familiar with SSRS i.e setup beyond report db - the report manager etc. Would failover cluster manager take care of the rest. I will have to guide the application team with this.

Getting some input as to the direction I need to look and research will be gratefully appreciated.

Thank you, Debbie.

1
  • Do you only have 2 servers? Are they the same spec? Do you have a SAN? – James Anderson Mar 27 '15 at 15:46
1

Solutions that provide HA will serve you well - both a failover cluster instance or AOAG. Technet articles are great to help you with this and understand it deeper.

For instance, if you use a FCI (SQL Server failover cluster instance), while installing you provide a network name which identifies the instance. Your SSRS only needs to know this networkname\instancename. Then, when the primary node failsover, another node will assume the work and keep serving your SSRS with the databases. Do notice that this solution undergoes a small downtime - the time for the cluster to disconnect the disks from the prior primary node to the "new to be" primary node, while transfering the cluster groups.

If you use AOAG, your SSRS will have to work with the VNN (virtual network name), and, when a failover occurs, a secondary node will assume the work and keep providing data. As in this case the node has its own disks (providing a second copy of the data, while on FCI you just had one copy of the data), the failover doesn't face the "transfer the disks" downtime.

As for the report server itself, taking into account your comment, this pages will provide you great guidance to scale-out:

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

https://msdn.microsoft.com/en-us/library/ms159114.aspx

You may want to consider setting up a NLB to take advantage of having more than one SSRS server. This should be ocnfigured first, and you can read about it here:

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

And, just in case, some aditional information for Reporting Services with AlwaysOn Availability Groups:

https://msdn.microsoft.com/en-us/library/hh882437.aspx

3
  • 1
    Thank you for the response. I understand and plan to use AOAG for the data source instance. I am not sure how to setup the instance with SSRS that houses the report server database and report manager etc – Debbie Mar 27 '15 at 16:56
  • How do I setup HA so report generation continues even if the SSRS instance is down. – Debbie Mar 27 '15 at 16:58
  • I did not see the links earlier. I will review these, thanks much for the guidance, – Debbie Mar 27 '15 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.