Cluster gateways

[mamra4]

Hi,

I have a power bi with two ms sql server datasource. The servers are in defferent network.
I tried to install on-premise data gateway on the first server and on the second, install it as cluster.

Everything looks fine, but refresh failed with error 26.  "Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified"

The error is for the second server. The instance name is correct.

I also try with demos power bi to reload data from each server seperated, with success ( one datasource per file and no-cluster data gateways ). So there is no problem with sql connection.

Gateway cluster:

 

Also tried with Distribute request across all gateways checked.

Gateways: 

Datasource on semantic model:

 

When I configure the connections, I can't choose somewhere the gateway, but only the cluster. 

 

So in connections I can see Partially Online and the status in Lineage view some times in Unable to connect, some times sucess but always refresh failed!

 

same server

 

 

 

So, is possible to load data from two different servers with cluster or cluster is only for load balancing? If this is not the right way, how I can load data from two different sql servers?

Thanks in advance,
M

[v-heq-msft]

Hi @mamra4 ,
Based on your description, first, make sure that both SQL Server instances are configured to allow remote connections. Error 26 is usually caused by not allowing remote connections. For Power BI Services or Power BI Desktop, you can connect directly to each data source. If you need to load data from two different SQL servers, you can create multiple data connections in Power BI Desktop, each corresponding to a SQL server. You can then combine this data using Power BI's data integration features, such as Merge, Append, or Copy/Paste data. Gateway clustering is primarily used for high availability and load balancing, but it can also facilitate connections to multiple data sources across different networks, provided that each gateway in the cluster has access to its designated data source.

Manage on-premises data gateway high-availability clusters and load balancing | Microsoft Learn
Plan, scale, and maintain a business-critical gateway solution | Microsoft Learn
Troubleshoot the on-premises data gateway | Microsoft Learn

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin · Accepted solution]

1. DO NOT install a Power BI Gateway on a VM that runs anything else. They will mercilessly starve each other of memory and compute power, and will quickly kill your disks/SSDs. Use dedicated VMs for your gateway cluster members.

2.  While geographically diverse gateway cluster VMs are good for business continuity they are usually bad (very bad) for performance due to the network latency differences.  Ideally your gateway cluster members should be logically and physically close to your main data sources.

3. If you have firewalls between your different networks you need to factor in the maintenance cost of opening ports between the networks. Remember that gateway cluster members must have free line of sight to the data sources. Gateways have no idea what a VPN is or how to establish one dynamically

4. Remember that all on-premise data sources in a semantic model must be served by the same gateway cluster. That means if your networks are sufficiently walled in then you may need separate gateways in each of the networks.