将SQL Server 2005与MySQL同步

I'm need to copy several tables wholesale from a third-party SQL Server 2000 database to a MySQL 5 database and keep them synchronized--i.e., when some CRUD happens on the SQL Server tables, I'd like it to be reflected in the MySQL versions in a timely manner. Now, I know there are ways to do this with 2000, but some time in the near future, the SQL Server database will be upgraded to 2005, which seems to not offer the same loopholes that 2000 does.

我需要将几个表从第三方SQL Server 2000数据库批量复制到MySQL 5数据库并保持同步 - 即,当SQL Server表上发生某些CRUD时,我希望它反映在MySQL版本及时。现在,我知道有办法用2000做到这一点,但在不久的将来,SQL Server数据库将升级到2005年,这似乎不会提供2000年的漏洞。

So, is there any way to replicate/synchronize data from SQL Server 2005 to MySQL that doesn't involve me comparing tables in the two databases programmatically? If so, will it also work with SQL Server 2000?

那么,有没有办法将数据从SQL Server 2005复制/同步到MySQL,而不涉及我以编程方式比较两个数据库中的表?如果是这样,它是否也适用于SQL Server 2000?

3 个解决方案

#1

Suggestions for replication of data from MS Sql 2005 and MySql

有关从MS Sql 2005和MySql复制数据的建议

One person suggests not messing with such a scheme and setting up another sql server.

一个人建议不要弄乱这样的方案并设置另一个SQL服务器。

SQL express has some constraints on performance, but maybe that's an option for you. I can't find anything that can work, out of the box.

SQL Express对性能有一些限制,但也许这是你的选择。我找不到任何可行的,开箱即用的东西。

#2

I've previously done this, although it will completly depend on your definition of timely!

我以前做过这个,虽然它完全取决于你及时的定义!

I used myODBC (http://dev.mysql.com/downloads/connector/odbc/5.1.html) to setup the mySQL server as a linked server. You can then use the openquery command (http://msdn.microsoft.com/en-us/library/ms188427.aspx) to get the data from the remote table.

我使用myODBC(http://dev.mysql.com/downloads/connector/odbc/5.1.html)将mySQL服务器设置为链接服务器。然后,您可以使用openquery命令(http://msdn.microsoft.com/en-us/library/ms188427.aspx)从远程表中获取数据。

All you then need to do is write a stored procedure which does an insert/update where not exists the data in the remote database. The SQL is a bit nasty but it is doable! Once you have an SP that works you can set it to run as a trigger somewhere or run it as a job in MSSQL server.

您需要做的就是编写一个存储过程,该过程执行插入/更新,其中不存在远程数据库中的数据。 SQL有点讨厌,但它是可行的!一旦有了可行的SP,就可以将其设置为在某处作为触发器运行,或者将其作为MSSQL服务器中的作业运行。

#3

I'm also going to suggest connecting SQL Server to MySQL via Linked Server. Then, create AFTER triggers on the related tables that makes use of INSERTED an DELETED psuedo-tables to ensure that all local operations are reflected on the remote databases. For example:

我还建议通过链接服务器将SQL Server连接到MySQL。然后,在相关表上创建AFTER触发器,这些触发器使用INSERTED和DELETEED伪表来确保所有本地操作都反映在远程数据库上。例如:

CREATE TRIGGER tr_Orders_Insert ON Orders AFTER INSERT AS
    BEGIN
        INSERT INTO MySQLServer...Orders (OrderID, OrderDate)
        SELECT OrderID, OrderDate FROM INSERTED
    END
GO

#1