Forum Discussion

MIN's avatar
MIN
Regular Visitor
4 years ago

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

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?

 

Need Help

9 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      MIN
      Regular Visitor
      4 years ago

      Hello BA_Pete ,

       

      Thank you so much for your suggestion.

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

      Regards,

       

      Min

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        4 years ago

        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