Re: Getting Data From Intranet (i) to Internet (MS SQL Server)

IMHO, it's worthwhile to purchase a solution for data replication
rather than roll your own...

Every roll your own solution I've ever seen had issues...

There's a reason data replication products cost $$$, it's not the
easiest task to do robustly.

There's lots of products out there, big $$$ from IBM or MS or...
But you might look at some of the smaller names:
http://www.hitsw.com/products_services/dbmoto/dbmoto.html
http://www.attunity.com/index.aspx
http://enterprise.replicator.daffodilsw.com/

There's even some open source
http://www.rubyrep.org/
http://dbreplicator.org/
http://enterprise.replicator.daffodilsw.com/ (not also available as a
commercial product above)

However, I'm not a fan of replication....

Ideally you could have a library with stored procedures (or views)
accessible to the app and make sure that only those objects are
accessible to the app.

HTH,
Charles

On Wed, May 25, 2011 at 12:14 PM, Mike Wills <mike@xxxxxxxxxxxx> wrote:

Okay, here is an interesting one and I am hoping someone has a better idea
of where to take this.

We will be writing an eCommerce application on the internet in ASP.NET. We
have are a local government org that sells licenses, permits, and of course
has utility bills. We currently take utility bills; but because of the
timeframe of getting it live, we duplicated an old method of completely
rebuilding the internet database nightly and collecting the transactions
nightly from a dump from the SQL server. I would like to get rid of many of
the layers of potential problems (FTP, multiple servers, multiple dump jobs)
and go as directly from the i to MS SQL as possible. MS SQL cannot see the
i, but the i can see the MS SQL server.

We will be collecting data from many tables and getting that customer data
on the SQL server. My idea is to put a trigger on each table we want to
track and log all transactions into one table. From there, have a .NET
application poll this table and process all new transactions. This table
would have a system key (to determine the set of tables), the system's
primary key, and what action was performed (add, change, delete).

On the other side, we'll have the transaction table that holds all of the
payments made. I am unsure about any other data that needs to be processed.

I realize this would probably be a prefect use of web services on the i. But
we would prefer to not punch a hole through the firewall from the internet
side (DMZ) to the intranet side if possible. Or can this be done securely
and avoid the possible problems with holes into our internal network?

Any thoughts?

--
Mike Wills
http://mikewills.me
--
This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.