0

okay so I googled quite a bit and didn't find an answer, so here I am.

I have two servers, Lets say Server1 and Server2. Server1 has 1 database S1.DB1. Server 2 has 2 databases S2.DB1 and S2.DB2.

Now, I have to do reporting on Server 1 where I need to use data from S1.DB1 and S2.DB1. Should I be using OpenQuery to get the data from server 2 or should I just create a copy of S2.DB1 in Server 1 so I can easily access those tables in Server 1?

My basic concerns are speed of data extraction in case of OpenQuery and Storage issues in case of duplicating DBs. Which one is the right/better way to go about it?

Dhruv Malik
  • 3
  • 2

1 Answers1

0

Use OpenQuery if it's fast enough. If you have your DBA's cooperation, he could create a linked server, which is more convenient.

Replicating the data requires more administrative overhead and introduces failure modes. It can be helpful if the data changes slowly and are queried relatively frequently. (Use replication, though; don't just "copy the database".)

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31