Loading...

Tech Talk Live Blog

Making SQL Server Reporting Services Highly Available

Greg Hess


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 Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770