Re: Direction for Keeping Web DB up to date

We are in the process of deciding how to keep a MS SQL Server database used by our website more up to date. Right now all processing and data entry happens on the i5. Then nightly there are jobs on the MS SQL Server that pulls data over in mass. They basically wipe the tables on there side and reload them. We are looking to do away with this process and do something more 'real-time'. This will apply to about 20 tables.

Two suggestions currently on the table are Triggers and Journals. We already have triggering happening on 3 of the tables that is used for a different process. There are currently no tables that are journaled. We are also concerned with which one will have a bigger impact on system performance.

Triggers: I was thinking that I could write the trigger programs to write entries to data queue(s). These dtaqs are watched by 'maintenance' programs that would update the MS SQL Tables 'directly' using JDBC. We already have separate RPGIV programs doing this type of thing to MySql. So we have expertise in house to handle this coding. And we can add any needed business rules into these RPGIV programs.

Journaling: We would have to add journaling to the needed tables. We are thinking and have in mind a 3rd party tool that would 'watch' these journals and apply any needed changes to the MS SQL server side. There is no in-house journaling experience. And we are constrained by what the tool can and cannot do.


Thoughts?

Thanks,
Scott J.