How to get time offset between SQL Server master and replica?

Question

I have SQL Server database and I replicate all data to second server with SSD disks to increase reading speed and reliability.

I've faced a problem that sometimes replicated data become outdated because of delay in replication process.

I'm going to switch to master database if replica becomes outdated but I need to know the time offset between them.

Do you have any ideas how to implement this? I thought of inserting to special table every second and then checking the difference between last rows, but this way looks inefficient...

Answer 1

Instead of marking your replication events with a date and time, can you instead use ROWVERSION columns in the master database? Every time a record is updated or inserted, a column of type ROWVERSION is updated to a new unique value in the database. When you replicate your data, move the ROWVERSION fields to varbinary(8) fields in the replica database. You can then compare the ROWVERSION fields in master to the varbinary(8) fields in the replica to determine if data in master has changed. This would be a much more reliable way to detect changes.