设计策略：跨2个不同数据库查询和更新数据
我们有一个需求，需要跨 2 个不同的数据库查询数据（一个在 SQL Server 中，另一个在 Oracle 中）。
以下是需要实现的场景：
- 查询：从一个数据库获取数据并匹配其他数据库中的值
- 更新：从一个数据库获取数据并更新其他数据库中的对象
：ASP.net、C#
我们正在使用的技术 我们考虑过的选项：
- 一个数据库中的临时区域
- 链接服务器（无法采用该方法，因为组织范围内的政策不允许这样做）
- 创建 Web 服务
- 创建 2 个不同的 DAL 并使用来自 2 个源的数据执行列表操作在DAL中
我想知道处理这种情况的最佳设计策略是什么？如果是，那么这种方法的优点和缺点是什么
We have a requirement in which we need to query data across 2 different databases ( 1 in SQL Server and other in Oracle).
Here are the scenarios which need to be implemented:
- Query: Get the data from one database and match for values in other
- Update: Get the data from one database and update the objects in other
Technology that we are using: ASP.net, C#
The options that we have thought about:
- Staging area in one database
- Link Server ( can't go with the approach as it is not allowed due to organization wide policy)
- Create web services
- Create 2 different DAL and perform list operations with the data from 2 sources in DAL
I would like to know what is the best design strategy to deal with this kind of a scenario? If yes, then what are the pros and cons of that approach
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
是否无法使用 SSIS 包在两台服务器之间进行数据转换并通过 ASP.Net 和 ASP.Net 调用它？ c# 项目或通过按需调用的计划作业？
Is it not possible to use SSIS package to do the data transformation between 2 servers and invoke it either via ASP.Net & c# project or via schedule job invoked on demand?
其中一个数据库的结果是否足够小以有效地传递？
如果是这样，我建议将数据库视为两个独立的数据源。
如果数据集很大，那么您可能必须考虑将某种形式的 ETL 放入其中一个数据库的暂存区域中。如果您需要查询从两个数据库返回最新数据，则可能会遇到问题。因为您需要进行实时 ETL。
Will the results from one of the databases be small enough to efficiently pass around?
If so, I would suggest treating the databases as two independent datasources.
If the datasets are large, then you may have to consider some form of ETL into a staging area on one of the database. You may have issues if you need the queries to return up-to-date data from both databases. Because you will need to do a real-time ETL.
这里有一篇关于在 Microsoft SQL Server 和 Oracle 之间执行分布式事务的文章：
我不知道它的效果如何，但是如果它确实工作，这可能是最适合您的解决方案：
There is an article here about performing distributed transactions between Microsoft SQL server and Oracle:
I don't know how well this works, however if it does work, this will probably be the best solution for you:
最好的策略是使用链接服务器，因为它是为查询和写入异构数据库而设计的，如上所述。但显然由于你提到的政策限制，这不是一个选择。
因此，为了以最佳性能获得您想要的结果，我的建议如下：
是否将其编写为 Web 服务的决定不会改变数据检索过程。但应考虑通过使进程尽可能靠近数据库服务器（在同一台计算机上或在 LAN/高速连接链路内）来从本质上减少数据传输时间的开销。
数据更新将非常简单。这只是标准的两阶段操作，从一个阶段提取数据并更新另一个阶段。 -
The best strategy for this will be to use Linked Server, as it is designed for querying and writing to heterogeneous databases as you described above. But obviously due to the policy constraint you mentioned, this is not the option.
Therefore, to achieve the result you want in the most optimal performance, here is what I suggest:
The decision to whether to write this as web service or not isn't going to change the data retrieval process. But consideration should be given in essentially reducing the overhead on data transfer time by keeping the process as close to your db server as possible either on same machine or within LAN/high speed connection link.
Data update will be quite straightforward. It will just be the standard two phase operations of pull data out from one and update the other. -
很难说最好的解决方案是什么。但我们有一个几乎相同的场景。
实时：
对于实时数据更新，我们使用 WebServices，因为在我们的例子中，两个不同的数据库属于不同的项目。因此每个项目都提供一个WebService，可用于数据检索和数据更新。这样做的好处是，只要 Web 服务接口不改变，项目就不必关心数据库结构的改变。
静态数据：
静态数据（例如员工）将被镜像，以便更快地访问。对于如此大量的数据，我们使用平面文件进行夜间更新。
对于静态数据，我认为显式定义数据所有者很重要。对于每一条数据，应该清楚哪个数据库有原始数据，哪个数据库只有卷影副本以便更快地访问。
因此，静态数据在影子数据库中是只读的，或者只能通过指定的 Web 服务进行更新。
It's hard to tell what the best solution is. But we have a scenario that's nearly the same.
RealTime:
For realtime data updating, we are using WebServices, since in our case, the two different databases belongs to distinct projects. So every project offers a WebService which can be used for data retrieval and data update. That has the advantage, that the project must not take care for database structure changes as long the webservice interface does not change.
Static Data:
Static data (e.g. employees) will be mirrored because for faster access. For that huge amount of data we are using flat files for the nightly update.
In case of static data I think it's important to explicit define data owners. For every piece of data it should be clear which database has the original data, and which database only has shadow copies for faster access.
So Static data is readonly in the shadow database, or only updateable through designated WebServices.
在 .NET 代码中使用多个数据源的问题是，您可能会面临 CRUD 操作失败 ACID 测试以及数据不一致的风险。
我最倾向于追随@Will A 对您的问题的评论...
设置到删除服务器的复制，然后链接两个远程服务器。
The problem with using multiple data sources in your .NET code is that you run the risk of having your CRUD ops fail ACID tests and having data inconsistencies.
I would be most inclined to pursue @Will A's comment to your question...
Set up a replication to a remove server, then link the two remote servers.
拥有多个 DAL 并在应用程序中对其进行处理 - 数千并不是一个大数字，只有在达到 100,000 或数百万时才需要担心，在这种情况下您的应用程序将挂起。
使用 linq 对生成的数据集执行数据操作，而不是循环遍历它们。
Have multiple DALs and handle it in the application - thousands is not a big number, you need to worry only if you are into 100,000s or millions in which case your application will hang.
Use linq to perform data operations on the datasets that are generated rather than looping through them.