next »

[SEOidiot]

Hello all!!

Hope you guys are all well and have had an excellent holiday break...

I have been working as usual (urghghghghghhhh)

I have a big project on this month and there is one section of it that has me completely stumped, any help would be amazing please

Customer has all their back office systems run off a MS-SQL database (customers, accounts, stock etc)
However....
They want their website to run off MySQL and use lots of the data from the MS-SQL

Whats the best way to move data between the two easily and reliably?

The MS-SQL database is on a box where we may not be able to add ODBC drivers... (Sigh)
The data will be written one way from the MS-SQL to MySQL as we are writing an API of sorts to write orders back into the MS-SQL database etc

Thanks Chaps

Paul

[perkiset]

Firstly, welcome back mate. Nice to see you about

The ODBC drivers would need to be on a box accessing the MSSQL base, not that box itself. I did this for a client a bit ago, where we have a legacy MSSQL base and application but we wanted to integrate with another company that uses MySQL, and then export data out in an XML format to yet another service.

I installed another little box with RedHat on it and the ODBC drivers, as well as MySQL and and rather lush PHP installation. First step was getting the box to talk to the MSSQL box which is not difficult, but was intricate, as a I recall. Once there, accessing it via PHP was quite easy.

If there's no automated solution out there, then if I had this task I'd probably create a table that held that last-imported record from the original database. In other words, the contacts table I have gotten everything from id 37114 on 12/23/09 ... so that I'd know easily what to scan for and retrieve. Of course, you could just look at the max(id) of the new table and see if you have everything from the old...

This is obviously trickier if you want to do an actual sync of tables that are thrashing, rather than just adding. Please tell me you don't need to write your own journaling system for the old database. But if you do, I believe I'd use triggers to place a record of the change into another table (a new one on the original database) and then periodically grab those changes, interpret them and apply them in the new. That's going to be fun.

Nuts might know of a way to get journaled records from a MSSQL db into a usable format... that'd save you a boatload of work.

[SEOidiot]

Hey mate

Thanks for that - no journal required thankfully

tbh i think ill outsource the setup of the box this ia all going on and the task of setting up the data copy
the thing i need to work out is.. as the MS-SQL is a database running a full erp system we cant just say if there are records that dont exist copy them over as there will be row updates going on all the time

im tempted to write views that only pull the smallest dataset possible
then at a set time overnight (its a UK / EU only site really) delete and replace the dataset on the MySQL db

concern is the big tables
item file = 75,000 records
sales analysis = 200,000 records

sigh

thanks

paul

[perkiset]

If the machines are next to each other on a gig network, then that's really not too bad of a transfer. Prolly take 10 minutes or so.

I think that's the right tact - daily replacement when you're unbusy.

Hope all's well with the kiddies and the holidays were as good to them as they were to mom and dad

[SEOidiot]

hey matey - holidays were good for laura but sandra and i had a trying time tbh....

the boxes are on different continents sigh

[perkiset]

Ooog. Less pretty.

Can you get a box next to it in any case? Or could you write a script that exports the data and then tar.gz's it so that you could pull it nightly efficiently?

Paging Nutballs: what's the programmatic export options for an MSSQL db?

(Sorry the holidays were trying ... ours were as well. A screwy year for most it would seem...  )

[nutballs]

hmm been out of mssql for a while now, but.

you can export to almost anything. Though the drivers for anything special would need to be installed. So in this case, mysql-odbc. But Idiot said he can't. So, im not really sure.
out of the box though, is SQL, access, excel and flat file. I think thats it.

replication however is a whole other issue.

[perkiset]

Well, but he could therefore go SQL (pure text) to a tar.gz which would be much smaller, draw that across the net and the pump it back up into his MySQL box... that'd be reasonably light on processor and bandwidth I'm thinking...