On-prem enterprise gateway installation best practice

[andrewporter]

I have been reading the various Power BI On-Prem Enterprise Gateway installation docs, mainly https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-onprem-indepth. 

"If you have an Analysis Services data source, you’ll need to install the gateway on a computer joined to the same forest/domain as your Analysis Services server.

The closer the gateway is to the server, the faster the connection will be. If you can get the gateway on the same server as the data source, that is best to avoid network latency between the gateway and the server."

We want to use Power BI on-prem gateway to connect Power BI to our SQL Server instance running on Windows Server.

The article clearly states that if possible, install the gateway on my data source (Windows Server running SQL Server).

There is hesitancy from our infrastructure team who are concerned with the Power BI Gateway Service taking up server resources because it is polling Azure Service Bus and handling Power BI queries. Is this a valid concern? Should we lean towards installing Power BI On-Premise Gateway on a dedicated server, or should we be content with simply letting it run on our SQL Server instance.

[v-qiuyu-msft]

Hi @andrewporter,

The most import thing to consider is Requirements. If the server run the SQL Server meet the requirement, it would better to install the data gateway on the same machine. You can also install on a dedicated server if the network is good and that server meet that requirement. Personally, I would suggest you install on the same server which hosts SQL server instance if that server can afford.

Best Regards,
Qiuyun Yu

[Seth_C_Bauer · Accepted solution]

@andrewporter Valid concern, depending on the load that you are going to be putting on the gateway. Is the SQL instance the main source for all your data access needs? Will there be other datasources on the gateway, what type of connection are you using?These and many more questions are why "it depends" is the typical answer.

The recommendations are based on getting the best performance. Kasper De Jonge wrote up a nice blog that touches on some of your question here

If you are using your SQL DB, especially in Direct Query, then yes it would be more advantagous to have the Gateway on the same server. But the gateway will obviously consume resource and increase the SQL traffic, and it could be significant if there are many users interacting with the report in DQ mode, or if reprocessing the dataset takes a long time. Hope that helps a bit.

[andrewporter]

@Seth_C_Bauer - thanks for the reply! I would say the SQL Server is the main source of our data access needs for now. In the future we'll definitely have other data sources on the gateway though.

In terms of connections (I belive you are referring to an import vs Direct Query) we had planned to use Direct Query for more operational / transient reports. However for reports on larger datasets and year over year with calculations we were planning to import that directly to PowerBI / Azure SQL Databases wherever that lives.

[Seth_C_Bauer]

@andrewporter The approach you outline is solid. I would say that the admins should baseline server activity prior to installing the gateway so they have an idea of the traffic prior to installation. Based on Kasper's blog, if you are going to start adding other datasources that don't include the SQL Server, then maybe a different gateway should be installed in to serve those sources.

Additional load should be assumed however, and your admins should be aware of the activity. Adam Saxton has some great video's that he does around Power BI, and I think he did a few on ways you can monitor or troubleshoot issues with traffic. I'd check his stuff out at guyinacube.com and pass any relevant topics to your admins.