Power BI Report Server Scale Out With Load Balancing

By: Mason Prewett

 
I was recently tasked with scaling out a Power BI Report Server for a Key2 Consulting client. They wanted to upgrade their environment from a single report server to multiple report servers and add load balancing.

The system was starting to see performance degradation due to workload and also had no failover when the report server was down. The intended scale out was going to solve these issues.
 

Scaling Out vs Scaling Up

When upgrading a system, there are two options:

• Scaling Up – Increase the allocated resources for the existing servers
• Scaling Out – Add additional servers to the system and spread the workload across them

Scaling out is certainly the more flexible architecture. It allows a system to be given more resources in a repeatable format that often requires no downtime.

Systems can also be scaled down if resources are found to not be needed and repurposed elsewhere. This is also the strategy that is used in cloud computing architectures to ensure that the infrastructure can be scaled up and down based on demand.
 

Scale Out Overview

Here are the steps that were involved in this effort:

1. Create two new virtual machines to add to the architecture. This brought the total number of servers to three.
2. Install, but don’t configure, Power BI Report Server on the two new virtual machines.
3. Configure load balancing with these three servers. An F5 load balancer was used in this scenario.
4. Create a DNS entry that points to the virtual IP address of the load balancer configuration.
5. Configure Power BI Report Server and scale out components for the two new virtual machines.

Here is a diagram of the architecture that was built to support this.
 

 

Benefits of this Scale Out Plan

• The reporting workload will be distributed evenly across the report servers by the load balancer
• If a report server goes down, the load balancer will automatically reroute all traffic to the other report servers
• This architecture can be easily scaled out further by adding and configuring a new report server into the Power BI Report Server scale out and load balancer

Creating the New Virtual Machines

Two new virtual machines were going to be added and we created these with the same specifications as the original report server. There is nothing special to consider for this beyond the system requirements recommended by Microsoft.
 

Install Power BI Report Server

For this step, I highly recommend simply installing Power BI Report Server on each new virtual machine but not choosing to configure the report server. Configuring the Power BI Report Server is a step presented at the end of the installation but just choose to close this instead.

The reasoning for this is because we saw definitive evidence that the system will start sending traffic to these servers once it is configured. In our case, we wanted the load balancer to handle the traffic and were unsure of the consequences of the system doing this. Also, the URL cannot be configured for these machines until the load balancer configuration is complete.

Given all of this, I would recommend taking care of the installation first and then configuring the Power BI Report Server as the last step.
 

Configure Load Balancing

The Key2 client had a network team that managed an F5 load balancer that was used in this architecture. There was nothing done specifically for Power BI Report Server with this. The network team configured this as they would with any other load balancing scenario.

Here are the things that we did coordinate with this team:

• Virtual IP Address – We reserved a virtual IP address for this load balancing configuration. Doing so is extremely important as this is what the DNS entry would point to for routing all traffic from the chosen URL to the report servers.
• Ensure that Client Affinity was Configured – This is a configuration that is done on the load balancer that ensures optimal performance is achieved for the Power BI Report Server as described in the Report Server Scale Out documentation (see the first “important” note).

Create a DNS Entry

A DNS entry is required in order to have a URL that points to the Power BI Report Server system instead of the machine name.

Let’s use this URL as an example: PBI.ReportServer.com. Let’s also use the following virtual IP address of the load balancer for an example: 11.123.45.67.

A DNS entry needs to be added to point all traffic going to PBI.ReportServer.com to 11.123.45.67. To confirm that this is in place, do a ping from command prompt (ping PBI.ReportServer.com) and make sure it returns 11.123.45.67.

Once this is in place, the report servers can be configured to use this URL. However, hold off until the last step to complete this.
 

Configure the Scale Out

This step is where the final configurations take place and where the most issues can occur. There are two sets of documentation that are needed for configuring the scale out and it is not always obvious which one is appropriate to go to. The answer is both and I wish I would have known this at the start.

1. Power BI Report Server Scale Out Configuration – covers just the configuration to add multiple servers to a Power BI Report Server system.
2. Power BI Report Server Load Balancer Configuration – covers additional configurations to add to a Power BI Report Server scale out that includes a load balancer.

It is best to follow this documentation in the order that I have them listed. I will not go over every step of these processes, I will just explain the important aspects and some things that were left out. Hopefully this will help lead to success where it caused me weeks of troubleshooting.
 

Note About Versions

At the top of the documentation, notice it says: “Applies To: SQL Server Reporting Services Enterprise Edition (version and later) and Power BI Report Server”. Power BI Report Server does not go by SQL Server versions but rather by its own versioning system. Only pay attention to this if you are configuring an SSRS Report Server only.
 

Configuring the Scale Out

The documentation for this can pretty much be followed step by step to get everything accomplished. Here are some things of note for this process, which all take place in the Report Server Configuration Manager.

• Service Accounts – Make sure you use a defined service account and password for the “Service Account” section. Do not use a built-in account as doing so is known to cause issues.
• Backend Database – All report servers should have the same database server and database chosen. This creates a setup where all report servers handle the report processing but speak to the same backend.
• SSL – If you use an SSL, which I would always recommend, this is set up in the sections titled “Web Service URL” and “Web Portal URL”. Install the certificates normally on the report servers and then they will show up when clicking the advanced button in the HTTPS setup section in the Report Server Configuration Manager.
• Joining Servers – When a server is joined to the Scale Out, it will start accepting traffic. I could not confirm if this was report processing or just server-to-server communication, but it is now part of the system and errors will be thrown if it is down. Be aware of this before joining servers.

Additional Configurations for a Load Balanced System

This documentation will walk you through additional configurations that need to be completed for a load balanced system. Here are the things of note for this process.

• Examples – I will be using an example URL (https://pbi.reportserver.com) and an example virtual IP (11.123.45.67) for this section.
• Virtual IP – just to restate, this is the IP address of the load balancer configuration and the URL points to this. The load balancer then distributes traffic across the report servers. This is how traffic gets to the system.
• Configuring View State Validation – This 100% needs to be done and it needs to be put in the RSReportServer.config file (under the section) and not the Web.config file. The second example in the documentation is correct for Power BI Report Server and case does matter. Also, I have confirmed that it is perfectly fine to use an online machine key generator for this. Don’t worry about using IIS or PowerShell – doing so will work but is much more difficult.
• Configure Hostname and URLRoot – This is also 100% needed. Using my examples defined above, here is what these configurations need to look like. These go in the section of the RSReportServer.config file.
• Hostname will likely have to be added as it is not there by default:
  
  PowerShell

  <Hostname>pbi.reportserver.com</Hostname>

  1	<Hostname>pbi.reportserver.com</Hostname>

• URLRoot is the full web service URL including https:
  
  PowerShell

  <URLRoot> https://pbi.reportserver.com/reportserver</URLRoot>

  1	<URLRoot> https://pbi.reportserver.com/reportserver</URLRoot>

• Hosts File – After everything was configured, we were not able to publish reports to the system. Power BI Desktop gave an error saying “An error occurred while attempting to save the report to Power BI Report Server”. This ended up being due to an additional configuration that was needed in the hosts file for each report server in a load balanced configuration. To do this, open NotePad as an administrator and open the file “C:\Windows\System32\drivers\etc\hosts”. On the last line, add an entry for your DNS entry and the IP address of the server (not the virtual IP). Example, on the report server with an IP address of 11.123.45.78, the entry would be “pbi.reportserver.com 11.123.45.78” with no quotes. These configurations need to be repeated on each report server, replacing the IP address for the server it is located on. This is not documented and caused us a week of troubleshooting. I am not sure if every configuration requires this, but Microsoft confirmed that our configuration did.

Configure a Single Server for Data Model Refreshes

A Power BI Report Server environment is very unique as it can host SSRS reports, Power BI reports and also handle Power BI data model refreshes. However, this can present a problem. All report requests are bound by web and report server timeouts that usually do not exceed around 2-3 minutes.

This means that a report can sit spinning for 2-3 minutes before the system terminates the request. This helps keep inefficient reports from affecting system performance.

Power BI data model refreshes are different. It is not unusual for a data model refresh to take 30 minutes, an hour, or sometimes more. The system defaults to allowing a maximum of 2 hours for this. It is difficult to manage system performance that allows both of these scenarios.

There is a setting for each report server that will allow it to only handle Power BI data model refreshes and not report requests. In the RSReportServer.config file, search for the setting IsDataModelRefreshService and set this to True to have that server handle Power BI data model refreshes. Set this configuration to False on the other servers to only allow them to handle report requests.
 

Questions?

Thanks for reading! We hope you found this blog post useful. Feel free to let us know if you have any questions about this article by simply leaving a comment below. We will reply as quickly as we can.
 

Keep Your Data Analytics Knowledge Sharp

Get fresh Key2 content and more delivered right to your inbox!
 

About Us

Key2 Consulting is a boutique data analytics consultancy that helps business leaders make better business decisions. We are a Microsoft Gold-Certified Partner and are located in Atlanta, Georgia. Learn more here.