Does anybody know of a tutorial on the web, explaining how to replicate from a microsoft database over to mysql.
Thanx in advance.
J
ms sql replicate to mysql
Do you mean to get MS SQL to automatically do it every so often, or to copy a MS SQL db over to MySQL permanently?
every so often.
MS SQL has some replication tools, but they're really designed to be used with 2 MS SQL servers. The idea being if you're in a production environment and server A goes down, server B is immediately available to be brought online without a hiccup.
Using ISQL (a command line tool) you can issue SQL statements against the database and export the results (and set this up as a scheduled task). MS SQL also supports scheduled tasks from withint SQL server (at least in SQL 2000, SQL v7 may be able to do this too). You could setup a query/stored procedure/view to do something at regular intervals too.
As far as I know, MS SQL is not happy about interacting with MySQL (or any other DB really). So this means you'll need to write some code. This could be as simple as:
- Having a script read data out of MS SQL and insert it into MySQL
- Have a script that tells MS SQL to dump the data and use the mysql command to mass import the data (which MySQL does very fast)
With MS SQL 2000, I know its possible to have stored procedures launch shell commands, so in theory, you could have MS SQL setup to dump the data to a file and then launch MySQL and its import tool.
Of course as you may know, MySQL doesn't support views, stored procedures, and triggers. So the only thing you will be doing is just backing up the data.
I'll have to take a look into it , as you described.
Im busy moving the whole system from NT over to Linux - and this is going to take some time. I want it to run itself stable, before we flip the switch for good.
Thanks for the advice - I'll go do a bit more research on it.
You could get MS SQL to output the data as a csv file and then get mysql to input that. And you could do it all using PHP.