Did you do this? You should use the same cluster on all 3 boxes.
Load balance across gateways in a cluster
You can choose to let traffic be distributed evenly across gateways in a cluster. By default, the selection of a gateway during load balancing is random.
For example, to provide load balancing from the Power BI service, select the gear iconin the upper-right corner, then selectManage gateways. Next, selectDistribute requests across all active gateways in this cluster.
This will allow you to use any of the 3 then to refresh.
At this point, I am unsure as to whether it is actually possible to do what I am doing.
When I read this thread or this one, it seems it is not possible. It seems the dataset can only contact 1 gateway. It does not matter if the additional gateways are on the same cluster.
As it stands, it seems I must have 1 cluster for each single gateway installed on each single SQL server. Then, I can refresh each dataset from each SQL server, but only individually. Which prevents me from building a worldwide reports and restricts me to a continental report.
So, the question, is it possible to refresh a dataset with data coming through different gateways, assigned to the same cluster?
That is what I did. I first put each gateway under the same cluster.
After I add the datasource details, it does say Connection Successful along with a message that say it could not connect to the datasource. The error details refer to something similar to the following
I just installed again the gateway, not in personal mode, on SERVER03. The gateway has been added to the Cluster created with the primary account, installed on SERVER02. I also added the datasources and mapped them again the gateway.
Everything looks fine.
HOwever, when I test the datasource connection, I get the error below, where basically the Server03 data settings cannot establish connection to the Server02:
Finally, if I try and refresh the dataset, I get the error below.
Error 40 is what SQL will throw when you cannot login.
If I install each gateway in its own cluster, it all works. BUt, I cannot build a report that grabs data from each SQL of those SQL servers. I'd be very happy to find out what I am doing wrong.