Connect to Availability Group

[tiktoska]

Hi!

I am wondering how can I connect to a SQL Server using Availability Group Name with the option ApplicationIntent=ReadOnly!

Is there any way to enter this information in the Advanced Editor?

[kaulug]

Any update? I want Power BI to connect to my AlwaysOn database using ApplicationIntent=ReadOnly in its connection string. So that AlwaysOn will distribute this load to one of my read-intent replicas.

I think this is a very important feature for AlwaysOn - PowerBI integration.

[alex_chianuri]

You need to select "Enable Failover Support" on the screen where you define the query.  That appends MultiSubnetFailover=True and ApplicationIntent=Readonly to the connection string.  One caveat - it only seems to work for the Import, but not Direct Query option.  That last bit also seems to be a bug, not a feature.

[kaulug]

Can I configure powerbi.com to use ApplicationIntent=ReadOnly in its connection string? This way I can connect powerbi.com to my availability group and make secondary or any other replica respond to powerbi queries, not the primary replica.

I think this is one of the most important features for AlwaysOn - PowerBI integration. I do not want to connect to a replica by its real address, I want to delegate load distribution to AlwaysOn.

[Greg_Deckler]

I do not see that option listed for Sql.Database:

https://msdn.microsoft.com/en-us/library/mt260902.aspx

You should be able to use the availability group name of the SQL Server.

let
    Source = Sql.Database("AvailabilityGroupName", "DBName"),
    dbo_TableName = Source{[Schema="dbo",Item="TableName"]}[Data]
in
    dbo_TableName

[Ubbink]

Is there already a solution for the "application intent" error?

[Anonymous]

Has anyone found a solution for this yet?

[mcolb88]

Did this ever get resolved? This is very important for DirectQuery mode.