I'm currently working on a Database Synchronization project and I wondered if anyone had any ideas other than what I've got so far. The context of this project is that we have a central server which stores data for 10 different locations in a consolidated database. Each store contains a replica of that database, but with only data relevant to their own operation.
Store owners can change data at the store level or chain owners can change data at the central level, but for either change the other level has to be notified. I currently have 2 ideas on how to do this.
Note: I am using MS SQL Server 7 for the central, but MSDE (Desktop Edition) for the store levels. So, replication from the store to the server is not available. I think I need to actually code this on my own.
When we are writing data to the central or the store level we use ASP.NET and some SQLConnection class to query the database. I could write a descendant class of SQLConnection that when an insert, update or delete occurs, a log is kept of these transactions and another program will pick up these transactions and execute them on the appropriate machine for synchronization.
We could also parse the MS SQL Server log files at the end of each day and look for transactions that will affect the synchronized database, but I'm not sure how to parse this log files, or if it will be slow to do so as there may be a lot of transactions that are not updating any data.