Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

2 Answers 2

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 record 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 records in the destination where the BINARY(8) number does not exist in the source table. The stored procedure then INSERTs records that do not exist in the destination database. This has the effect of only copying records 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.

share|improve this answer

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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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