Very interesting question ..
Based on my understanding, I will try to answer this.
Other experts can correct me wherever I am wrong.
AlwaysON is different from Database Mirroring especially when sending the log blocks to the secondary replica/s.
The difference is how the log block gets send to the mirrored instance (for mirroring) or replica (for AlwaysON).
For Database Mirroring
- it flushes the log block to the disk and once completed locally, it sends the log block to the secondary server.
For AlwaysON
- it flushes the log blocks in parallel to the secondary using a secondary log scanner on primary.
- The beauty is that the secondary could have hardened log blocks even before the primary I/O completes which increases performance and narrows the NOT IN SYNC window.
- Now the interesting part is that to monitor above scenario, there are progress messages. The secondary sends messages to the primary indicating the hardened LSN level. The primary uses that info to help determine synchronization state.
So, there is no alert mechanism like Mirroring to actually be able to know the oldest unsent transaction. The only way to know is using DMVs. and possibly Extended Events.
As a side note, you can use Policy Based Management to monitor your AlwaysON health.
Below is the query that I am using to monitor my AlwaysON environment :
/************************************************************************************************
Author : Kin Shah
Purpose : Find "How far is secondary behind primary"
Written for DBA.STACKEXCHANGE.COM
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Disclaimer : Any scripts found on internet you must irst
- understand what it is doing
- then test it if it suits your requirements
I am not responsible for any data loss or any blue screen that you might get.
^^^
*** USE THIS AS PER YOUR OWN RISK ****
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
*************************************************************************************************/
SELECT AGS.NAME AS AGGroupName
,AR.replica_server_name AS InstanceName
,HARS.role_desc
,Db_name(DRS.database_id) AS DBName
,DRS.database_id
,is_ag_replica_local = CASE
WHEN DRS.is_local = 1
THEN N'LOCAL'
ELSE 'REMOTE'
END
,AR.availability_mode_desc AS SyncMode
,DRS.synchronization_state_desc AS SyncState
,DRS.last_hardened_lsn
,DRS.end_of_log_lsn
,DRS.last_redone_lsn
,DRS.last_hardened_time
,DRS.last_redone_time
,DRS.log_send_queue_size
,DRS.redo_queue_size AS 'Redo_Queue_Size(KB)'
/*
if the last_hardened_lsn from the primary server == last_hardened_lsn from secondary server
then there is NO LATENCY
*/
,'seconds behind primary' = CASE
WHEN EXISTS (
SELECT DRS.last_hardened_lsn
FROM (
(
sys.availability_groups AS AGS INNER JOIN sys.availability_replicas AS AR ON AGS.group_id = AR.group_id
) INNER JOIN sys.dm_hadr_availability_replica_states AS HARS ON AR.replica_id = HARS.replica_id
)
INNER JOIN sys.dm_hadr_database_replica_states DRS ON AGS.group_id = DRS.group_id
AND DRS.replica_id = HARS.replica_id
WHERE HARS.role_desc = 'PRIMARY'
AND DRS.last_hardened_lsn = DRS.last_hardened_lsn
)
THEN 0
ELSE datediff(s, last_hardened_time, getdate())
end
FROM sys.dm_hadr_database_replica_states DRS
LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
ORDER BY Db_name(DRS.database_id)
,is_ag_replica_local
References :
"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.
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
"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).
Best Answer
This sounds like you might have a long running transaction on the primary replica. Run this query to find any transactions that have been running longer than 10 mins:
Once you find the transaction, either complete it, or kill it.