The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.
See if your editor made the list.
COURSE of the MONTH
9 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Data synchronization between MsSQL and MySQL
Posted on 2015-01-16
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
2
10 Comments
Active 1 day ago
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).
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?
Active 1 day ago
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.
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..
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 :)
Active today
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.
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.
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?...
Active today
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.
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
- Databases
- AWS
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing.
Set up your basic HTML file. Open your form tag and set the method and action attributes.:
(CODE)
Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month9 days, 17 hours left to enroll
762 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.