Forum Discussion
Azure SQL DB Hyperscale - Connect to High Availability (HA) Read Only Replica in Power BI
Hello,
We just switched our Azure SQL DB service tier from serverless to hyperscale. With the hyperscale architechure, you have the option of creating a high availabilty (HA) read only replica of the database. In Hyperscale databases, the ApplicationIntent argument in the connection string used by the client dictates whether the connection is routed to the read-write primary replica or to a read-only HA replica. Is there a way to pass the ApplicationIntent argument to power BI in the connection string to make use of the HA replica? Microsofts documentation gives an example of using the argument in a C# application, but it would be great it we could make use of this in Power BI as well. Link to documentation below.
-- Connection string with application intent
Server=tcp:<myserver>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;
2 Replies
Hi, BrandedSaiyan ;
Step-1:Choose the server name and database name;
Step-2: Click “Advanced options” button and then “Additional Connection Parameters” and select “Enable SQL Server Failover support”. Then click OK.
This enables MultiSubnetFailover (fail over function in MS availability group) and set ApplicationIntent to ‘read-only’ (to use the read-only replica of SQL DB).
Bonus – by enabling MultiSubnetFailover option in the process, this gives you all the added benefits of Always-on Availablity groups too, which ensure the availability of your reports and disastrous backups.
Step-3: Click “Apply changes” button.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you for the response. From your answer, it would appear that all I need to do is check the "Enable SQL Server Failover Support" box under Advanced Options.
Is there a way I can update this via the REST API or XMLA endpoints? I don't see this as a field returned from the Get Datasources REST endpoint, and I'm unsure of which field I'd be looking for in $SYSTEM.TMSCHEMA_PARTITIONS or $SYSTEM.TMSCHEMA_EXPRESSIONS. Either way your response is very helpful, hopefully there is a way to mass update this across the tenant.
