Hi out there...
I have a problem which I need some help solving.
I have a server with MS SQL Server 2005 and a database which is sync. from a remote server, and therefor its readonly. My other server is with a MySQL server.
What I want to do is, make a sync. from my mssql to my mysql database.
I found that I have to make a connection to my mysql server in Linked servers through mysql odbc driver.
I found that i might could use triggers for the sync job, but my mssql database is readonly, so I can't make any triggers to it.
Is it possible to make a new database, and a view which takes data from the readonly database, and make some kind of trigger on the view ?
Or how do I proceed with this job ?
I'm not a prof. database man, but not either a noob, something in the middel.
I have not worked alot with mssql server, so please explain what you mean.
Couple of questions:
1) How many tables, views, etc do you need to synchronize? A lot or a little?
2) For these tables/views, etc. Is the volume of data large or small?
3) When you mean Synch to our MySQL database, do you just mean replicate over or is there a database there you are trying to synchronize or merge information with?
Thanks for your answer.... I will try answering your questions as good as I can.
1. It's 4 tables total I want to synchronize.
2. The volume of data depends on which table we are talking about, but the largest contains about 20.000 records.
3. What I want to do is to make an exact copi on the the MySQL server without empty the destination table before i put new records in.
The way I do it now is that I "drop" the destination table, and rebuild it with the new information from the readonly MSSQL database.
But this is not a good solution, becuase the MySQL database is a backend for a webpage.... So I need to add the new information and alter the exsiting data, without dropping the table. I don't really know the difference between synchronization and replication. Hope this gives you an idea of what I want to do.
I would have the following suggestions for you.
1) While the model you have is definately not perfect because you have to drop a table it does keep it simple. If you are interested in keeping it close to this one thing you could do is change the way your copy script works so that you copy into a table "Production_temp". Once the copy is done, simply drop your "Production" table and re-name the "Production_temp" table to "Producton". This way the only down time is a fraction of a second while the table name(s) are changing.
2) Use a SQL Server Job or a SSIS package to write a script that selects and inserts only new records into your MySQL database. You should still be able to used your lLinked Servers and just chnage the way the script works.(although if you use SSIS and can use another type of ODBC/OLE DB driver I would suggest this as Linked Servers have poor performance.)
Insert Into [MyTable via linked server] (col1,col2,col3....)
Where x NOT IN (Select col1 From [MyTable via linked server])
There are quite a few variations you can do on this model for te sake of performance, let me know if this helps and if not send over more info.