SQL Server 和 MySQL 同步

2022-01-22 00:00:00 synchronization mysql sql-server

我正在与一个在 SQL Server 和 MySQL 之间进行同步的客户端工作,该客户端包含完全相同的架构和数据.我们希望将这些数据集中到一个数据库中.除了性能和可维护性问题,原始设计还有什么不好的地方?

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?

推荐答案

您可以在 SQL Server 中创建一个链接服务器实例,与 MySQL 实例.

You can create a linked server instance in SQL Server, with the MySQL instance.

尽管是完全专有的,但 SQL Server 中提供的良好连接功能之一是能够通过链接服务器查询其他服务器.本质上,链接服务器是一种直接查询另一个 RDBMS 的方法;这通常通过使用安装在服务器上的 ODBC 驱动程序来实现.

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.

参考这篇文章:一步一步的过程 SQL Server 链接服务器到 MySQL.

如果您代表您连接的 MySQL 用户授予适当的权限,您就可以写入对应您的 MySQL 实例.因此,您可以更新存储过程以执行额外的步骤将记录插入 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.

更简单的解决方案是使用商业应用程序 - 来自 Spectral Core 的 Omega SyncOmega Sync 可以比较和同步数据库模式和表数据.您甚至可以同步异构数据库的数据(例如,将您的本地 SQL Server 数据库与您网站上的 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).

相关文章