2

I have high availability group with two nodes and I ensure they have "synchronized commit" mode. So I assured they are identical in records of tables.

A developer reported a bug for different results of stored procedure is related to a report between primary and secondary node. At first, it was ridiculously funny for me due to knowing about my mode in HA but it is correct. The result sometimes is different.

I tried to save log for this difference so I created a table in master database which isn’t involved in HA and try to save the count of any tables are in the query( I save two numbers one count(*) as usual and the other one with "nolock" parameter to know about dirty records) but I have some log records show the tables are the same but the result of join of them is different in two nodes! Do you have any suggestion for me to discover what causes this distinguish ?

Improve this question
3
  • 2
    Synchronous commit ensures log records are hardened in the log on the secondary but still not necessarily data written to tables, this is implemented by a separate background process so there can still be latency between them.
    – sTTu
    Commented Jan 28, 2023 at 9:29
  • 1
    Also be aware that counts obtained with NOLOCK may be wrong not just because of uncommitted transactions but due to row movement during allocation order scans.
    – Dan Guzman
    Commented Jan 28, 2023 at 12:00
  • Also look at the query or procedure. It could depend on instance collation which could be different, or could have plan-dependent behavior, like TOP without ORDER BY.
    – David Browne - Microsoft
    Commented Jan 28, 2023 at 19:16

1 Answer 1

Reset to default
3

You'll want to read this documentation that covers a wide variety of items that can all contribute to this. The long and the short is, if you need exact point in time 100% up to date information, use the primary replica.

Notable excerpt, though:

The primary replica sends log records of changes on primary database to the secondary replicas. On each secondary database, a dedicated redo thread applies the log records. On a read-access secondary database, a given data change does not appear in query results until the log record that contains the change has been applied to the secondary database and the transaction has been committed on primary database.

Don't forget that secondary replicas also remap the isolation level to snapshot.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.