在MS SQL Server和Oracle Server之间同步数据 [英] Syncing Data between MS SQL Server and Oracle Server

问题描述

我的同事和我正在尝试找到将SQL数据库与SQL服务器同步的最佳查询。有大约80k +行，每行约19列数据。我们在两个服务器之间有一个链接服务器设置，我们有一个查询，但对于80k记录，查询花了10小时来复制记录。我可以发布我们使用的查询，但我想有一个新的眼睛。这是一个新的过程，所以我们不是试图改进一个解决方案现有的代码。我之前说过，权限不是一个问题，它只是一个问题，从A点到B点在最快的时间。这是要在冷灌注支持的网站上使用，客户端希望单击一个按钮来同步数据，但是，这只是我们正在使用的要求的愿望列表。

My colleague and I are trying to find the best query to sync up an Oracle database with that of a SQL server. There are about 80k+ rows with ~19 columns of data in each row. We have a linked server setup between the two servers and we have a query that works but for 80k records, the query took 10 hours to copy the records over. I can post the query we used but I would like to have a fresh set of eyes. This is a new process so we aren't trying to retrofit a solution to existing code. LIke I said before, permissions aren't an issue, it is just a matter of getting the data from Point A to Point B in the quickest time. This is to be used on a coldfusion supported web site and the client would like to click a buttton to sync up the data but again, this is just "wish list" of requirements we are working with.

其他的想法我想补充一下：
我们试过openquery和使用链接服务器，但两者花了大约相同的时间来完成。
大多数是varchar（64字节），几个varchar（128）和几个varchar（12个字节）。
其他人提出的一个建议是将数据写入平面文件，将平面文件ftp到Point B，然后导入。这是一个可行的解决方案，但我们包括的步骤越多，有更多的机会打破。

Additional Thoughs I'd like to add: We have tried openquery and using linked server but both took about the same time to complete. Most are varchar(64 bytes), a couple of varchar(128) and a couple of varchar(12 bytes). One suggestion someone else made was to write the data to a flat file, ftp the flat file to Point B and then import it. That is a viable solution but the more steps we include, the more chances there are of something breaking.

提前感谢。我期待看到y'alls解决方案。

Thanks in advance. I look forward to seeing y'alls solutions.

推荐答案

我使用SSIS包比链接服务器更成功。如果你使用Oracle DLL，它不是太糟糕。

I've had more success with an SSIS package than linked servers. If you use the Oracle DLL's, it's not too bad.

这篇关于在MS SQL Server和Oracle Server之间同步数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！