1

The scenario is that developers used a linked server object on ServerM to reach ServerST. This linked server was setup as a "SQL Server" type using a domain login that exists as a login on ServerST.

Now the developers require this linked server object (named ServerST) to be reconfigured to point at ServerAR (even though confusingly the object would still be named ServerST). This allows them to save time rewriting a lot of code, time they do not have.

Now I can create a new linked server on ServerM of the type "Other data source" and provide a connection string that works. However a query on the original linked server that took 5s took 46s using the new linked server.

So my question is how do I provide a linked server named ServerST, that points to ServerAR and operates at the same speed.

All servers are on SQL Server 2008 R2 SP2.

The code I currently use to create the slow linked server is:

sp_addlinkedserver 'ServerST', ' ', 'MSDASQL', '', '',  
    'Driver={SQL Server};Server=ServerST;Database=theDB;Uid=link_ServerM;Pwd={P@ssw0rd};'
Improve this question

1 Answer 1

Reset to default
1

Why not just use the SQLNCLI10 provider? Are you required to use MSDASQL?

Here is a sp_addlinkedserver script adding a linked server named ServerST that points to ServerAR.

EXEC master.dbo.sp_addlinkedserver @server = N'ServerST', @srvproduct=N'ServerST', 
    @provider=N'SQLNCLI10', @datasrc=N'ServerAR'

Update 19/082014

Download SQL Query Stress Tool to compare performance of both SQL Servers, ServerST and ServerAR.

enter image description here

Scripting out your existing Linked Server

This will also include any specially defined parameters such as collation and time out configurations.

enter image description here

Improve this answer
7
  • 1
    Have you tested the difference in performance between ServerAR and ServerST? Perhaps the slowness is coming from there. Try this application on both servers, using the same parameters: datamanipulation.net/sqlquerystress
    – Craig Efrein
    Commented Aug 19, 2014 at 8:27
  • For testing purposes all 3 linked servers currently go to ServerST but using different methodolgy. SQL Server type, SQLNCLI10 and MSDASQL. The latter two operate equally slower than the first. I don't think a stress test tool will be of aid.
    – Paul
    Commented Aug 19, 2014 at 8:32
  • 1
    How is ServerAR different from ServerST? According to your question, ServerAR appears to be replacing ServerST. Or is that not what's happening?
    – Craig Efrein
    Commented Aug 19, 2014 at 8:36
  • I don't feel this is relevant but ServerST is a testing box (non production) whilst ServerAR is not currently defined (it may be the production version of ServerST), they just need the facility to look elsewhere via reconfiguring the linked server object. My dilemma is providing a configurable linked server seems to be slower, even when comparing against the same two servers (ServerM->ServerST).
    – Paul
    Commented Aug 19, 2014 at 8:39
  • 1
    No worries, I was glad to help
    – Craig Efrein
    Commented Aug 19, 2014 at 8:47

Your Answer

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

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