Single gateways break under enterprise load — and when they do, every report that depends on on-premises data goes dark. Learn how to build Power BI gateway clusters that keep your data connections alive through failures, maintenance windows, and peak traffic surges.
Picture this: it's 7:45 AM on a Monday morning. Your executive team is preparing for a board presentation at 9:00 AM, and the flagship Power BI dashboard — the one that pulls live sales data from your on-premises SQL Server — shows a spinning refresh icon instead of numbers. You dig in and discover that the single gateway machine your team installed eight months ago quietly ran out of disk space over the weekend. Every scheduled refresh queued up, timed out, and failed. The reports are stale. The executives are unhappy. The data team is scrambling.
This scenario plays out in enterprises every week, and the root cause is almost always the same: treating the on-premises data gateway as a "set it and forget it" single point of failure. A gateway is the bridge between your on-premises data sources — SQL Server, Oracle, SAP, file shares, and more — and the Power BI Service running in Microsoft's cloud. When that bridge has only one lane and it closes, everything stops. Gateway clusters solve this problem by giving that bridge multiple lanes, so traffic keeps flowing even when one lane is under maintenance or has broken down.
By the end of this lesson, you will understand how gateway clusters work from first principles, how to plan and build a cluster that can handle enterprise workloads, and how to troubleshoot the problems that actually trip people up in production.
What you'll learn:
You should be comfortable with the concept of the on-premises data gateway before diving in. If you are new to gateways entirely, start with the lesson on choosing the right refresh architecture for your Power BI environment and then read the foundational guide to connecting Power BI to on-premises data sources using the on-premises data gateway. You should also have a basic understanding of Power BI workspaces and licensing, covered in the workspaces, capacities, and licensing for enterprise deployment lesson.
You will need a Power BI Pro or Premium license and gateway administrator rights in your tenant to follow the hands-on steps.
Before you can appreciate what a cluster does, you need to understand why a lone gateway struggles under enterprise conditions.
The on-premises data gateway is software installed on a Windows Server machine that sits inside your corporate network. It maintains an outbound connection to Azure Service Bus — Microsoft's cloud messaging layer — and listens for requests from the Power BI Service. When a scheduled refresh fires, Power BI sends a message through Service Bus to the gateway, which then opens a connection to your on-premises database, executes queries, pulls back the data, compresses it, and streams it back to the cloud.
That sounds simple, but the math at enterprise scale is brutal. Imagine 200 datasets, each configured to refresh every hour. That is potentially 200 concurrent connections, compression operations, and data transfers all competing for the CPU, memory, network bandwidth, and thread pool of a single Windows Server machine. Add DirectQuery reports — where every dashboard interaction fires a live query through the gateway — and you can easily saturate a well-provisioned server during business hours.
Beyond raw capacity, there is the maintenance problem. Applying Windows patches to a gateway machine requires a reboot, which means your gateway is offline for several minutes. Any refresh that fires during that window fails. In a 24/7 enterprise environment, there is no safe time to reboot a single gateway.
Key insight: Gateway clusters solve two distinct problems simultaneously: high availability (keeping data connections alive when one gateway member fails or is taken offline) and load balancing (spreading refresh and query work across multiple machines so no single member becomes a bottleneck).
A gateway cluster is a group of two or more gateway installations that are registered to the same logical gateway resource in the Power BI Service. From Power BI's perspective, the cluster appears as a single gateway. Datasets and dataflows are configured to use the cluster gateway, not any individual machine.
When Power BI needs to execute a refresh or route a DirectQuery request, it looks at the cluster and selects which physical member should handle the work. This selection process is what makes clusters powerful — and it is worth understanding in some detail.
Power BI uses a weighted round-robin approach for distributing work across cluster members. Each member periodically reports its current load — CPU usage, memory consumption, active connections — back to the Power BI Service. The service uses this telemetry to avoid routing new requests to members that are already under heavy load.
Think of it like a restaurant with multiple cashiers. A smart manager watches each cashier's queue and directs new customers to whichever cashier has the shortest line, rather than mechanically rotating customers regardless of how busy each cashier is.
If a cluster member stops responding — because the machine rebooted, the gateway service crashed, or network connectivity dropped — the Power BI Service automatically stops routing requests to that member and redirects all traffic to the remaining healthy members. This is the high availability mechanism in action.
Note: Failover is not instantaneous. The Power BI Service has to detect that a member is unhealthy, which typically takes between 30 seconds and a few minutes depending on the heartbeat interval. During that detection window, some requests may fail before traffic is rerouted. This is why gateway clusters reduce downtime but do not eliminate it entirely.
This is a common misconception worth correcting early. Unlike some clustering technologies that have a "primary" node that handles writes and a "secondary" that handles reads, all Power BI gateway cluster members are equal peers. Any member can handle any type of request — scheduled refresh, DirectQuery, or dataflow refresh. There is no concept of a primary failover node.
However, the first gateway you install becomes the one that "owns" the cluster's credential vault and configuration. That machine is sometimes called the primary member informally, but its only special role is that it was created first. All subsequent members you add to the cluster inherit the configuration and credential access automatically.
Jumping straight into installation without planning is how you end up with a cluster that technically exists but does not actually solve your availability or performance problems.
For a minimum viable high-availability cluster, you need two members. With two members, one can go offline for maintenance while the other keeps serving traffic. However, two-member clusters leave you vulnerable: if the remaining member also experiences a problem during the maintenance window, you are back to zero.
A three-member cluster is the practical enterprise standard. With three members, you can take one offline for patching while the other two absorb the load. If a second member has an unexpected failure during that window, you still have one functioning member keeping your data connections alive.
For very large enterprises with hundreds of datasets and heavy DirectQuery usage, four or five members may be appropriate. Use the monitoring data from your existing single gateway — CPU, memory, active connection counts during peak refresh windows — to size the cluster. A common rule of thumb: if your single gateway regularly exceeds 60% average CPU during your morning refresh window, plan for at least three members of equivalent specification.
All gateway cluster members must be able to reach your on-premises data sources. This seems obvious but has real implications for network architecture. If your SQL Server databases are in a secured VLAN, every gateway member needs a network path and firewall rule to reach that VLAN. If you add a new cluster member in a different data center segment, you may need new firewall rules before that member can actually do useful work.
Recommended minimum specifications for each cluster member in a medium-to-large enterprise environment:
Warning: Do not install gateway cluster members on the same physical or virtual machines as your SQL Server databases. Gateway compression and query operations are CPU and memory intensive, and running them on the database server creates resource contention that degrades both gateway performance and database query performance.
Each gateway service needs a Windows service account to run. Use a dedicated domain service account — not a personal user account, and not the Local System account. The service account needs:
Using the same service account across all cluster members simplifies credential management and ensures consistent access behavior.
Download the on-premises data gateway installer from the Microsoft Download Center or from the Power BI Service (Settings → Manage connections and gateways → Download gateway). Always download the current version — Microsoft updates the gateway monthly, and running an old version can cause compatibility issues with new Power BI features.
Run the installer on your first server. When you reach the "Register a new gateway on this computer" vs. "Migrate, restore, or takeover an existing gateway" prompt, choose "Register a new gateway on this computer." Give the gateway cluster a meaningful name that reflects its purpose and environment — something like PROD-GW-CLUSTER-EASTUS rather than the default MyGateway. You will live with this name in your Power BI tenant for years, so make it descriptive.
During registration, you will be prompted to set a recovery key. This key is critical — it encrypts the credential vault for the cluster. Store it in your organization's password manager or secrets vault. If all cluster members fail simultaneously and you need to restore from backup, this key is the only way to decrypt and recover your data source credentials.
After installation, open the gateway app (accessible from the system tray or the Start menu under "On-premises data gateway"). Confirm the gateway shows a green "The gateway is online and ready to use" status message. This indicates a successful outbound connection to Azure Service Bus.
Tip: After confirming the first member is online, log into the Power BI Service and navigate to Settings → Manage connections and gateways. Confirm the new cluster gateway appears in the list. This is the single logical gateway resource that datasets and dataflows will be pointed to.
With your first gateway member running, adding additional members follows a nearly identical installation process — with one critical difference at the registration step.
Install the gateway software on your second server using the same installer version. At the registration prompt, choose "Register a new gateway on this computer." On the next screen, you will see a checkbox that says "Add to an existing gateway cluster." Check this box.
You will now be prompted to:
After entering the recovery key and completing registration, the second member automatically downloads the cluster configuration, connects to the same Azure Service Bus namespace, and begins receiving its share of routed requests. The process typically takes two to three minutes.
Repeat this process for each additional member you want in the cluster.
Note: All members of a cluster must be running the same version of the gateway software. If you update one member to a new monthly release, update all members within a short window. Microsoft provides a 30-day compatibility window between gateway versions, but running mixed versions for extended periods can cause inconsistent behavior.
A gateway cluster without configured data sources cannot route any requests. Data source configurations live at the cluster level, not at the individual member level — this is by design. When you configure a SQL Server data source on the cluster, every member automatically inherits the ability to connect to that source using the stored credentials.
Navigate to the Power BI Service, go to Settings → Manage connections and gateways, and select your cluster. Click "New connection" (in the older UI it is called "Add data source"). Configure the connection type, server name, database name, and authentication method exactly as you would for a single gateway.
For a SQL Server connection, you will typically use one of these authentication methods:
Warning: If you use Windows authentication, verify that the service account running the gateway service on every cluster member has the required database permissions. A common mistake is granting permissions to the service account on the first member's server but forgetting that the account needs the same SQL Server permissions regardless of which cluster member executes the query.
Once the data source is configured at the cluster level, you can assign datasets and dataflows to it just as you would with a single gateway. Navigate to the dataset settings in your workspace, expand the Gateway and cloud connections section, and map each data source to the cluster connection.
By default, Power BI distributes requests across all online cluster members automatically. In most cases, you should leave this default in place — it is well-tested and handles the majority of enterprise workload patterns effectively.
However, there are two advanced options worth understanding.
In the Power BI Service under Manage connections and gateways, select your cluster and open its settings. You will see a toggle for "Distribute requests across all active gateways in this cluster." Ensure this is enabled. If it is disabled, all traffic routes to the primary member, which defeats the load-balancing purpose of your cluster entirely.
For clusters handling very high volumes of DirectQuery traffic — such as those supporting composite models and DirectQuery reports that fire a query on every visual interaction — you can tune whether the load balancing algorithm prioritizes even distribution or performance optimization. The performance-optimized setting routes repeat queries from the same dataset to the same cluster member when possible, improving cache hit rates at the cost of potentially uneven load distribution. For most enterprises, the default (optimize for even distribution) is the right choice.
A cluster you cannot observe is a cluster you cannot manage. Power BI provides two layers of monitoring for gateway clusters.
In Manage connections and gateways, select your cluster. The members list shows each member's current status: Online, Offline, or a timestamp of its last heartbeat. Check this view as part of your daily operational routine, or better yet, set up automated alerts so your team is notified when a member goes offline.
The Power BI REST API exposes gateway cluster member status endpoints, which you can use to build automated health checks that integrate with your organization's monitoring platform (such as Azure Monitor or PagerDuty).
The gateway application on each cluster member writes detailed diagnostic logs to C:\Users\<service_account>\AppData\Local\Microsoft\On-premises data gateway. These logs are invaluable for diagnosing specific refresh failures, connection timeouts, and credential errors. When a refresh fails with a vague "Unable to connect to the data source" error in the Power BI Service, the gateway logs on the member that processed the request will almost always reveal the underlying cause — an expired password, a firewall rule change, or a data source that is temporarily unavailable.
Tip: Configure log retention and rotation for your gateway log directories. In busy clusters processing hundreds of refreshes per day, log files can grow to several gigabytes per week. Establish a process (a scheduled task or log management tool) to archive or delete old logs, otherwise you risk the gateway machine running out of disk space — which, as our opening scenario demonstrated, causes cascading failures.
This exercise walks you through building a two-member gateway cluster in a lab environment. You will need two Windows Server machines (virtual machines work perfectly), access to a Power BI tenant with gateway administrator rights, and an on-premises SQL Server or any accessible database.
Step 1: Install the first gateway member
On your first server, download and install the on-premises data gateway. Register it as a new gateway named LAB-GW-CLUSTER-01. Set a recovery key of your choice and store it securely. Confirm the gateway shows online status in both the desktop app and the Power BI Service.
Step 2: Install the second gateway member
On your second server, install the same version of the gateway software. At registration, select "Register a new gateway on this computer" and then enable "Add to an existing gateway cluster." Select LAB-GW-CLUSTER-01 from the dropdown and enter your recovery key. After registration completes, verify that both members appear under the cluster in Manage connections and gateways.
Step 3: Configure a data source In the Power BI Service, select the cluster and add a new SQL Server connection pointing to your test SQL Server. Use either Windows or SQL authentication. Test the connection to confirm both cluster members can reach the database.
Step 4: Connect a dataset In Power BI Desktop, build a simple report against your SQL Server (a star schema works well for this exercise). Publish it to a workspace. In the dataset settings, map the SQL Server data source to your cluster connection. Trigger a manual refresh and confirm it succeeds.
Step 5: Simulate failover Open the gateway desktop app on your second server and click "Stop service" (or stop the "On-premises data gateway service" from the Windows Services control panel). Return to the Power BI Service and trigger another manual refresh. Observe that the refresh succeeds — it was handled by the first member, which took over after detecting the second member was offline. Restart the second member's gateway service and confirm it returns to Online status in the cluster view.
Mistake: Forgetting to update all members simultaneously When a new gateway version is released, it is tempting to update one member at a time over several days. This leads to version mismatches that cause inconsistent behavior — some requests succeed, others fail, and the errors are difficult to diagnose because they are non-deterministic.
Mistake: Using personal user accounts as the gateway service account When the employee whose account runs the gateway service leaves the company and their account is disabled, every cluster member's gateway service fails to start after the next reboot. Always use a dedicated service account that is not tied to any individual person.
Mistake: Mapping datasets to a specific cluster member instead of the cluster In older versions of the gateway UI, it was possible to point a dataset at a specific cluster member rather than the cluster itself. This bypasses all load balancing and high availability logic. Always confirm that datasets are connected to the cluster-level connection, not a member-specific one.
Troubleshooting: "Gateway not reachable" errors despite gateway showing Online This usually indicates a credential problem on a specific data source rather than a true gateway availability issue. Open the data source configuration in the Power BI Service and re-enter the credentials. Then trigger a refresh and check the gateway logs to identify which member processed the request and what error it encountered.
Troubleshooting: Refresh requests consistently routing to one member If your monitoring shows one cluster member handling 90% of requests while others are idle, verify that the "Distribute requests across all active gateways in this cluster" toggle is enabled. If it is already enabled, check that the idle members are returning valid health telemetry — a member that reports stale load data may be deprioritized by the routing algorithm.
Warning: Never delete a cluster member from the Power BI Service without first stopping or uninstalling the gateway software on that machine. If the software is still running but the registration has been removed from the service side, the orphaned gateway process will repeatedly attempt to reconnect, generating noise in your Azure Service Bus logs and potentially consuming resources.
A gateway cluster transforms your on-premises data connection from a fragile single point of failure into a resilient, load-balanced infrastructure component. By deploying two or more cluster members, you gain the ability to patch and maintain gateway machines without service interruptions, absorb traffic spikes during peak refresh windows, and survive unexpected member failures without your reports going dark.
The key principles to carry forward:
With a stable gateway cluster in place, you are ready to build more sophisticated data architectures on top of it. If your on-premises datasets are large and you want to minimize the data transferred through your cluster on each refresh, explore implementing incremental refresh for large-scale enterprise datasets, which dramatically reduces the volume of data flowing through the gateway by only refreshing new and changed records. If your reports require real-time data access rather than scheduled batch refreshes, review composite models and DirectQuery to understand how live query traffic flows through your cluster and how to size it appropriately.
For teams managing the broader reliability of their Power BI environment, the companion lesson on implementing scheduled refresh and refresh failure alerting pairs naturally with gateway cluster operations — because even a perfectly healthy cluster cannot compensate for refresh schedules that are poorly configured or missing failure notifications.