0

I am currently utilizing SQL Server with an Always On Available Group. In my AG, I have a primary active node and a secondary node used for read only queries. To access the secondary node, we pass ApplicationIntent=ReadOnly as part of the connection string. This works great under normal circumstances in our .net code.

I was recently asked to setup a datasource in AWS QuickSight pointing to our Secondary readonly node utilizing ApplicationIntent=ReadOnly. Unfortunately the datasource setup does not allow for the addition of any connection string properties outside of server, port, and database name. We have reached out to the AWS QuickSight team and added a feature request. But we dont have an ETA on if and when that could be completed.

So what I am looking for here is a workaround, hack, or some other magic idea to get this to work. I am currently looking into the AWS Quicksight CLI to see if there are any additional parameters that are not built into the UI. Another thought I had (and I dont even know if this is possible), would be to create some sort of SQL Proxy/Forwarder which could be an intermediary piece of software that could append the ApplicationIntent=ReadOnly config parameters to the connection string.

Does anyone have any other ideas? Are there any other features of SQL Server that could solve this? Is my Man in the Middle software idea even possible? Or is there something else I could create to do this?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Jon
  • 2,129
  • 7
  • 23
  • 31
  • You can directly specify the host that holds the read only replica. you won't get automatic failover though. – StrayCatDBA Jun 02 '23 at 19:11
  • @StrayCatDBA, I was under the impression that you cannot specify the read only replica directly without paying for an additional node in licensing costs. Is that incorrect? – Jon Jun 02 '23 at 19:23
  • enabling it for reads incurs the licensing cost, not how you connect to it. – StrayCatDBA Jun 02 '23 at 19:25
  • @StrayCatDBA, I just attemped to set it up using the replica directly and received an error message saying: The target database is in an availability group and is currently accessible for connections when the application intent is set to read only. – Jon Jun 02 '23 at 19:40
  • Sorry, I forgot that's how that works. I think you're stuck. There are other options if you're using something different than AG's (e.g. log shipping or mirroring), but nothing easy nor realtime. – StrayCatDBA Jun 02 '23 at 19:51
  • The connection string is used to configure the client driver, it doesn't get sent through to the database server for interpretation. Specifically, in client drivers that support TDS v7.4, the `ApplicationIntent=ReadOnly` connection string property causes the `TypeFlags` in the `LOGIN7` message to get the `0x20` bit set. So, you're either going to have to wait patiently for AWS to do their thing or try to find some other workaround. – AlwaysLearning Jun 02 '23 at 23:37
  • Does this aws thing allow other odbc sources? You could implement some sort of wrapper driver/server that sits between aws and your replica machine. In jdbc it would be *relatively* easy but I don't know how hard it is in odbc / oledb/ whatever aws uses – siggemannen Jun 03 '23 at 11:25

0 Answers0