Data is transferred hourly and per minute. Sub-minute data transfers may even be necessary in the future. We seem to be FTPing too much, and it is growing.
Would database-database interoperability (e.g., SQL Server to Oracle via Oracle Gateway), text file gen via SQL SELECTs generally be more robust and scalable, considering all the functional/processing power available in enterprise database environments?
Would database-database interoperability be less hit on net traffic/load than FTP?
It does have its issues, though. Performance, security, integrity, availability and scalability are some of them. Concurrent data transfers can put a significant strain on FTP servers. FTP is not inherently secure. You open yourself up when you allow this type of access. FTP Servers cannot balance many connections across a path of redundant servers. They also typically do not scale well.
You may have to upgrade your hardware if demands on the systems become huge. If you can use your database to transfer data, then I would certainly lean towards this method, though you may open yourself up to other problems. An incorrectly scripted SQL statement can kill the I/O you have on your system. You also leave yourself open to database corruption and/or access violations on the data side. Though neither is appealing, I would rather someone break into my OS than my database.
You may also want to look into other FTP-type servers that may give you some of what you need. Here's a URL that does comparisons of various types of FTP Servers that might be helpful to you:
This was first published in May 2003