I am working with a client who is syncing between SQL Server and MySQL containing the exact same schema and data. We want to centralize that data into one database. Other then performance and maintainability issues, what else is bad about the original design?
1
votes
from one point of view, you're using an open source product which is a cancer destroying society. from the other point of view, you're using a microsoft product, which will crash your machine and kick your dog.
– Marc B
@MarcB little harsh :-) - besides open source just copies the commercial cancer design (ps total OSF fan)
– Adrian Cornish
I think you could not also call it a cancer destroyer despite of that fact, people are just becoming practical there are lots of features that an enterprise may offer when compared that of an open source but sometimes those features are not needed by people that it would be better to go for an open source whose list of features are enough to sustain once need, but somehow if you really don't care of being practical and could spend a lot of money to anything then no one would stop you. I think it would all depend on how you get to analyse and resolve a problem. ;)
– Christopher Pelayo
2 Answers
4
votes
You can create a linked server instance in SQL Server, with the MySQL instance.
Despite being completely proprietary, one of the nice connectivity features offered in SQL Server is the ability to query other servers through a Linked Server. Essentially, a linked server is a method of directly querying another RDBMS; this often happens through the use of an ODBC driver installed on the server.
Refer This article : step-by-step process SQL Server Linked Server to MySQL.
Providing you grant the MySQL user you connect on behalf of proper permissions, you can write to the MySQL instance accouding to you. So you can update stored procedures to do an additional step to insert records into MySQL.
Much easier solution is to use commercial application - Omega Sync from Spectral Core Omega Sync can compare and synchronize both database schema and table data. You can even synchronize data of heterogeneous databases (for example, compare your local SQL Server database with a MySQL replica on your web site - and synchronize all the differences in just a few minutes).
0
votes
on the otherhand I think you've already mentioned what possible problems you may encounter when synchronizing 2 db at the same time aside from this two I think it would be the resources. since there are different RDBMS working for the application they would also have a separate resources for each, like when I update a particular record of a user it still needs to check on which resource does it really exist, but I love to hear more from other people out there this is really an interesting topic to discuss. ;)
