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?

  • If you have another SQL server, you could use merge replication to synchronize the data using web synchronization. The two compact editions would be the subscriber and the regular SQL server would be the publisher – user1207758 Sep 13 '12 at 19:54
  • @user1207758: Nope. Just two databases. – Will Sep 13 '12 at 20:02

From the development perspective, you could try the Microsoft Sync Framework.

For further information about Microsoft Sync Framework, refer to the following links:


I have a similar system in place to populate a database on a SQL Server Express instance with a subset of data from a SQL Server Enterprise instance.

For simplicity, I created TIMESTAMP fields on the source database, and modified the field definition on the destination database to the syntactically-equivalent BINARY(8) field type. I created a LINKED SERVER object on the source server to allow a stored procedure on the source machine to access the database on the destination machine.

Whenever a row is INSERTed or UPDATEd on the source server, the TIMESTAMP field is automatically updated to a new unique number. The stored procedure I created to copy data from the source to the destination deletes rows in the destination where the BINARY(8) number does not exist in the source table. The stored procedure then INSERTs rows that do not exist in the destination database. This has the effect of only copying rows to the destination server that have been added or changed since the last export.

One downside to this approach occurs through the use of a linked server for the export process. Querying a linked server can be inefficient since data from one side of the server link must be transferred to the other side of the link to either JOIN it or WHERE it. However, for my situation the network bandwidth is large enough, and the data being transferred is small enough that it works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.