The Power BI On-Premises Data Gateway - Real Answers to the Questions That Matter

The on-premises data gateway is the least glamorous component in the entire Power BI stack, and it's involved in more support calls than everything else combined. Every Australian organisation with a SQL Server under a desk, a file share full of Excel, or an ERP that's not going to the cloud this decade has one of these gateways running somewhere. Usually installed years ago, by someone who has since left, on a VM nobody wants to touch.

Microsoft maintains an official on-premises data gateway FAQ that's worth a read. But the questions clients actually ask us are a bit different from the ones in the documentation, so here's the version based on what comes up in real engagements.

Do I even need a gateway?

You need a gateway when the Power BI service has to reach data it can't get to directly. On-premises SQL Server, Oracle, file shares, local folders of Excel and CSV files, and most things sitting inside your corporate network with no public endpoint.

You don't need one for cloud sources with public endpoints: Azure SQL Database, Snowflake on its public endpoint, SharePoint Online, Dataverse and the like. The confusion starts with cloud sources locked behind private endpoints or VNets. Your Azure SQL instance with public access disabled is, from Power BI's point of view, on-premises. For those you want a VNet data gateway, the managed Microsoft-hosted flavour, which spares you the VM but brings its own licensing requirements (it needs Fabric capacity or Premium).

The question behind the question is usually "can I avoid running this thing at all", and increasingly the answer is yes, by moving the data. A good chunk of our gateway conversations end with a plan to land the on-prem data into OneLake or a warehouse instead, which is work our Microsoft Fabric consultants do regularly. The gateway then becomes a tactical bridge rather than permanent infrastructure. But that's a roadmap, not a Tuesday afternoon, so most organisations run gateways for years. Run them properly.

Personal mode or standard mode?

Standard mode, almost always. Personal mode exists for the individual analyst refreshing their own datasets; it runs under one user's account, supports only Power BI (no Power Apps, no Power Automate, no Fabric pipelines), can't be shared, and can't be clustered. It's fine for a personal project. It is not fine as the thing your finance team's month-end reporting secretly depends on.

And that's exactly what happens. Someone builds a useful report, sets up refresh through personal mode on their desktop, the report becomes load-bearing, then they go on leave and their machine gets turned off. We've been called in to diagnose "Power BI is broken" at a mid-sized manufacturer where the entire root cause was a departed analyst's laptop being reimaged. If a dataset matters to more than one person, it goes through a standard-mode gateway on a server, owned by IT, with documented data sources. No exceptions worth making.

Where should the gateway live, and what should it run on?

Three rules cover most of it.

Close to the data, not the users. The gateway is the middleman between your source and the Power BI service, so network latency between gateway and data source is what hurts. If your warehouse is in your Sydney data centre, the gateway belongs in that data centre, not in the Brisbane office where the analysts sit.

On a server that's always on. A dedicated VM, not a shared utility box, and definitely not anyone's workstation. The gateway is happiest with solid memory and decent CPU; Microsoft's baseline of 8 cores and 8 GB is a floor, not a target. If you're doing heavy scheduled refreshes with big Power Query transformations, the mashup engine runs on the gateway machine, so all that data is flowing through and being transformed in its memory. We generally size 16 GB minimum for anything serious, and put the spooling directory on fast disk.

Not on the data source server. Tempting, because the latency is zero. But refresh spikes then compete with your database for CPU and memory, and you've coupled two failure domains. Separate VM, fat network pipe between them.

One genuinely under-appreciated fact: the gateway makes only outbound connections, to Azure Relay over 443. No inbound firewall holes. This is the answer to about half of the security objections that come up in gateway approval meetings, and knowing it in advance shortens those meetings considerably.

What about high availability?

Gateway clusters. Install the gateway on a second machine, join it to the existing gateway during setup, and the service distributes requests and survives the loss of a member. Since the gateway sits in the critical path of every scheduled refresh and every DirectQuery report that uses it, a single-node gateway means a single Windows Update can take out your reporting overnight.

Our honest take: most organisations don't cluster, and most of them get away with it for scheduled-refresh workloads because a failed 2am refresh just runs late. The maths changes completely with DirectQuery or live connections to on-prem sources, because then the gateway is in the path of every report interaction, and gateway down means dashboards down in front of executives. DirectQuery through a gateway without a cluster is a configuration we flag in every health check we run.

While you're at it, turn on additional logging and set up monitoring before you need it. The gateway publishes performance counters and logs that almost nobody looks at until the day everything is slow, and historical baselines are exactly what you'll wish you had.

Why is my refresh slow, and is the gateway to blame?

Sometimes. The gateway gets blamed for everything, and the actual culprit is roughly evenly split between three places.

The source query. If the SQL behind your dataset takes 40 minutes to run in Management Studio, no gateway tuning will save you. Check this first; it's the cheapest test you can do.

The transformations. Power Query work that doesn't fold back to the source gets executed by the gateway's mashup engine. A merge between two large non-folding queries will chew through gateway memory and crawl. The fix is upstream: push the transformation into the source, or fix query folding, rather than buying the gateway more RAM forever. This is the same "do the work as far upstream as you can" principle we bang on about in our Power BI consulting engagements, and the gateway is where ignoring it becomes visible.

The gateway machine itself. Underspecced VM, antivirus scanning the spooling directory, six other services on the same box, or one gateway serving every team in the company. Separating workloads onto different gateway clusters (one for heavy scheduled refresh, one for latency-sensitive DirectQuery) is a cheap structural fix that's saved several clients from a much more dramatic re-architecture.

A special mention for the classic mystery: refresh works in Power BI Desktop but fails in the service. Desktop uses your machine, your credentials and your drivers; the service uses the gateway machine, the stored data-source credentials and whatever drivers are installed there. The gateway VM missing the Oracle client, or an OLE DB provider, or having a stale credential after a service-account password rotation, explains most of these. Check the data source credentials in the gateway settings before you check anything else; credential expiry after a password policy change is the single most common gateway "outage" we see.

Who should own this thing?

The boring answer is the right one: IT operations, with a named owner, like any other piece of infrastructure. The gateway needs monthly-ish updates (Microsoft ships them regularly and old versions age out of support), credential management when service accounts rotate, and capacity review as workloads grow. The failure pattern is always organisational rather than technical: the gateway was installed by the BI team, IT doesn't know it exists, the BI person leaves, and eighteen months later an expired credential or an ancient version brings down month-end reporting and nobody knows where the gateway even runs. Recovery keys deserve a sentence too: the key set at install time is what lets you migrate or restore the gateway later. Store it in your secrets vault now. Losing it means rebuilding every data source definition by hand, and yes, we've watched a client do exactly that.

It's also worth remembering the gateway isn't just Power BI's. The same standard-mode gateway serves Power Apps, Power Automate, and Fabric data pipelines, which makes it shared infrastructure for your whole Power Platform and data estate, and one more reason it shouldn't belong to one enthusiast in the analytics team. As organisations layer more automation and AI onto operational data, the humble gateway quietly becomes part of the plumbing behind all of it, which is a theme that comes up constantly in our AI for business intelligence work.

The short version

Standard mode on a dedicated, well-specced VM close to the data. Cluster it if DirectQuery or anything business-critical flows through it. Keep it updated, vault the recovery key, give it a named owner in IT, and when refreshes get slow, suspect the source query and non-folding transformations before you suspect the gateway. None of this is hard. It's just the kind of unglamorous discipline that determines whether your reporting platform is reliable or whether it merely works most of the time.