Solved

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

Posted on 2011-02-28
5
1,002 Views
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).

Diagram
0
Comment
Question by:lorsungcu
  • 2
  • 2
5 Comments
 
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.
0
 
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.
0
 
LVL 1

Expert Comment

by:RealRaven
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.
0
 
LVL 2

Accepted Solution

by:
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.
0
 
LVL 2

Author Closing Comment

by:lorsungcu
ID: 35187286
Did not complete
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now