SQL Server Connectivity – Why Changing Network Protocol Order Doesn’t Make Sense

connectivityNetworksql servertcpip

All I want to do is to connect to SQL Server though Named Pipes without using an explicit way like NP:ServerName because mentioned approach works. I want to connect through Named Pipes using just ServerName and give a SQL Server a chance to choose the right network protocol. So I have 3 VMs:

What I did step by step:

In Server Configurations of SQL Server A I enabled all network protocols.

I am also familiar with the Order column, however, changing it appears to have no affect. I swapped the order of TCP/IP and Named Pipes:
I tried to connect from SQL Server B into SQL Server A using Computer Name(WIN-VKHOKLJ3IJG) and I hoped that it will use Named Pipes protocol but unfortunately it chose TCP/IP as a connection protocol:

Could someone explain why TCP/IP was chosen instead of Named Pipes? According the order, SQL Server should choose Named Pipes but it rather preferred to use TCP/IP.

Best Answer

I believe the issue has to do with what client is used when establishing connectivity to Server B from Server A rather than how you order your protocol order on the server itself.

The first hint comes from Choosing a Network Protocol: Enabling the Protocol (highlighting mine):

The protocol must be enabled on both the client and server to work. The server can listen for requests on all enabled protocols at the same time. Client computers can pick one, or try the protocols in the order listed in SQL Server Configuration Manager.

I suspect most clients determine the desired order of the protocols being used rather than delegate that to the server.

Further digging through MS documentation yields this interesting note in the Client Protocols Properties (Order Tab) article:

Note

These settings (e.g. Order) are not used by Microsoft .NET SqlClient. The protocol order for .NET SqlClient is first TCP, and then named pipes, which cannot be changed.

As we can see for the .NET SqlClient, if TCP/IP is an enabled protocol on the server and the .NET SqlClient is being used, it doesn't matter what you do, you're getting a TCP/IP connection. So with this client in particular the protocol specified on the server most definitely doesn't matter.

With these examples, I think it's pretty clear that the client you're using to connect to a server has more bearing on the protocol being used rather than the connection protocol ordering on the server itself.

In your specific case, if you really want to use Named Pipes instead of TCP/IP, you may be able to force it via a Client Alias:

You would need to create this alias on Server A for Server B. Just note that aliases come with their own headaches (e.g. you've now got another thing to manage whenever you deal with connectivity changes on Server B), but as a workaround, it may do the trick.

Also of note, Named Pipes can become problematic on slow connections where the same issues are not as impacting via TCP/IP connections, which is likely why most clients gravitate toward the TCP/IP protocol by default.

Related Solutions

Sql-server – SQL Server: Should we use TCP or Named Pipes or use the default

I prefer TCP/IP over Named Pipes, even though in most situations there will be no noticeable difference. You can do this by adjusting the protocols supported by the instance in SQL Server Configuration Manager rather than hard-coding things in your connection string (this makes it easier to make changes or to troubleshoot).

Essentially the routing and other overhead involved with named pipes (unless your apps are on the same machine as SQL Server, in which case there is only a little extra overhead) make it the less efficient option, especially at scale, in a slower network environment (100MB or less), or if your workloads come in bursts.

If your apps are on the same box as SQL Server, you should also keep shared memory in mind - if you have applications on the SQL Server box directly communicating with SQL Server, this is going to be the most efficient option.

You can read about the performance advantages of TCP/IP in more detail.

Sql-server – How does disabling a network protocol affect SQL server performance

Yes, IMHO you should disable named pipes and use only TCP/IP for better performance. You can force this in the connection strings for your apps and change them one at a time, rather than just shutting off named pipes and then getting a bunch of complaints:

Data Source = tcp:IP-or-host-name[\instance-name]

Don't shut off named pipes until all your apps are using TCP/IP. You can check what your apps are using via sys.dm_exec_connections.net_transport - this will have values such as TCP or Shared Memory. You can check the program too via a query like this:

SELECT c.net_transport, s.program_name, c.client_net_address
  FROM sys.dm_exec_sessions AS s
  INNER JOIN sys.dm_exec_connections AS c
  ON s.session_id = c.session_id;

However, for your second question, using one login or multiple logins won't affect performance much unless you're at the upper end and exceeding your pooled connections limit. In most cases consolidating into a single login will just make it much harder to later audit etc.

Best Answer

Related Solutions

Sql-server – SQL Server: Should we use TCP or Named Pipes or use the default

Sql-server – How does disabling a network protocol affect SQL server performance

Related Question