5

I'm working on a project that utilizes the R2DBC library for reactive database access. However, I'm unsure if R2DBC supports the ApplicationIntent=ReadOnly parameter for SQL Server connections. This parameter is typically used in Microsoft SQL Server connection strings to indicate that the application intends to only read from the database, allowing SQL Server to redirect the connection to a read-only replica if available.

I've reviewed the official R2DBC documentation, but couldn't find any specific information about this parameter. I'm wondering if anyone has experience using R2DBC with SQL Server and can provide insights on whether R2DBC supports "ApplicationIntent=ReadOnly" or if there are any workarounds to achieve a similar behavior.

If R2DBC doesn't directly support this parameter, I'd appreciate suggestions on alternative approaches to ensure that R2DBC connections to SQL Server are treated as read-only, such as configuring the SQL Server connection at the database level.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Farinaz Kiani
  • 51
  • 4
  • So if you download the source code (`git clone https://github.com/r2dbc/r2dbc-mssql.git`) and search it for `ApplicationIntent` do you find anything? – AlwaysLearning May 29 '23 at 10:24
  • @AlwaysLearning No, also in the README file, specifically in the 'Supported ConnectionFactory Discovery Options' section, you won't find 'ApplicationIntent'. – Farinaz Kiani May 29 '23 at 10:50
  • From a 5 second Google it looks like it supports arbitrary key value pairs in the connection string so what happened when you tried it? – Martin Smith May 29 '23 at 11:48
  • @MartinSmith When I add this property to the connection string, it appears that it doesn't work because I receive the following error message: `io.r2dbc.mssql.ExceptionFactory$MssqlPermissionDeniedException: The target database is currently accessible for connections only when the application intent is set to read-only`. – Farinaz Kiani May 29 '23 at 13:18
  • 1
    `r2dbc-mssql` is a TDS client implementation, it doesn't wrap JDBC connections or anything like that. You'll notice in `Login7.java` that a `LOGIN_READ_ONLY_INTENT` flag is declared but it is unused. I think that's a pretty clear indication that it doesn't actually support `ApplicationIntent=ReadOnly`. – AlwaysLearning May 29 '23 at 20:59

1 Answers1

0

Thanks to @AlwaysLearning, After exploring various options and searching for solutions, I took the initiative to edit the mssql-r2dbc sources.

I delved into the codebase, making the necessary modifications to incorporate the support for the "ApplicationIntent=ReadOnly" parameter in SQL Server connections.

Farinaz Kiani
  • 51
  • 4