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.
Best Answer
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