How to connect to any available database when primary one is offline

[MIN]

I have 2 identical SQL databases on 2 server. PBI gateways are installed on each server and registered under same cluster for alternate routing purpose.

I setup connections to each database and count rows of each identical tables to detect the connection is OK aiming to select one of any available database.

The issue is, when primary database is offline, "try Sql.Database("address","database")" do not catch the error and the schedule refresh fails showing "Invalid Connection Credentials"; error code = -2147467259 and it was not able to route to next database.

In this case, can we catch that error in Power Query OR is there any solution for routing the request to any online database for redundancy purpose?

 

[BA_Pete]

Hi @MIN ,

You should be able to start your M code like this:

let
    Source1 = SQL Server 1,
    Source2 = SQL Server 2,
    pickSource = try Source1 otherwise Source2,
    xformSteps = // the rest of your code, using pickSource as the previous step in the first step

Pete

[MIN]

Hello @BA_Pete ,

Thank you so much for your suggestion.

However, "try" statement is still sticking to Source1.

Regards,

Min

[BA_Pete]

Hi @MIN ,

I don't see the issue here, it's just asking you to confirm the credentials isn't it?

Confirm the credentials and these will be saved in the PBIX Data Source area, then the switch should work fine.

Pete

[MIN]

Hi @BA_Pete ,

Actually, I'm trying to connect to any of 2 identical databases hoping that 1 of them is accessible in case the latter is offline.

For that reason, I shut down the db1 (Source1) -

          

to test if "try" statement catchs the error (offline status) and routes to Source2.

Instead, db1 prompts again to provide credential when it goes offline.

On the other hand, I'm not sure it is a correct way to setup a dynamic connection on PBI.

Regards,

Min

[BA_Pete]

Hi MIN,

I wonder whether adding a step below each source will force a recognisable error to initiate the switch. Try something like this:

let
    Source1 = SQL Server 1,
    chgTypes1 = Table.TransformColumnTypes(Source1,{{"numberColumn1", Int64.Type}}),

    Source2 = SQL Server 2,
    chgTypes2 = Table.TransformColumnTypes(Source2,{{"numberColumn1", Int64.Type}}),

    pickSource = try chgTypes1 otherwise chgTypes2,
    xformSteps = // the rest of your code, using pickSource as the previous step in the first step

Pete

[MIN]

Hi @BA_Pete,

Thank you for your time.

It seems like "try" do not skip whenever the expression is valid and prompts for credential.

Regards,

Min

[BA_Pete]

Hi MIN,

You've not added a new step in your screenshot, you've just surrounded your original connection string with the TransformColumnTypes and AddColumn functions. Please try it exactly as per my example, using a column that is known to exist in the table when the source is connected, not by creating a new one. Creating a new column to transform like this completely negates the point of the process as it won't throw a 'column not found' error, it will defer to the source credential error.

Try going through it like this:

1) Bring both sources back online.

2) Create a connection to Source1 and provide valid credentials if prompted.

3) Add a new custom column called 'Source' with the value "Source1".

4) Pick any column in that table and change the data type to create the chgTypes1 step.

5) Add your Source2 connection and select this step in the Applied Steps list to load. Provide valid credentials if prompted.

6) Add a new custom column called 'Source' with the value "Source2".

7) Pick any column in that table and change the data type to create the chgTypes2 step.

8 ) Add a custom step with the pickSource step code.

9) Check the [Source] column to see which source is being loaded from at this point.

10) With the pickSource step still selected, take Source1 offline and Refresh Preview.

11) Check the [Source] column to see which source is being loaded from at this point.

The only other way I can think of to force the switch would be to change the error evaluation and switch at step 8 ) above slightly by adding a separate testSource step and changing the pickSource step, something like this:

let
    Source1 = SQL Server 1,
    chgTypes1 = Table.TransformColumnTypes(Source1,{{"numberColumn1", Int64.Type}}),

    Source2 = SQL Server 2,
    chgTypes2 = Table.TransformColumnTypes(Source2,{{"numberColumn1", Int64.Type}}),
    
    testSource = try chgTypes1,

    pickSource = if testSource[HasError] then chgTypes2 else chgTypes1,
    xformSteps = // the rest of your code, using pickSource as the previous step in the first step

Hopefully going through the steps like this will get this working for you. If not, then it may be an issue with how the sources are set up on your gateway(s) preventing the correct credentials from being used.

Pete

[MIN]

Hi @BA_Pete,

Thank you very much for your thorough suggestion.

I have tried it and found the same issue as earlier.

Regards,

Min Thein

[BA_Pete]

Hi MIN,

This is quite odd. I have no problems switching between sources in the ways that we've discussed here.

Maybe try one more thing:

1) Create a brand new query that only points to Server1, and make any transformations you need. Let's call this 'queryOnServer1'. Disable Load for this query.

2) Create a brand new query that only points to Server2, and make any transformations you need. Let's call this 'queryOnServer2'. Disable Load for this query.

3) Make sure both queries connect and are authenticating in their own right.

4) Create a third query that starts like this:

let
    Source = try queryOnServer1 otherwise queryOnServer2
    ...

...or like this:

let
    testSource = try queryOnServer1,
    pickSource = if testSource[HasError] then queryOnServer2 else queryOnServer1,
    ...

5) Test by then taking Server1 down and see if this works.

I also notice that you don't seem to have any navigation steps in your query, which is where you would normally define the schema and table that you want to import. Not sure if this is relevant, but appears strange. Also not sure what your 'AddSourceColX' steps are doing.

Pete