How to Synchronize SQLServer Database and MySQL Database

Question

My Scenario:

I have two applications. First one is a website which is connected to MySQL Database and 2nd one is a Desktop Application which is connected to SQL Server2008 R2 Database. The Desktop application updates records locally and the MySQL database is updated online though the website.

Problem:

Two different databases, how can we update at the spot when changes are made either in MySQL or SQL Database?

What I Want:

Databases should be synchronized to each other (e.g. if changes are made in MySQL then SQL server database should be updated, or if changes are made in SQL Database then MySQL database should be updated)

Could anybody please suggest some code, any idea, or any solution to solve this issue?

Hello, StackOverflow is for asking questions that do not solicit debate and have definite answers. — Dustin Kingen
– Dustin Kingen, Commented Jul 3, 2013 at 12:40
That being said you could look into using some kind of message queue (NServiceBus RabbitMQ, etc), which is what I did at one of my contracts. — Dustin Kingen
– Dustin Kingen, Commented Jul 3, 2013 at 12:40
You could try replication with triggers - stackoverflow.com/questions/1455018/…. — Alex
– Alex, Commented Jul 3, 2013 at 12:41

Farhan · Accepted Answer · 2013-12-17 07:19:13Z

1

Make use of Restful API's to Update information from MS SQL server to MYSQL server.

answered Dec 17, 2013 at 7:19

Farhan

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ryan1234 · Accepted Answer · 2013-07-04 00:12:17Z

One of the first things I would point out is that complete and perfect syncing is not possible. Unfortunately there will be data types that exist in SQL Server that don't exist in MySQL and vice versa.

But assuming the data types are pretty simple and the schemas are similar, here are some options:

Use a service bus. You can write an application that monitors both database systems and when it sees a change, it pushes an object onto the service bus. Listeners to the service bus will see the objects and write them to the appropriate destination.
Use triggers like Alex suggested. SQL Server can have CLR code execute on a trigger. The CLR code could be some C# that writes directly to MySQL. Takes some setup, but it's possible. I've investigated running a process from a trigger in MySQL and all options are ugly. It's possible, but security is a major concern. The idea is that a record is changed, trigger is fired and an external process is run.
Write an application that constantly looks for "diffs" in tables and moves data back and forth. You'll need to modify all tables to make sure there is support for date/time stamps for each record so you can track when a record has "changed".

Collectives™ on Stack Overflow

How to Synchronize SQLServer Database and MySQL Database

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related