Replication/ syncronisation 2 MSSQL db - 1 linked to as400 - best way?

Hi There,

I would like to have the opinion of you experts!

I have a data on the AS400 which is my actual source.
I have a webpplication that needs this data (read only).

Linking my webapp directly to the AS400 works but is quite slow and the link could drop now and then.
To be sure and to increase response time, I want to have the data from the as400 transfered to a MSSQL db, which I can use for my webapp.

I have setup 2 MSSQL databases. The first one (A) is already linked to the AS400 source, and the second one (B) should be updated by the first one (A).

I would use db B for my webapp.

I need to find out how I can replicate/synchonise the linked database A with database B (in almost real time). Also I don't want to have a risk for when the link to the as400 drops, database B gets empried or upset in one or another way.

Now does my plan makes any sense, are there better or more efficient ways to do this?

And what would be the best way to replicate or synchronise the MSSQL databases?

Thanks,
KJWe do something similar here (production data source is AS/400, web data source is MS SQL). I use DTS to migrate data from production to the SQL Server periodically (as frequently as every 15 minutes). It is far from a perfect solution, but it allows users to bang away merrily on the reports server (MS SQL) with no effect on the production (AS/400). Developing the rules for migrating the data can be complex, however.

Other solutions (linking, direct connect via OLE DB/ODBC) provide more real-time data, but may also negatively impact the performance of the production server.

hmscott|||Did you consider to setup a linked server from MSSQL?
And, do you replace all the data or do you compare first before you migrate and just update the records changed?

I had my webapplication connected directly to the As400 (via Client Access ODBC Driver) which gives indeed real-time data but is already slow. We are now using it with 4 test users only so you can imagine what would happen when we go live with xxx users.

Cheers,
KJ