How can I switch data source in case of failure automatically using on premise gateway cluster?

[nihilistdbanana]

This is My PowerBI setup at the moment. Currently all the data sources are mapped to the path in the PROD File Server.

I want the data source to automatically switch to DR in case a file is not available in the PROD folder.

But the issue is that although the folder structure and the file names are the same in both, DR server has a different path, and I cant figure out how I can configure the data model or the on-premise gateway cluster to switch this automatically in case of failure.

I want to focus on the cluster because I also want to be able to switch to DR if the PROD File server or the PROD on-premise gateway goes down.

[v-echaithra]

Hi @nihilistdbanana ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[tayloramy]

Hi @nihilistdbanana, 

I'm not sure this is possible from the gateway. I think you'd need to set something up upstream of the gateway that handles the DR. 

In my environment, we use CNAMEs for our SQL server instances, so if something needs to fail over, we update the CNAME and then all connections are automatically switched. I'm no expert at storage management, but I wonder if something similar is possible at the storage layer? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.  

[v-echaithra]

Hi @nihilistdbanana ,

Power Query in Power BI supports conditional logic, allowing you to try loading data from one file path and fallback to another if the first one fails. You can use try, otherwise in M language to achieve this.
This code first attempts to load the file from the PROD server. If it fails for example, if the file doesn't exist or is inaccessible, Power BI will automatically try to load the file from the DR server. It is simple and self-contained solution within Power BI. No need for external infrastructure changes. There might be a slight delay when checking both paths. Additionally, this solution works for individual files and not the entire dataset if you're dealing with multiple files.

let
prodPath = "\\prodserver\files\myfile.csv", // PROD server path
drPath = "\\drserver\files\myfile.csv", // DR server path
source = try Csv.Document(File.Contents(prodPath)) otherwise Csv.Document(File.Contents(drPath))
in
source

Hope this helps.
Thank you

[ribisht17]

hi @nihilistdbanana 

Did you check this page ? 
Manage on-premises data gateway high-availability clusters and load balancing | Microsoft Learn 

Regards,

Ritesh

Community Champion

Please mark the answer if helpful so that it can help others

Dance-Sing with Data -BI & Analytics

[nihilistdbanana]

Hi @ribisht17,

Thank you for prompt response!

Yes I've seen this, but from what I understood this refers to the scenario where both the gateways in the cluster are pointing to the same data source. In my case the two point to two different paths. 

So when I configure the data model to access files I have to configure it with a path like "<Prod Server File Path>/file_name" and that full path is only in the prod. I also want to be able to use the "<DR Server File Path>/file_name" in case the file is missing in PROD.