Solving pressure with an NLB

It has been a long time since I posted anything. Regardless, life has been busy. I am currently creating a workshop/office in my basement and a woodshop in my garage which consume most of my time. Not to mention being a parent to two kids and two pets. However, lets talk about Microsoft’s built in Network Load Balancer and how it can be combined with SSRS Scale-Out to effectively balance your reporting load.

Growing pressure from the reports

I had the pleasure of deploying a SSRS Scale-Out setup for work recently, only to find that Microsoft SQL Server Reporting Services does not, inherently, load balance. In Microsoft’s defense, it technically “is.” The kicker is that its separating “scheduled” report generations to other servers added to the deployment, where the main Reporting Server is taking on all of those wonderful user requests. Luckily, there is another option that will help us achieve actual load balancing.

Enter, Microsoft’s NLB

Microsoft provides the NLB free in Window Server and its relatively easy to setup. For SSRS, there are a few things you have to do for it to behave as intended. First, you need to have a working SSRS Scale-Out deployment. For this example, lets call it Reporting 1 and Reporting 2.

On Reporting 1, go to the start menu and open Server Manager. From here, click “Add roles and features” to install the Network Load Balancer. Progress through the menus until you get to “Features” and click the check box for “Network Load Balancing.” Proceed to install this feature.

When the installation is complete, you should see the NLB app available in your start menu, or you can search for it. Open the application and we are one step closer to creating our NLB Cluster.

Here, you will see the main view for the NLB Manager. Click on Cluster in the menu bar and select “New.” In this window, you will enter the first server in the “host” section. So in this case, its “Reporting 1” and hit connect. If successful, you will see the Ethernet interface the server uses. Click Next.

In Host Parameters, your first server should already be displayed here. You can add the other server IPS that you wish to load balance. Ensure you have NLB Manager installed on each device before doing so. I am not 100 percent positive if you need to have this installed on all devices or not, but I figure why risk it for the biscuit. After you have all the server ips added, click next.

In Cluster IP Addresses, click add. Enter the IP address for the Cluster IP. I would stick with the same subnet as your servers, but the last IP octet should be something like “.10” if that is possible for your network. Once added, proceed to the next step.

With the Cluster IP set, it is time to put the “Full Internet Name” in. Typically this will be already determined by your SSRS site name. To clarify, if your reporting server is “reporting.lots-PROD/reports”, then the Full Internet Name will be “reporting.lots-PROD.” In Cluster Operation, select “Multicast.” Proceed by click next.

Finally, you are present with the option to setup port rules. I suggest deleting the ports selected by default. You will want to add the following ports; 1433 (SQL), 80 (HTTP), and 443 (HTTPS). Hit okay and the cluster will being doing its thing. You might become disconnected shortly, but you will reconnect.

If everything worked as it should, you will see all the servers in your cluster, with a status of Converged and green icon indicating connection status.

Now, for this NLB to work for SSRS, there are a few configurations on the server side that need to be done. I will leave that for another post, but you can find the instructions here.

*Not Included

So you didn’t know, eh?

Something new I was recently made aware of was that Reporting Services are no longer updated with SQL Server CUs as of SQL Server 2017. What this means is that you have to download the Reporting Services installation media SEPARATELY. That, my friends, is frustrating.

My colleague found this and shared it with me, which then prompted us to upgrade our Reporting servers to the most recent version we could. Our hope is that by updating the Reporting Service, we might be able to resolve an issue that has been plaguing us for a couple of weeks.

The Problem!

Currently, we have a production Reporting site that houses a decent amount of reports. Nothing too crazy, but enough. Supposedly after we updated to SQL Server 2017 CU23, we had users complaining about their reports not loading, and then not being able to open any report in general. Now, when I say “open a report”, I mean clicking into the folder that houses the report, and then clicking the report itself. The user does not even get to the screen where they can input their parameters; they just receive a loading screen that sits there indefinitely. We threw more RAM/CPU at the server, because it should have been provisioned higher in the first place, but that did not completely solve the issue. The work around required a full server reboot, not just a services reboot, which we found odd. 

Finally, after going down the rabit hole of why Reporting Services are now a standalone install, the report server was updated to SQL Server 2017 CU24 and updated to the latest Reporting Services version for 2017. We have not received any reports of issues, but I am not sold on us resolving the issue.

Did you look at the ReportServer Log?

Yes, we looked into the logs, and yes we looked into the executionlog view (executionlog3) for details of the reports. However, the reports never actually fail, since they never actually GET to even passing the parameters. Maybe you can sense some frustration in this post?

Anyways, hoping that resolved the issue but since it has been awhile since I posted something SQL related, I figured I would share this. Full disclosure, I did not find this info-my colleague did. So thanks to he/she who will not be named due to privacy reasons. However, thank you!