CODEWITHSUNDEEP
sql-serverdatabase-performance
Trevor
Trevor

Reputation: 4860

Better Way of Doing SQL Server Remote Query?

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.

Upvotes: 0

Views: 1046

Answers (2)

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

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.

Upvotes: 2

Sam Sussman
Sam Sussman

Reputation: 1045

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.

Upvotes: 0

Related Questions