Developer here. I have a system where there is a front machine creating rows in a moderately complex database, and a back machine that must sync with the front machine to run reports. Both run SQL Server Compact databases.
Currently, we pull (and back up) the database to the back. But now it takes too long due to network latency and database size, so we are evaluating our options. One of them is to add a timestamp/rowversion column to each table. We can then just pull updated/inserted records and apply them accordingly.
Is using the timestamp to grab all new/updated records from the front to update/add them to the back a viable solution? Will the timestamps stay in sync, or will the back get new timestamps when the records are applied?
Or is there another option I am not aware of?