Should we use TCP or Named Pipes or use the default in SQL server?
When connecting to a SQL Server 2008 R2 from a .NET 4 client application on a different server in the same LAN, one can set three different network protocols:
- TCP Named Pipes
- Don't set anything in the connection string and use the default
What is best practice? What to choose?Additional information: Both TCP and Named Pipes are enabled both on the server and on the client. The application is using database mirroring. Client and server communicate over a fast LAN.
We are investigating this because we have rare and spurious connectivity and timeout problems. (But regardless of that I'd like to know the best practice).
There is an article on this subject on MSDN but it is very generic and vague. It does not advise or recommend anything useful.
How to use SQL Server named pipes?
- Named Pipes protocol are useful for the application that are designed around NetBIOS or other LAN-based protocols.
- Named Pipes provides easy access to Remote Procedure Calls (RPC) within a single security domain and thus is advantageous to these applications.
- Usually TCP Protocol is good in practice because you don't have to care about all these on the network.
- The process to SQL Server named pipes:
- Open the SQL Server Configuration Manager on the SQL Server.
- Expand SQL Server Network Configuration > Protocols for [Instance name]
- Makes sure that Shared Memory, Named Pipes, and TCP/IP are enabled. If any of the above was disabled, enable them.
Restart SQL Server Services to apply the changes.