AS400/iSeries replicating to SQL Server 2005

I need to replicate data from an AS400/iSeries to SQL Server 2005. How can we do this? Can anyone recommend a product that might be able to do this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if you need a permenant replication (not a one time thing) you can use ibm websphere federation server
it's a product by ibm that is able to connect to many data sources including db2 and sql server and oracle etc...
and you can use it to perform replication to sql server
Dave FordSoftware Developer / Database AdministratorCommented:

I like SQL Server's own DTS. It's extremely effective, and (best of all) you already have it.

-- DaveSlash
Gary PattersonVP Technology / Senior Consultant Commented:

DTS is an SQL Server 2000 technology that is replaced in SQL Server 2005 by SQL Server Integration Services (SSIS).  Websphere Federation Server and SSIS are both options for replication, and there are many other tools and techniques, as well.  One particularly cool product is HiT Software's DBMoto product:  It provides shapshot, one-way mirroring, and two-way mirroring capabilities:

There are numerous other tools, too, and costs vary dramatically.  There really isn't, in my opinion, a single "one-size-fits-all" solution.  When we design these sorts of replication solutions for our clients, there are a number of factors that we consider:

1) Application requirements.  Is this a development or QA system that just needs an occasional update to refresh test data?  IS it a data warehouse that needs to be updated with monthly or weekly sales figures?  Is it a customer order-tracking system that needs to be updated instantaneously?  Perhaps you have a mix of these requirements.
2) Scheduling of updates.
3) Volume of data.
4) Data security.
5) Type and speed of connection between DB servers.
6) Snapshot, one-way morroring, or two-way mirroring of changes?
7) Reliability.
8) Ability to quickly verify that databases are in sync.

The "best tool" depends several of the above factors.  The key factor is how up-to-date you need the replica to be.  Do you need every update immediately reflected in the replica (mirroring), or do you plan to reload or push updates less frequently (snapshots)?

Do you need to replicate in one direction only (iSeries to SQL server), or do you need to be able to do two-way mirroring (updates to either system get pushed to the replication partner)?

If you would like to give us a little more background on your requirements, we might be able to give more specific guidance.

- Gary Patterson
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

stanfordaAuthor Commented:
Sorry... I should have also noted that we need replicate one way only with iSeries as a source and SQL Server 2005 as destination and that we need changes on the iSeries to be visible on SQL Server in near real time... so about a 30-60 second delay would be OK. We were going to get DB Moto, but they were really messing us around, so we are looking at alternatives. I had wondered about SSIS/DTS, but could not get my head around how to get it running in near real-time so it only downloaded the changes as they occur on the iSeries. It seems to me that what ever we do it is going to need to be monitoring the journal on the iSeries to see the changes as they happen. We are hoping to get some good feedback from the experts exchange. In the meantime I am looking at something called SQL DRPLus from a company called Starquest. Thank you for the input so far.
you could use triggers in the iseries db2 in order to capture the changes to the replicated tables and write these changes to "change tables", that is, tables that will allow you to replicate the change to sql server
then you can have an ssis package that is constantly running and polling these change tables and apply the changes (and keep track of which changes were applied already)
Gary PattersonVP Technology / Senior Consultant Commented:
I have written and maintained homegrown "data replication" applications in the past, using triggers as momi_sabaq has suggested, and by writing AS/400 journal monitors.

Inevitably, something happens and replication gets out of sync.  If it is important that the databases stay in sync, you really want to get a reliable commercial solution that can verify database synchronization, and restore sync if an error occurs.  That is a lot of code to write and test in a homegrown application, and testing failure scenarios is very complicated.

Look at DBMoto from HiT software.  It does exactly what you need, plus offers additional capabilities such as two-way mirroring for future applications.

- Gary Patterson

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stanfordaAuthor Commented:
Thank you for your input
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.