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

Posted on 2011-02-28
Medium Priority
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

590 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