2

I have a SQL Server instance, Instance1, that has a linked SQL Server, Instance2. The linked server technically works, however I am positive that Instance1 is connecting to Instance2 via Named Pipes. I can see this very clearly by testing a sample query using TCP/IP and Named Pipes. Query run times are a difference between <1 second for TCP/IP and >45 seconds for Named Pipes.

Can I force the linked SQL Server on Instance1 to use TCP/IP when connecting to Instance2? I have not been able to find any information relating to this, do I have any other options if this can't be done?

I tried creating the linked server by specifying the port but it seems to use Named Pipes anyway, i.e., I created it as Instance2,1433. Is there maybe a way to tell the master database to use TCP/IP for linked servers?

Any help is greatly appreciated. I'm hoping the answer is not "Turn off Named Pipes in Instance2 SQL configuration" although I realize I might have to do that if I can't get this to work.

Edit: I have without a doubt verified that it is Named Pipes that is causing the problem, and I've seen this same problem with Named Pipes before (it's well documented behaviour of Named Pipes). I just haven't seen how to solve it when using Linked Servers.

test
  • 131
  • 2
  • 6
  • What evidence are you using to support your claim that it's using named pipes? I'd use net_transport from sys.dm_exec_connections myself. Merely differing query times is not sufficient evidence to me. – Ben Thul Feb 04 '17 at 20:00
  • @Ben Thul Because I've seen it through `sys.dm_exec_connections`, `sys.sysprocesses`, and code that forces TCP/IP & Named Pipe libraries - `DBMSSOCN` vs `DBNMPNTW`. – test Feb 04 '17 at 22:32
  • Just playing Devil's Advocate, but if NP is undesirable, why have it configured on the instance at all? That is, can you just disable it so you can ensure that NP won't be used? – Ben Thul Feb 04 '17 at 23:02
  • @Ben Thul I mentioned disabling Named Pipes on the server in my post (as a last option). I was hoping there is a different way to configure the linked server so that I don't have to do that. – test Feb 05 '17 at 01:16
  • Ah… I missed that. My mistake. Out of curiosity, where is Named Pipes in the native client configuration preferences? I'm looking at mine and I'm sure I didn't change it and named pipes is listed last. Maybe your system is configured differently? To be clear, I'd expect the configuration on Instance1 to be relevant here. – Ben Thul Feb 05 '17 at 02:17

1 Answers1

2

I tried, with success, the Connection String method to force a protocol: Data Source=protocol:servername, portnumber;

Example:

EXEC master.dbo.sp_addlinkedserver @server = N'DB_OBRA', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'tcp:10.0.34.33'
Homo Programmatoris
  • 21
  • 3