如何从不同的SQL Server查询多个数据库 [英] How to query multiple databases from different SQL Servers

问题描述

我们有约。 8个奇怪的SQL Server用于不同的目的，例如在1个服务器中插入数据，在另一个服务器中更新等（或根据用户的区域仅连接到那些数据库）。

问题有时需要从多个SQL Server数据库查询数据。所以说，我有一个  Id   属性，
并基于  Id，
数据需要从这8个服务器中检索（如果有一个Id匹配，所以基本上查询所有数据库）。

所以基本上用户登录的服务器将使用"链接服务器"功能和连接到其他SQL Server（用户当前使用的服务器充当
源 SQL Server），并使用" UNION "支持所有数据的功能。

由于每天都在进行大量交易，因此这种方法不可行，性能明智。

< p style ="">所以有关更好的方法来实现相同的上述功能的任何建议。我读了一个名为" Server
Groups "但不确定。

应用程序是使用Jquery / Ajax / HTML / API和ADO.NET在.Net Web窗体中创建的。
因此，在.net应用程序中，我可以建立单独的连接并将ID从.net应用程序传递到这些服务器，但
链接服务器方法是用于创建自定义报告仪表板。因此，对于自定义报告，我们需要从多个SQL Server获取数据并创建报告。

解决方案

高级解决方案：

解决方案1：

1. 创建您从数据库中不同服务器访问（链接服务器）表的视图。 （数据访问视图）
2. 创建登台表并根据数据访问视图加载数据。
3. 根据您的要求安排数据加载。
4. 根据登台表更新永久表。

解决方案2：事务复制

1. 使用事务复制将您需要将表从不同服务器复制到服务器的表。
2. 将数据与复制表与表格进行比较并加载数据。

We have approx. 8 odd SQL Servers used for different purposes like inserting data in 1 server, update in another etc. (or connecting to only that databases based on user’s region).

The problem is sometimes query for data needs to be done from multiple SQL Server databases. So say, I have an Id property, and based on the Id, data needs to be retrieved from these multiple 8 servers (if there is an Id match, so basically querying all database).

So basically the server which the user is logged into, will use "Linked Server" functionality and connect to other SQL Servers (with the server which the user is currently on acts as the source SQL Server), and using "UNION" functionality to club all data.

As a lot of transactions is taking place each day, this approach is not feasible, performance wise.

So any recommendations on a better approach to achieve the same above functionality. I read a concept called "Server Groups" but not sure of it.

The application is made in .Net Web Forms using Jquery/Ajax/HTML/API and ADO.NET. So in .net application I can establish individual connections and pass the ID from .net app to these servers, but the Linked Server methodology is used to create Custom Report Dashboard. So for custom reports we need to fetch data from multiple sql server and create reports.

解决方案

High level solution:

Solution 1:

1. Create views which you are access (linked server) the tables from different servers in your database. (Data Access Views)
2. Create staging tables and load the data based on data access views.
3. Schedule the data load based on your requirement.
4. Update the permanent tables based on staging table.

Solution 2: Transactional Replication

1. Replicate the tables which you require the tables from different servers to your server by using transactional replication.
2. Compare the data with replicated tables vs your tables and load the data.

这篇关于如何从不同的SQL Server查询多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！