Troubleshooting Gateway

[EVEAdmin]

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.

[EVEAdmin]

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? 

[v-diye-msft]

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

[EVEAdmin]

@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

[v-diye-msft]

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?

[EVEAdmin]

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

[u02cm62]

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.

[EVEAdmin]

---

---

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

[EVEAdmin]

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

[u02cm62]

Please read this if it's not clear

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-high-availability-clusters