Questions on Availability Group Readable Secondary
&quot;What is the behaviour of Read-intent only? Why the agent job failed?&quot; You're making a promise to the server that this connection won't try to write any data to the database in the availability group. If you do try to write, that write will fail as the database cannot be written to. I believe that you need to go through the availability group listener to get this behavior, though. You've configured your secondary to accept connections only from clients that a) go through the listener and b) specify the ReadOnly application intent. Since your error says it's trying to connect to (local), I suspect that it's connecting to the instance directly.

I believe this also relates to how you've configured your Agent job. What are you specifying for the connection string? If it hasn't specified ApplicationIntent=ReadOnly (or something similar), it will get directed to the primary.
If it is all reads, your connection string likely doesn't specify the database. It has to include the database name so that SQL Server can tell which routing list to use (your server could have multiple Availability Groups running on it).  See: AlwaysOn Secondary readable - can&#39;t connect with applicationintent=readonly

&quot;What are the difference between read intent only and option yes on readable secondary?&quot; The difference between &quot;read-intent only&quot; and &quot;any&quot; for a database in the secondary role determines what kinds of connections it will accept. The former says that it will only accept connections from clients that pinky swear that they're not going to write anything (i.e. specify ApplicationIntent=ReadOnly). &quot;Any&quot; says it'll take anything (writes will still fail, but the requirement of the client to say that they're not going to write anything is relaxed).




You asked in a comment:
Can I specify the connection string AppicationIntent=ReadOnly in SQL Agent Job T-SQL script? Also, I am still a bit confused by that behaviours, given that my insert statement indeed is targeting the MySQL platform instead of SQL Server. Why the &quot;Read-Intent Only&quot; settings cannot let me go, especially I am not trying to connect to (local)?

It depends on what kind of SQL Agent job you have. For example, I've used ApplicationIntent=ReadOnly in SSIS packages, but I don't know how to specify that for things like a standard Transact SQL job. As to why it failed, if you have set up your secondary to only accept connections that explicitly set their ApplicationIntent to ReadOnly and your job didn't do that, it will be rejected (even though you know that it isn't going to do any writes).