cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

Troubleshooting Gateway

On PBI desktop, I built a report that contacts 3 different SQL Servers:

  • Server 2
  • Server 3
  • Server 4

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? 

Thank you.

 

9 REPLIES 9
Highlighted
Helper V
Helper V

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.

Any clue? 

Highlighted

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 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.

Highlighted
Highlighted

Thankyou, I did not complete that step, indeed. Will try soon. Will that work even if each SQL instance is on a different server?
Highlighted



@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. 

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?

Highlighted

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"?

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted

@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

Cluster URI:WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID:4c600672-dd5f-429c-8eb0-5bb5e1f48f43
Request ID:8d86151f-5f22-4f09-9096-01ba68477466
Time:2018-07-28 09:31:21Z
Highlighted

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?

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted

@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.

Snag_855d3e3.png

 

HOwever, when I test the datasource connection, I get the error below, where basically the Server03 data settings cannot establish connection to the Server02:

Snag_870c6ff.png

 

Finally, if I try and refresh the dataset, I get the error below.

Error 40 is what SQL will throw when you cannot login.

 


Snag_85a6c86.png

 

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors