Re: Mysql and MSSQL

["Tom Metro-12" <user=71@nabble.blu.org>]

 Michael Webb wrote: 
> What happened is that they created a table that needs to be either wiped 
> and re-created on a weekly basis or updated.  The file they uploaded to the 
> table via csv is about 32mb, so their instructions to us is to cut the file 
> into 8 parts and upload it as a csv then run some updates to the table. 

So to restate, for clarification, you have a remotely hosted MySQL 
database with a table that you need to keep in sync on a weekly basis 
with a database table you have stored locally? 

It seems one low-tech approach to pull off this kind of one-way sync 
without opening up new ports and potential security risks is to setup a 
cron script (or the equivalent) locally that exports the table to a CSV 
file, pushes it to the remote host via rsync, which will minimize the 
amount of data transmitted if the file is largely static, and finally 
imports the data into MySQL. 


> If it was a sql server I would write either a trigger on 
> a table that is created... 

Trigger on which table? The table in your local database? Your stated 
criteria was a weekly update. Do you need or would you benefit from a 
near-real-time update of the remote table? 


> ...and connect via odbc. 

I suspect you won't get too many recommendations for running ODBC over 
the Internet unless you tunnel it through some sort of VPN (like SSH). 


> Am I wasting my time? 

I think automating the process is well warranted, but you need to decide 
what you would gain from a solution more complex than a scripted batch 
upload. Do you need more frequent updates? Would uploading a 32 MB file 
(pretty small by modern standards, and is that compressed?) cost you 
more in time and money? 

Other options to consider are various tools and products for database 
replication, though it sounds like they'd be overkill for your needs. 

  -Tom 

-- 
Tom Metro 
Venture Logic, Newton, MA, USA 
"Enterprise solutions through open source." 
Professional Profile: http://tmetro.venturelogic.com/

-- 
This message has been scanned for viruses and 
dangerous content by MailScanner, and is 
believed to be clean. 

_______________________________________________ 
Discuss mailing list 
[hidden email] 
http://lists.blu.org/mailman/listinfo/discuss