Share via

how to choose between Shared Memory, Named Pipes, TCP/IP, VIA?

  • Article

Question

Tuesday, September 7, 2010 5:56 AM | 1 vote

What protocol to choose?
Or, Why are they so many?

I am mostly interested in contexts:
1) 
development on local machine
2)
development from remote machine (having SQL Server on another machine) 
3)
Production
4)
what else should I bother/know about?

I am developer, mostly using Developer Ed. mostly locally on Windows XP Pro SP3,
and really had never bothered which protocol was used as far as connection was being successful.
And I had never had any troubles to bother looking and choosing between protocols
Why and when should I bother to choose between protocols for development?

All replies (6)

Tuesday, September 7, 2010 1:27 PM ✅Answered | 3 votes

>>This all sound like thre is no need in using anything except TCP/IP which is anyway installed and >>configured with any Windows?
>>independently on the same machine, on the same network remotely or over internet!

Yes, TCP/IP is preferred and widely used among others.

Tuesday, September 7, 2010 7:05 AM | 1 vote

Try to read http://msdn.microsoft.com/en-us/library/ms187892.aspx

Tuesday, September 7, 2010 8:25 AM | 1 vote

Why and when should I bother to choose between protocols for development?

Most of the organizations use TCP/IP protocol to connect to SQL Server, By default SQL Server will listen on default port 1433. You can customize the port other than 1433 for better security in this case you should inform to developers to connect to the customized port. 

It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer running an instance of Microsoft® SQL Server™ , the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is extremely fast.

In general, TCP/IP preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.

http://msdn.microsoft.com/en-us/library/aa178138(SQL.80).aspx

Shared Memory

Connections to Microsoft SQL Server from a client running on the same computer use the shared memory protocol. Shared memory has no configurable properties. Shared memory is always tried first, and cannot be moved from the top position of the Enabled Protocols list in the Client Protocols Properties list. The Shared Memory protocol can be disabled, which is useful when troubleshooting one of the other protocols.

http://msdn.microsoft.com/en-us/library/ms187662.aspx

 

 

Rajesh Kasturi Please click the Mark as Answer button if my post solves your problem.

Tuesday, September 7, 2010 1:24 PM | 1 vote

Try to read http://msdn.microsoft.com/en-us/library/ms187892.aspx

Thanks, this article leads to more confusion and further questions than clarifications.

In section "Named Pipes vs. TCP/I{ Sockets" it tells:

  • " For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on. This can be very helpful in a slow network"

Why would I choose Named Pipes if I do not have any problems with TCP/IP?
It is written that Named Pipes performance is either comparable with TCP/IP in fast network or worse at slow  transmissions.

  • "For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients"

Hooh, which conclusions should I jump at after reading this? It is not me who manually invoke more or less interactivity in connection. Whu should I care?

VIA is deprecated.

Shared memory

  • "Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly"

Well, I do not have any need in any troubleshooting.

This all sound like thre is no need in using anything except TCP/IP which is anyway installed and configured with any Windows?
independently on the same machine, on the same network remotely or over internet!

 

Thursday, September 9, 2010 7:22 AM | 1 vote

>>This all sound like thre is no need in using anything except TCP/IP which is anyway installed and >>configured with any Windows?
>>independently on the same machine, on the same network remotely or over internet!

Yes, TCP/IP is preferred and widely used among others.

Well, after some reading I understood that msdn docs are self-contradictive and misleading.
And ""TCP/IP is preferred and widely used among others"  is not correct answer.
According to [1], it is impossible to use TCP/IP if AD (Kerberos) is not available.

This makes from TCP/IP and outlaw in most development cases:
1)
development against local SQL Server (on the same machine)
2)
remote development in workgroup

In order to check this, I left only TCP/IP the only enabled protocol both on SQL server and on client and I cannot connect to my local SQL Server (workgroup Windows XP Pro SP3)! neither through localhost, nor to 127.0.0.1 either being connected or disconnected to network receiving:

-
TITLE: Connect to Server

Cannot connect to localhost [or 127.0.0.1].

ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to
SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

BUTTONS:  OK

though, according to [1] the TCP/IP connection should succeed if to connect through 127.0.0.1 or if to disable NIC connection

[1]
MSDN Blogs > SQL Protocols >
**“Cannot generate SSPI context” error message, when connect to local SQL Server outside domain
**http://blogs.msdn.com/b/sql_protocols/archive/2005/10/19/482782.aspx?PageIndex=1#comments

Wednesday, October 9, 2019 8:41 AM

Check in the SQL Server Configuration Manager MMC, that you have the TCP port 1433 set under the TCP/IP properties,  IP Addresses > IP All section. I find that sometimes this is empty and then it does not bind the port.