Connecting to SQL AG using ReadOnly routing from Azure Analysis Services

Question

We are using Azure Analysis Services Tabular model. It is connected through a gateway to an On Prem SQL Server Availability Group.

We recently added read only routing to the Availability Group and have a web app that with a connection string that adds "ApplicationIntent=ReadOnly" for our read queries.

We are able to verify the queries are hitting the read only node from the web app, but have not had the same luck with the refresh of our AAS Tabular model. The queries continue to refresh from the ReadWrite node rather than the ReadOnly (verified with sp_whoisactive on the db).

This is our connection xmla (with sensitive info renamed)

"dataSources": [
  {
    "type": "structured",
    "name": "someconnectionname",
    "connectionDetails": {
      "protocol": "tds",
      "address": {
        "server": "servername,portnum",
        "database": "mydbname", 
    "applicationIntent": "ReadOnly"
      },
      "authentication": null,
      "query": null
    },
    "options": {
      "commandTimeout": "P5D"
    },
    "credential": {
      "AuthenticationKind": "UsernamePassword",
      "kind": "SQL",
      "path": "servername,portnum;mydbname",
      "Username": "myUserName",
      "EncryptConnection": false,
      "PrivacySetting": "Private"
    }
  }

I've tried editing the applicationIntent: ReadOnly to

"Application Intent": "ReadOnly"

"ApplicationIntent": "ReadOnly"

"applicationIntent": "ReadOnly"

"applicationIntent": "Read Only"

And then tried moving the application intent to just below the "connectionDetails" as well and tried the same variance.

The kicker is that when I execute the query it accepts the parameter just fine, but then the queries are still showing up on the writeable node when I check using "sp_whoisactive".

(the query does fail if I place the intent into the "options")

Please let me know if you have an answer. Thank you!

Okay. So if you happen to run into this, finally got this guidance from MS on Github. github.com/MicrosoftDocs/azure-docs/issues/22374 — Tojamismis

Tojamismis Tojamismis · Accepted Answer · 2019-03-29T15:05:05

And then we added the multiSubnetFailover=true into the json like so. What is interesting is that we don't actually have MultiSubnetFailover set on the AG, but this makes the structured connection type work.

Refer to the guidance here on Github

"dataSources": [
{
"type": "structured",
"name": "someconnectionname",
"connectionDetails": {
  "protocol": "tds",
  "address": {
    "server": "servername,portnum",
    "database": "mydbname", 
    "applicationIntent": "ReadOnly"
  },
  "authentication": null,
  "query": null
},
"options": {
  "commandTimeout": "P5D",
  "multiSubnetFailover": "true"
},
"credential": {
  "AuthenticationKind": "UsernamePassword",
  "kind": "SQL",
  "path": "servername,portnum;mydbname",
  "Username": "myUserName",
  "EncryptConnection": false,
  "PrivacySetting": "Private"
}

}

Connecting to SQL AG using ReadOnly routing from Azure Analysis Services

1 Answers