Automatically setting on read-only application intent

[humada05]

Question

We have a couple customers that wanted us to enable read-only application intent for our connections to their instances. After adding this, we thought this driver setting could apply to other customer databases as well, but we hit issues trying to do that. Specific examples were wtih mirrored instances and CT not being supported for secondaries.

Is it possible for the MSSQL driver to automatically switch between read-only and read-write application intents? We were intending to create this logic ourselves, but its quite difficult to understand when a database is able to accept read-only intent connections.

note: I ask this same question here: #2332. I wasnt sure if creating an issue or using the discussion section was more appropriate for this repo.

[tkyc]

@humada05 Just clarifying here to make sure I understand. So, when the database only accepts read-only intent, you want to automatically switch the connection to a read-only application intent?

[humada05]

@tkyc something like that would work for me. Though defaulting to read-only intent (regardless of what DB accepts) would be more ideal for us since our connections do not require write access to the database.

[tkyc]

@humada05

Though defaulting to read-only intent (regardless of what DB accepts) would be more ideal

I'm still not clear on how that isn't the same as you setting the application intent to read-only in the connections yourself, if that is the case then I don't think it's appropriate to make the change on the driver's logic. From your initial posting, I'm assuming if you do set the intent to read-only, you're encountering issues with your secondaries? Is that right? And if this is the case, wouldn't this happen anyway even if the driver defaults the intent to read-only?

[humada05]

From your initial posting, I'm assuming if you do set the intent to read-only, you're encountering issues with your secondaries?

Yes exactly.

And if this is the case, wouldn't this happen anyway even if the driver defaults the intent to read-only?

Right, so it does happen and we have to retry the connection after resetting the app intent (either to read-write or read-only, depending on the error). My main point here was if it was possible for the driver itself to reconfigure the settings so that a connection would always be established successfully.

[tkyc]

@humada05

Hm, to do what you said, the driver will need knowledge beforehand from the server about the application intent. And that means having a successful prior connection to do so. This looks impossible to do...

[lilgreenbird]

In addition, this isn't really the responsibility of the driver and that's not how the this feature is meant to work. To do what you described that would be something that would be implemented on the client application. Here's a link to the doc on High Availability for your reference for more description on this feature. https://learn.microsoft.com/en-us/sql/connect/jdbc/jdbc-driver-support-for-high-availability-disaster-recovery?view=sql-server-ver16

[David-Engel]

To add some clarification, Application intent is only something that would be known by the application, not the driver. Only the application knows whether it needs read-write. The setting is passed to the server during connection and is used by availability groups to route incoming connections to an appropriate server (read-only or read-write). It's not meant as a signal in the other direction (server to client) for what kind of server has been connected to.

[lilgreenbird]

@humada05 hope this answers your question, pls let us know if you have more questions otherwise we will be closing this issue soon.

[humada05]

Hi all,

Thanks for all the input. It sounds like this is logic that more suited for the client application to implement. I will go ahead and close this ticket then.