Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Oct 7 '11 at 21:37

This question came from our site for professional and enthusiast programmers.

    
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? –  MatthewMartin Oct 5 '11 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 '11 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?) –  MatthewMartin Oct 6 '11 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. –  MatthewMartin Oct 6 '11 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 '11 at 18:51
show 2 more comments

2 Answers

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:

share|improve this answer
    
+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. –  Justin ᚅᚔᚈᚄᚒᚔ Oct 5 '11 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 '11 at 16:05
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.