3

I am DBA of server A (SQL Server 2008R2), linked to server B (SQL Server 2012SP1), linked to server C (not sure which version). I have no control on server C, and I cannot contact its DBA. I am not even sure that server C is Microsoft SQL Server, it might be Oracle.

On server B, I can contact the DBA and ask for a view on server C, example:

Create view [dbo].[View_Stuff] as 
select * from 
openquery (SERVER_C, 'SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x')

I would like to do things like:

SELECT * FROM View_Stuff WHERE mykey=27

This view gets a lot of data from server C, because the openquery gets all the content of the table, which is not acceptable. On the other hand, I know that this would extract only one row, and would be very efficient on server C:

SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27

I considered using indexed views, but they cannot work with linked servers. Mirroring the data is possible, but I really want real time information.

What can I ask to define on server B to be able to get data efficiently and in real time from a query on server A?

Improve this question
6
  • Linked servers are never ideal for performance. Is there the possibility of using log shipping or always on to get a fairly recent local copy of the data?
    – James Anderson
    Oct 28, 2014 at 11:39
  • You are right about performance, those are not options. Believe me, it's annoying :-)
    – carlo.borreo
    Oct 28, 2014 at 11:41
  • You mention mirroring is possible. You could go down the route of mirroring the databases to your server and then using snapshots to read the data but it can be a bit of work to automate that. I expect the linked servers are your only option. What is the query for?
    – James Anderson
    Oct 28, 2014 at 11:47
  • I wrote a simple PHP script that reads the list of views on server B, and for each of them it creates an equivalent table on my server A, then copies the data. This is what I call mirroring. Problem is, the data transfer takes hours, and I want to access information in real time.
    – carlo.borreo
    Oct 28, 2014 at 11:52
  • Once you have the initial load could you write a process that only copies over the changes from then on? Is server B physically far away?
    – James Anderson
    Oct 28, 2014 at 12:19

1 Answer 1

Reset to default
2

Since you know exactly the T-SQL you'd like to run on server 'B', why not just execute that query remotely, at server 'B', like this:

DECLARE @cmd nvarchar(max);
SET @cmd = N'SELECT t.c1
    , t.c2
    , t.c3
FROM OPENQUERY (SERVER_C, N''SELECT c1,c2,c3 FROM t1,t2 WHERE t1.x=t2.x AND mykey=27;'') t;
';
EXEC [SERVER_B].tempdb.sys.sp_executesql @cmd;

That will cause server 'B' to query server 'C' with all the parameters you want, and should be about as quick as you can get.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.