Over Web Service, Update A Table From Another Same Table Which Is In Different Location

Question

Hi,

I have two different database. One of them, original database and another one is cache database.

This databases are in different location. Ones a day, I must update cache database from original database. And I must this update progress with a Web Service which is working on Original Database machine.

I can it with clear all Cache DB Tables and Insert Original Datas in every progress. But I think is a Bad scenario. So how can I this update progress with efficiency. And have you any suggestion.

Thanks.

Answer 1

I'm pretty sure that there are DB syncing technologies out there, but since you already have the requirement, I'd recommend to use a change-log.

So, you'll have a "CHANGE_LOG" table, to which you insert rows whenever you do "writes" on your tables (INSERT,UPDATE,DELETE). Once a day, you can apply these changes one-by-one to the cache DB.

Deleting the change-log once it's applied is okay, but you can also confer "version" to the DBs. So each change to the DB will increment the version number. That can be used to manage more than one chache DBs.

To provide additional assurance for example, you can have a trigger in the cache DB that increment their own version numbers. That way, your process can inquire a cache DB and will know what changes must be applied, without maintaining that in the master DB (that way, hooking up a new cache DB, bringing up a crashed cache DB up to date is easy, too.).

Note that you probably need to purge the change log from time to time.

Answer 2

The way I see it you're going to have to grab all the data from the source database, as you don't seem to have any way of interrogating it to see what data has changed. A simple way to do it would be to copy all the data from the source database into temporary or staging tables in the cache database. Then you can do a diff between both sets of tables and update the records that have changed. Or once you have all the data in the staging tables drop/rename the existing tables and rename the staging tables to the existing table names.