On PBI desktop, I built a report that contacts 3 different SQL Servers:
Server 2 is already running the "on premises Gateway", in the cluster A. Data refresh is working properly. Everything is fine, so far.
The question is: how do I need to install the gateway on Server 3 and Server 4? Shall I add them to the same cluster A? or each gateway to its own cluster?
Can't go past the current situation.
If I install the gateway on each server and I use the same cluster, refreshiing the dataset, from 3 sources, will fail.
If I install the gateway on each server and create a new cluster for each server, refresh will also fail.
It will only work when I run the query runs on a single data source. From the example above, I can refresh data that come only from Server 1 or Server 2 or Server 3.
If the dataset runs queries on different servers, then it fails, no matter what.
Hi @EVEAdmin ,
No need to create new cluster for them, you can put them under same cluster.
After addind them to data source setting in gateway, could you plz make sure the it prompts "Connection succesfully"?
@v-diye-msft thank you
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
Hi @EVEAdmin ,
There's only one gateway works in one time (unless On-premise and Personal ), did you put all the source info under same gateway? rather than different gateways?
@v-diye-msft thank you again.
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.
Did you do this? You should use the same cluster on all 3 boxes.
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 icon in the upper-right corner, then select Manage gateways. Next, select Distribute requests across all active gateways in this cluster.
This will allow you to use any of the 3 then to refresh.
@u02cm62 thank you
Unfortunately, that did not fix the situation.
At this point, I am unsure as to whether it is actually possible to do what I am doing.
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?
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.