保持两个数据库与时间戳/行版本同步 [英] Keep two databases synchronized with timestamp / rowversion

问题描述

我有一个包含表A的主数据库和一个包含A的另一个副本的辅助数据库.每次我的应用程序启动时，它都会检查主数据库中表A的所有行，并更新辅助数据库中A的行. >

此丑陋行为的需要是对旧数据库的支持，但是每次启动时此操作开始的CPU成本都非常高. 我发现行已更新时可以存储一个时间戳(Microsoft也称为行版本).

因此，我的应用程序需要存储最后修改/插入的行的最后时间戳，并且在连续重新启动时，将仅在主数据库中查询数据库中已修改的行(或插入的新行).

这将大大加快速度，但是我将如何处理已删除的行? 谢谢

编辑:我只是注意到我仅以只读模式访问主数据库. 因此，我无法在原始数据库中添加时间戳，也无法以任何方式插入TRIGGERS.

总有一天我可以快速查看主数据库中的更改而无需修改吗?

解决方案

您需要一些方法来标记已删除的行，以便在从属端进行处理.最好使用触发器，当触发器被删除时，您可以将整行存储，也可以将(table，id)元组存储在另一个表中-调用新的deleted_rows表.

然后，当您的应用程序启动时，它将读取触发器填充的deleted_rows表，并将这些更改应用于从属数据库.完成操作后，请确保清除deleted_rows，这样您就不必再以后尝试重新处理这些记录了.

I have a primary database containing table A and a secondary database containing another copy of A. Each time my application starts it checks all the rows of table A in the primary database and updates the rows of A in secondary database.

The need for this ugly behaviour is support for a legacy database however this operation on each start is starting to be very cpu expensive. I have found out a timestamp (also called row version by Microsoft) can store when rows have been updated.

My application would need therefore to store the last timestamp of the last modified/inserted row and on successive restarts would only query the primary database for modified rows (or inserted new rows) from the database.

This would considerably speed things up, however how would I deal with deleted rows?? Thank you

EDIT: I just noticed I only access the primary database in read-only mode. I therefore cannot put a timestamp in the original database and I cannot in any way insert TRIGGERS of sort.

Is there someway I can quickly see what changed in the primary database without modifying it?

解决方案

You'd need some way to flag deleted rows for processing on the slave side. This might be a good case to use a trigger whereby when a row is deleted, you store either the whole row or maybe just the (table, id) tuple in another table - call that your new deleted_rows table.

Then when your app starts, it reads the deleted_rows table populated by your trigger and applies those changes to the slave db. Be sure to clear out deleted_rows when you're done so you don't bother trying to reprocess those records later.

这篇关于保持两个数据库与时间戳/行版本同步的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！