Most secure method to replicate SQL between firewall

Donald_Gibson
Donald_Gibson used Ask the Experts™
on
We are installing a SQL database in the Process Network.  This database needs to be accessible from IT.  We do not allow communication from IT network to Process Network directly – a jump through the DMZ is required for security.  Our policy will require a second database in the DMZ.  

Process Network SQL-
The database on the Process Network will send information daily (30-minute to 2 hour increments) to the DMZ SQL database.  This is basically who received how much weight of a certain product for billing. PN SQL dB will also receive updated driver information from DMZ SQL.  This is the Master database.

DMZ SQL-
This needs to have the same information as the Process Network SQL database.  IT personnel will connect to this SQL server to update driver information.  Reports will be sent upstream when data is received from the Process Network SQL database.

The vendor of this SQL software (TopTech) has recommended sending a flat-file via FTP when a value is created/updated in the dB.  My concern here is security and database not being up to date.  Local IT has suggest we open up a port between the two SQL databases for instant replication.

Does Experts-Exchange recommend us to use flat-file via FTP to keep these two databases up-to-date or is a more dynamic approach desirable?  What are the pros/cons of each?  Where does security fall into this?

Thanks,

Donald

toptech.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Use SFTP instead of FTP.  It is encrypted and, unlike FTPS, requires only a single open port on your firewalls.

Author

Commented:
Is Secure FTP still more secure than allowing an open locked down port?

I'm looking for the most secure way to update two databases with a firewall inbetween.  Also looking for recommendations on pros/cons of each methods - flatfile vs dynamic updates.
Commented:
Be careful that when you are reading resources that mention "Secure FTP" that they are talking about SSH based SFTP and not FTPS which is plain FTP over SSL.  The former requires only one port and is a different protocol.  The advantage of either SFTP or FTPS over a plain non-VPN port is that even if someone managed to access the data stream the actual data would be encrypted.

As to how you should keep the remote copy in sync, that probably depends on if you database is updated multiple times per day or multiple times per minute... and exactlhy how exact you need the synchronization to be... what is an acceptable lag, that sort of thing.

Author

Commented:
We would like the data to flow downstream within 5 minutes but when data (billing information) flows ups stream, we want that to be less than 1 minute (instantaneous would be ideal).  

Would Secure FTP be more secure than allowing SQL to replicate over a given port and locking down that port?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial