FireDAC ReadOnly connection

[jauchriw 0]

Hello,

 

Microsoft SQL Server supports a read only connection when using availability groups:

https://techcommunity.microsoft.com/t5/sql-server-support-blog/connect-to-sql-server-using-application-intent-read-only/ba-p/317758https://techcommunity.microsoft.com/t5/sql-server-support-blog/connect-to-sql-server-using-application-intent-read-only/ba-p/317758

 

You have to add " ;applicationintent=readonly" to the connection string to enable the read only mode and get on the second replica.

 

Does FireDAC support this?

[Brian Evans 108]

Put the extra parameter in ODBCAdvanced for the FDConnection (under Params after selecting the MSSQL driver) or FDPhysMSSQLDriverLink.

Edited November 16, 2023 by Brian Evans

[VLDG 1]

You can also define a default transaction to your connection with options readonly to true.

 

In this case you have to be sure all dataset use this default transaction.

 

With Delphi 12 you can also use SecurityOptions https://docwiki.embarcadero.com/RADStudio/Athens/en/What%27s_New#FireDAC_and_Security

 

But for your case Brian answer is probably the best option.

[DonilZ 0]

Hello,

 

Did you succeed in creating a connection?

 

I tried both options:
1) Connection.TxOptions.ReadOnly = True (and Connection.UpdateOptions.ReadOnly = True)
2) MSSQLDriverLink.ODBCAdvanced := 'ApplicationIntent=ReadOnly' (also tried the full connection string)

However, the connection is still not created read-only 😞

 

[Dmitry Arefiev 101]

Look at http://docwiki.embarcadero.com/RADStudio/Athens/en/DBMS_Environment_Reports_(FireDAC)

1) What are the real connection parameters ? May be something is not specified correctly ...

2) What is the loaded MSSQL ODBC driver version ? May be it does not support ApplicationIntent  ...

[jauchriw 0]

Hello, sorry for the late answer.

 

The answer of Brian worked for me:

On 11/16/2023 at 3:02 PM, Brian Evans said:

Put the extra parameter in ODBCAdvanced for the FDConnection (under Params after selecting the MSSQL driver) or FDPhysMSSQLDriverLink.

 

Please note that the readonly connection is something complete different than just a readonly transaction.

 

The readonly connection automatically connects to the secondary node of our Always On sql cluster node and executes the queries there and not on the (writable) primary node.

This means that the primary node has a lower workload.

[jauchriw 0]

On 11/22/2023 at 3:54 PM, DonilZ said:

Hello,

 

Did you succeed in creating a connection?

 

I tried both options:
1) Connection.TxOptions.ReadOnly = True (and Connection.UpdateOptions.ReadOnly = True)
2) MSSQLDriverLink.ODBCAdvanced := 'ApplicationIntent=ReadOnly' (also tried the full connection string) 

 

Also try MSSQLDriverLink.ODBCAdvanced := 'Application Intent=ReadOnly' (with space)