Today I was testing a scenario with a readable secondary in an Availability Group (AG). The database in the AG contained a memory-optimized table and I was testing read-only query support on the secondary.

The mystery

While changes for on-disk tables are replicated near-instantaneously (of course in my test setup there was no other overhead) and were almost immediately visible on the secondary replica, I was surprised to see that it was not the case with the memory-optimized table.

For example, I would insert a row into the table at the primary replica. That row would not appear in the readable secondary, not even after waiting a few seconds. Note that there was no other workload related to memory optimized tables at the time.

Interestingly, if I would insert a new row in the table on the primary, it would cause the previously inserted row to show up at the secondary! I was quite puzzled to see this behavior. On a hunch, I issued a manual CHECKPOINT on the primary. This ‘flushed’ the queue and I was able to see both rows on the secondary.

Truth revealed!

Since this was a bit strange, I dug around a bit and found a gem in the documentation which explains this behavior. There is something called a ‘safe timestamp’ which is used on the readable secondary to only return rows older than a ‘safe point’ which is normally updated by the garbage collection thread on the primary.

Normally on an active system, this safe timestamp would be periodically changed and periodically sent across to the secondary on a regular basis. But if there is not much activity on memory optimized tables in the database (like in my case) then there is an inordinate delay in sending this safe timestamp across.

Conclusion

If such data latency for in-memory tables is not tolerable on the readable secondary, the product team recommends the usage of ‘dummy’ transactions to propagate this safe timestamp more periodically:

Changes made by transactions on the primary replica since the last safe-timestamp update are not visible on the secondary replica till the next transmission and update of the safe-timestamp. If transactional activity on the primary replica stops before the internal threshold for safe-timestamp update is crossed, the changes made since the last update to safe-timestamp will not be visible on the secondary replica. To alleviate this issue, you may need to run a few DML transactions on a dummy durable memory-optimized table on the primary replica. Alternatively, though not recommended, you can force shipping of safe-timestamp by running a manual checkpoint.

Be aware of this behavior if you plan to use readable secondary and have some phases where there is minimal activity in the system but you still need the changes to in-memory tables to show up on the secondary as soon as possible.