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.