Is it possible to use ApplicationIntent with Microsoft Access

Question

we host an MS SQL 2019 database using a read replica and were wondering if it is possible to use applicationintent=readonly in the Linked Table Manager connection string in MS Access to force it to use the read replica.

When I tried, it still allowed me to write data using the Access connection to the SQL table, so I am not sure if I did something wrong or if it is just not possible to do this in Access.

Please see connection string below:

DRIVER=SQL Server;SERVER=XXX;UID=XXX;PWD=XXX;APP=Microsoft
 Office;DATABASE=XXX;APPLICATIONINTENT=readonly;

Answer 1

This documentation lists all the requirements Connect to an Always On availability group listener

Read-only routing refers to automatically routing incoming listener connections to a readable secondary replica that is configured to allow read-only workloads.

Connections are automatically routed to the read-only replica if the following are true:

• At least one secondary replica is set to read-only access, and each read-only secondary replica and the primary replica are configured to support read-only routing.

• The connection string references a database involved in the Availability Group. An alternative to this would be the login used in the connection has the database configured as its default database. For more information, see this article on how the algorithm works with read-only routing.

• The connection string references an availability group listener, and the application intent of the incoming connection is set to read-only (for example, by using the Application Intent=ReadOnly keyword in the ODBC or OLEDB connection strings or connection attributes or properties).

Since you already specify Database and ApplicationIntent in your connection query, you only need to check for the listener and the read-only routing list.

You can check the listener with this DMV:

select
    *
from sys.availability_group_listeners as agl

And the read-only routing list with this snippet

SELECT 
    ar.replica_server_name AS RoutingListOwner
    , arr.replica_server_name AS ReadOnlyReplica
    , arorl.routing_priority
    , arr.read_only_routing_url
    , arr.secondary_role_allow_connections_desc
FROM sys.availability_read_only_routing_lists AS arorl 
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = arorl.replica_id
JOIN sys.availability_replicas AS arr
    ON arr.replica_id = arorl.read_only_replica_id
order by ar.replica_server_name, arorl.routing_priority