进行 SQL Server 远程查询的更好方法？
我有两个 SQL Server 实例，并且从一台服务器上对另一台服务器上的数据库进行大量远程查询。就像 server1.database1 上的这个查询一样：
select T1.id
from server1.database1.dbo.table1 T1
inner join server2.datbase2.dbo.table2 T2
on T1.id = T2.id
我从其他人那里继承了这段代码，并且想知道是否有更好（更快）的方法来做到这一点？我的意思是，有没有一种方法可以在 server1.database1.dbo 上创建 server2.databse2.dbo.table2 的精确副本，该副本可以自我更新并实时保持最新状态？
Microsoft SQL Server 标准版（64 位） 版本 10.0.4000.0
编辑：实际上，我现在在这种情况下所做的是，如果可以的话，我使用开放查询和 with(nolock) 来获取我需要的最小数据集，然后将其放入临时表中。我将“id”列设置为唯一的聚集索引，以便它可以快速加入我在服务器 1 上加入的任何内容。
I have two SQL Server instances and I do a lot of remote querying of a database on one server from another server. Like this query on server1.database1:
select T1.id
from server1.database1.dbo.table1 T1
inner join server2.datbase2.dbo.table2 T2
on T1.id = T2.id
I've inherited this code from someone else and was wondering if there's a better (faster) way of doing this? I mean, is there a way I could create an exact replica copy of server2.databse2.dbo.table2 on server1.database1.dbo that updates itself and keeps itself current in real-time?
Microsoft SQL Server Standard Edition (64-bit)
Version 10.0.4000.0
EDIT: Actually, what I do now in this scenario is, if I can, I use open query and with(nolock) to grab the smallest dataset I need, and I put that in a temp table. And I set up the "id" column to be a unique clustered index, so that it can join quickly on whatever I'm joining on server 1.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 SQL Server 中，您可以根据自己的情况选择三个主要选项（取决于您的版本）：
日志传送：易于设置和维护；但是，“副本”数据库不是实时的，并且只能与原始服务器上的最后一个事务日志备份一样最新。
镜像：非常接近实时，但无法直接读取“副本”数据库；相反，需要定期创建快照。
复制：难以管理和维护，但可能会为您提供“副本”数据库上数据的最实时版本。
除非您的链接服务器存在性能或稳定性问题，否则它将坚持使用该方法，除非您愿意花费大量时间和精力来实现这三种方法之一。
In SQL Server, you have three main options for your scenario (Depending on your version and edition):
Log Shipping: Easy to setup and maintain; however, the "replica" database wouldn't be real-time, and would only be as up-to-date as your last transaction log backup from the original server.
Mirroring: Very close to real-time, but the "replica" database can't be read from directly; instead, a snapshots would need to be periodically created.
Replication: Difficult to manage and maintain, but would likely give you the most real-time version of the data on your "replica" database.
Unless you're having performance or stability issues with the linked server, it'd stick with that approach unless you're willing to spend a lot of time and effort implementing one of these three approaches.
您可以创建表的视图，它将是不可编辑的，但可以获取确切的表，而无需始终引用其他数据库。然而，您仍然需要另一张桌子。
You could create a view of the table, it would be uneditable, but would grab the exact table without needing to always refer back to the other database. You will still need the other table however.