Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

572 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