What does ApplicationIntent=ReadOnly mean in the connection string

Question

I am using MS Access to connect to Sql Server through a DSN connection. This is a linked table to a sql server backend. Here is the connection string

ODBC;DSN=mydsn;Description=mydesc;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=mydb;ApplicationIntent=READONLY;;TABLE=dbo.mytable

As you can see there is a ApplicationIntent=READONLY tag in the connection string. What does this mean. Am I connecting to the database in a read only fashion? Is it recommended to perform updates and inserts using this connection string?

Answer 1

This means that if you are using Availability Groups in SQL Server 2012, the engine knows that your connections are read only and can be routed to read-only replicas (if they exist). Some information here:

• Configure Read-Only Access on an Availability Replica
• Availability Group Listeners, Client Connectivity, and Application Failover

If you are not currently using Availability Groups, it may be a good idea to leave that in there for forward compatibility, but it really depends on whether or not you are intentionally only just reading. This should prevent writes but there are some caveats. These Connect items may be useful or may leave you scratching your head. I'll confess I haven't read them through.

• ApplicationIntent=ReadOnly allows updates to a database
• ApplicationIntent=ReadOnly does not send the connection to the secondary copy