Data synchronization between MsSQL and MySQL
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
By the way, yet another way to synchronize that was not mentioned but provides probably the most instant synchronization, is by using triggers.
http://msdn.microsoft.com/en-us/library/y817hyb6%28v=vs.110%29.aspx
You have service template, so start there.
By the way, yet another way to synchronize that was not mentioned but provides probably the most instant synchronization, is by using triggers.
ASKER
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..
:-) "The determined Real Programmer can write FORTRAN programs in any language".
ASKER
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 :)
I'll keep in mind your suggestion and i'm gonna consider :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?...
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?...
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.
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.
ASKER
I'm going to analyze it, and also see the cost €€€, lol..
ASKER