[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Data synchronization between MsSQL and MySQL

Posted on 2015-01-16
10
Medium Priority
?
3,879 Views
Last Modified: 2015-04-06
Hello Experts,

I have to synchronize data between a MySQL database in a website and a MsSQL from an ERP.

I have the knowledge to do it in several ways, but i need an advice from someone that as been trought this waters before or as more experience than me.

What road should i take?..
1. Should i build a webserver with a php based app with cron jobs inside my client infrastructure and connect the website by webservices using json and update both MySQL and the MsSQL ERP databases this way?
2. Should i write a windows C# program that starts automatically with windows, with a timer that checks and connects the website by webservices using json and update both MySQL and the MsSQL ERP databases this way?
3. Should i use a tool from MsSQL to do this, a DTS, ODBC, etc?
4. Should i look for a tool already done for this purpose that is worth the money spent?

Should i use json? xml?... i like more json, is more simple and fast to implement :P

Thx in advanced,

Miguel
0
Comment
Question by:justaphase
  • 5
  • 3
  • 2
10 Comments
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 1200 total points
ID: 40554681
Use whatever you feel more comfortable with. I personally would do it by DTS. Speaking of your #2, even better is to write it as a service. Speaking of #4, https://dbconvert.com/convert-mssql-to-mysql-sync.php, http://www.symmetricds.org/ (open source).
0
 
LVL 1

Author Comment

by:justaphase
ID: 40555364
I never created a service with C# on Visual Studio... i tried once, but failed on understanding the logic or how to begin with... have any direction to point me to? any good article?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40555600
Here's whole chapter on MSDN, with walkthrough:
http://msdn.microsoft.com/en-us/library/y817hyb6%28v=vs.110%29.aspx

You have service template, so start there.new project
By the way, yet another way to synchronize that was not mentioned but provides probably the most instant synchronization, is by using triggers.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
LVL 1

Author Comment

by:justaphase
ID: 40556062
No.. triggers or DTS won't do it.. i need to manipulate the data programmatically.. and although i made several things with the SQL language and the SQL engine, i don't think it's strong enough to do it.. C# or PHP will..
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40556102
:-) "The determined Real Programmer can write FORTRAN programs in any language".
0
 
LVL 1

Author Comment

by:justaphase
ID: 40556107
LOL... believe me, i did very complex and wild things in MsSQL and MySQL, specially in MsSQL...
I'll keep in mind your suggestion and i'm gonna consider :)
0
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 800 total points
ID: 40562209
Joining the following options:
3. Should i use a tool from MsSQL to do this, a DTS, ODBC, etc?
 4. Should i look for a tool already done for this purpose that is worth the money spent?

------------------------------------------------------------------------------------------------------------
5. Should i use a tool from MsSQL already done for this purpose so I don't need to spend money?

Yes. Have you ever heard about Integration Services (SSIS)? Is the evolution of DTS (SQL Server 2000) and give you plenty of options from the simple and direct Import/Export between two sources to the more complex ETL where you can have unlimited sources.
0
 
LVL 1

Author Comment

by:justaphase
ID: 40564514
Good point Vitor,
going to see that..

Does SSIS bidirectional sync? Two tables, one in MySQL and another in MsSQL and both update each other, how does SSIS manage that?...
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40564529
Yes, it's bidirectional, or tri, or quad, .... :)
Depends on what you want. It's very flexible.

You just need to have the client driver installed to connect to non-SQL Server databases and you'll see it's very easy. Just add components and if you want to have more complex solutions it allows you to add .net code.
0
 
LVL 1

Author Comment

by:justaphase
ID: 40564566
I'm going to analyze it, and also see the cost €€€, lol..
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

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