Stored procedure/trigger to synchronize MS SQL tables with non identical tables on linked MySQL database?

Posted on 2011-02-28
Last Modified: 2012-05-11
I have two databases on separate servers.  One is MS SQL on SQL Express 2008, the other is MySQL 5.1.49.  I have an application that needs to write to the MS SQL database, but I have another that needs to use that information, but also needs to use the MySQL database.  So far I have linked the MySQL database using the ODBC 5.1 driver, but am at a loss at what to do next.  Should I look at creating procedures and scheduling it?  Should I trigger an update on a second, identical database on the mysql server, then use mysql to update the real database with information from the temporary one?  Should I be trying to trigger everything to go straight to the mysql db?  This is not what I normally do, so please, I am starting from scratch knowledge wise (especially with MS SQL).

Question by:lorsungcu
  • 2
  • 2
LVL 15

Expert Comment

by:Simon Ball
ID: 35004985
what are your applications written in?  can you edit those?  or does this have to be done via SQL?

Do you need to find "new" records in one data set and append them to the other?

If so, it looks like you need to write a find unmatched sql (select * from tbl1 left join tbl2 on tbl1.key1 = tbl2.key1 where tbl2.ke1 isnull) - where tbl1 is the one with the records and tbl2 has the "gaps"...

then use the contents of that query to make a version of the record set you are going to append.
LVL 15

Expert Comment

by:Simon Ball
ID: 35005024
Ideally you'd only want one database for which both systems tap into.  I don;t know how a linked server mysql works on SQl server... but it really sounds like you should have one database serving both those functons, rather than 2 different databases which you are going to copy data between.... however, idealism aside, in the real world itsnot always possible to have the ideal solution.

so you're going to have to append(find unmatched) both ways e.g. are there records in mysql to copy to MSSql, and vice versa?

Once you have some SQL for find records in eitherand adding them to the other, you can reverse it and put both chunks into a stored procedure on the SQL server, which you can then set to run from the server agent on a schedule of your choosing...  *assuming sqlserver 2008 express lets you use the scheduler/agent...)

I'd recomment creating some audit table too which records @@rowcount from each append and a timestamp and the direction, incase something goes wrong later and you need to look at whats been happening.

Expert Comment

ID: 35017856
I can see two solutions. Neither is pretty but they will do the job.

1. Have all applications write to both databases

You could connect your application to both servers and write identical data to both servers. No need to connect the databases or have something in between.

2. Copy data between them with a standalone application

You could create a new application to shuffle data.
Let each change to tables that needs to be copied have a trigger that stores the data in  a outgoing copy table then let the new application read from that table and put in the other database. Then delete the row from the outgoing copy table.

There are benefits to both solutions. The second one can run on specific hours or all the time and the normal applications will never have to wait for the write in two databases.

As a bonus your new application can rewrite pars of the data or have a coded filter for what data gets written.

Accepted Solution

lorsungcu earned 0 total points
ID: 35156843
We ended up recommending the customer use the MySQL DB from their internet sales app.  I was never able to get MS SQL and MySQL talking properly.

Author Closing Comment

ID: 35187286
Did not complete

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question