3

I am currently in discussions with DBA and IT Security staff within my company, and am being told that the only way to securely link to our master database (MSSQL behind a firewall) to our web apps is to make a backup file, FTP the file to the web server, and run a script(s) to restore that imported backup file to a database hosted outside the firewall (and then import the data to MYSQL). I am having a hard time believing that this is the best solution. Ideally, I would like to have a two-way direct link between the master MSSQL server and a MySQL database that server web apps, and have those database sync. Can anyone point me toward such a solution, or is it true that we need to rely on FTP to cross the firewall?

Thanks!

7
  • So, updates happen where? If they happen on the copy of the db that is on the webserver, then that is the one source of truth & the valuable data. The stuff locked behind the firewall is merely a backup unless it has other apps behind the web updating it. Is the website readonly? Oct 5, 2011 at 19:41
  • The primary source of updates is internal processes that are constantly running (the web is primarily used for reporting). That said, there are a handful of cases in which web users can/should be able to make updates--right now those are all manual processes.
    – ctrane
    Oct 6, 2011 at 13:58
  • If the web site is readonly, for reporting only, and if the user of the website only needs data as of a day ago, then you have a typical reporting/datawarehouse scenario and this is indeed the way it is done. Do your web users need realtime data? (e.g. are they checking to see what happened 10 minutes ago, like stock prices, or are they checking things like annual sales, which change slowly?) Oct 6, 2011 at 14:01
  • EDIT: Sorry, just read the sentence about occasional edits. Who owns the data anyhow? In data warehouse/reporting scenarios, usually the reporter doesn't own the data. So they have to negotiate with who does own the data as to when and how updates are done. Oct 6, 2011 at 14:05
  • Thanks again Matthew. We do own the data. A search for the terms reporting/datawarehouse led me to an article on transactional replication, which sounds great. Unfortunately, I was not able to find any good information on security risks of replication across firewalls (I found lots of articles about setting up replication across firewalls--which obviously tells me that people are doing it). Do you by chance know of an article/book/manual that addresses the issue?
    – ctrane
    Oct 6, 2011 at 18:51

2 Answers 2

3

That sounds like a nightmare.

Why try to keep two databases in sync? There's nothing wrong with having internet-facing web applications directly accessing a database server. I'm no networking expert, but I think is how the typical setup works:

  • Web applications deployed on internet facing servers in DMZ
  • SQL Server hosted on server in internal network
  • Database requests are forwarded by the firewall from the web server (DMZ) to the specific IP and port of the database server (internal)

There are definitely other configurations, but this is the one I'm somewhat familiar with.

Just make sure the usual precautions are taken care of:

2
  • +1. As long as there is no public access to the database server, and your web application is sufficiently protected from running malicious code, there's no reason to keep separate "web" and "master" DB servers. Oct 5, 2011 at 20:59
  • Thank you for your comments. That was my original thought, however I don't think there is any possibility of getting the others to buy in to directly accessing the "master" DB from the web (our DBA has a colleague that told him he would be fired if he suggested such a thing--that left quite an impression with management). That is what led me to ask about replication. I know that replication is possible, I just can't find anything that addresses the firewall concerns. It just seems that there would be a way to satisfy the security concerns and get the data connection I need.
    – ctrane
    Oct 6, 2011 at 16:05
0

As you know, importing tab delimited files into MySQL is the easy part. Getting MSSQL data exported into a format that is compatible with MySQL is a whole different problem. Once you have the tab delimited files correct, you can import them like so:

TRUNCATE TABLE App;
LOAD DATA LOCAL INFILE 'App.tab' INTO TABLE App LINES TERMINATED BY '\r\n';

To get data out of MSSQL you need to :

  1. Export the MSSQL data schema to a .sql file
  2. Massage the .sql schema file into a format that works with MySQL
  3. Export each table to its own .tab delimited file named after the table and matching the data you have in step 2.
  4. Push all these files to DEV-PRODUCTION server.
  5. Use the MySQL LOAD DATA function to import everything.

All of this is scriptable using a batch file and using mysql.exe in -remote mode. An example of remote mode is here (but this example is a export rather than an import like what you need):

mysqldump --databases myDB -v -h zzzzzzs9c49.us-east-1.rds.amazonaws.com -u 
user -P 3306 -p > rdsexport.sql

Also, maybe take a look at this just in case it would work: SymmetricDS

You must log in to answer this question.