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];