Questions on Availability Group Readable Secondary

Question

Recently I encountered an issue in my SQL server. Please advise the concept and mechanism behind the AlwaysOn cluster.

Background:
We built up a AlwaysOn cluster with 1 primary node dbs1 and 1 secondary node dbs2. Given that they configured both readable secondary as "read-intent only". They have also created the read-only routing list. Every connections specifying "ApplicationIntent=ReadOnly" will be redirected to dbs2.

Issue:
The same agent job ( for insert data from mssql to MySQL) running on dbs2 is unstable - suddenly succeed and suddenly fail. For the success case, the agent session will be displayed on dbs1 instead of dbs2.

For failed case, it displayed an error that

"Unable to connect to SQL Server '(local)'" -- Agent Job History

"the Target database is in an availability group and is currently accessible for connections when the application intent is set to read only". -- SQL Error Log

But when we changed the readable secondary to be "yes", the agent job can always run successfully with the session on dbs2.

My Questions:

1) What is the behaviour of Read-intent only? Why the agent job failed?

2) Even it succeed, why the session is on dbs1 instead of dbs2, given that the agent job was set on dbs2? Does it relate to insert/delete/update command in the script?

3)What are the difference between read intent only and option yes on readable secondary? I have tested that the readonly routing in latter option still valid after specifying "ApplicationIntent= readonly"

I am really curious of their mechanisms. Please feel free to comment and discuss. Thanks.

Additional Information (newly update on 4 Aug 2017):

Below is my agent job script. Can I specify "ApplicationIntent=ReadOnly" on it? And I am confused that this T-SQL did not write any data in SQL Server table but write on another MySQL database. Why the availability group don't let me pass? Again, special thanks to Ben and SqlWorldWide's comment and feedback!

EXECUTE ( 'TRUNCATE TABLE MySQL_Table' ) AT MySQL_Server;

INSERT INTO MySQL_Server...MySQL_Table  (c1,c2,c3)  SELECT c1,c2,c3 FROM [dbs2].[SQL_Database].[dbo].[SQL_Table];

Answer 1

1. "What is the behaviour of Read-intent only? Why the agent job failed?" 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.

2. 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't connect with applicationintent=readonly

3. "What are the difference between read intent only and option yes on readable secondary?" The difference between "read-intent only" and "any" 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). "Any" 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 "Read-Intent Only" 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).

Answer 2

You can write a PowerShell script wrapper and specify ApplicationIntent=ReadOnly in the SqlConnection property.

e.g.,

$Server = "Server"
$Database = "Database"
$userName = "userName"
$password = 'password'
$SqlConn = New-Object System.Data.SqlClient.SqlConnection("Server = $Server; Database = $Database;User Id=$userName;Password=$password;applicationintent=ReadOnly")
$SqlConn.Open()
$cmd = $SqlConn.CreateCommand()
$cmd.CommandType = 'text'
$cmd.CommandText = 'put your T-SQL script here'
$cmd.CommandTimeout=0
$results = $cmd.ExecuteReader()
$dt = New-Object System.Data.DataTable
$dt.Load($results)
$SqlConn.Close()
$dt | Export-Csv -LiteralPath "W:\Logs\log.out" -NoTypeInformation