Go Premium for a chance to win a PS4. Enter to Win


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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we’ll look at how to deploy ProxySQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

972 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