How does one make SQL Server Reporting Services highly available? This is something I have been researching. We have several applications that depend on Reporting Services, so maintaining high availability (minimizing or mitigating the impact of downtime) is critical and needs to be added into updates to our SQL Server High Availability Plans.
My Google searches suggest making the Reporting Services databases highly available using Failover Cluster Instance (FCI) or AlwaysOn Availability Groups, then configuring multiple report servers in a scale-out deployment all pointing to the same database. This approach is briefly outlined in this Microsoft Developer Network (MSDN) article, High Availability (Reporting Services). For High Availability they recommend a Network Load Balancing (NLB) cluster for the web servers hosting the report servers.
An alternate method to the NLB cluster uses AlwaysOn Availability Groups to protect the database, and installing SQL Server Reporting Services in the same instance for all replicas assigned to the Availability Group. When properly configured the listener for the Availability Group can also redirect users to the working Report Server.
My SQL Server configuration consists of two servers in our primary datacenter with a third at an off-site location. All three participate in the Availability Group. The ones in the primary data center use synchronous-commit mode, while the off-site one uses asynchronous-commit mode.
Before making changes to my production environment, I tested my plan in a lab environment. Since I did not have one setup and ready to go, I opted to use the Virtual Labs at the Microsoft Learning Center. I used the “AlwaysOn Availability Groups in SQL Server 2014” lab which gives full run of three SQL Servers and a domain controller for around 2 hours.
I did need to do a little work to setup the Availability Group and install Reporting Services on each server. When installing Reporting Services, it is important to choose Install but do not configure server. This MSDN article, Configure a Native Mode Report Server Scale-Out Deployment, contains more detailed instructions on installing and configuring the servers in a scale-out deployment. I used the Availability Group Listener DSN name when configuring the report manager and web service addresses, and placed the database in the Availability Group.
Once the above items are done, all servers should respond to Reporting Services requests, but since we will be accessing it through Listener DSN we are hitting the primary server for the group. If there is a failover, new traffic will be directed to the new primary server.
One thing this does not account for is scheduled reports. Those are setup as SQL Server Agent jobs on the SQL instance hosting the database at the time they are created. We currently have very few of these, so it is a minor issue. My future plan is to work on a script to copy these jobs to each server in a disabled state, and then enable them if the server becomes the primary for the database.
Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.
1020 New Holland Avenue, Lancaster, PA 17601