Comparison and Selection of network protocols in SQL Server

Network Protocol in SQL Server

To connect to the SQL Server database engine, you must enable the network protocol. Microsoft SQL Server can process requests through multiple protocols at the same time. The client connects to SQL Server using a single protocol. If the clientProgramIf you do not know which Protocol SQL Server is listening for, you can configure the client to try multiple protocols in sequence. Use the SQL Server Configuration Manager to enable, disable, and configure network protocols.

Shared Memory
Shared memory is the simplest protocol available, with no configurable settings. Because clients using the shared memory Protocol can only connect to SQL Server instances running on the same computer, it is useless for most database activities. If you suspect that other protocols are incorrectly configured, use the shared memory protocol for troubleshooting. Note:
Clients Using MDAC 2.8 or earlier versions cannot use the shared memory protocol. If these clients try to use it, they will automatically switch to the named pipes protocol.

TCP/IP
TCP/IP is a common protocol widely used on the Internet. It communicates with computers with different hardware structures and operating systems in the Interconnect Network. TCP/IP includes the network traffic standard and provides advanced security functions. It is currently the most commonly used protocol in business. It may be complicated to configure a computer to use TCP/IP, but most networked computers are correctly configured. To configure TCP/IP settings that do not appear in the SQL Server Configuration Manager, see the Microsoft Windows documentation.

Named Pipes
Named Pipes is a protocol developed for LAN. A part of the memory is used by a process to transmit information to another process. Therefore, the output of a process is the input of another process. The second process can be local (on the same computer as the first process) or remote (on a networked computer ).

 

Via
The virtual interface adapter (VIA) protocol is used together with the via hardware. For information about how to use via, contact the hardware supplier. Important
The via protocol is not recommended. This function will be deleted in later versions of Microsoft SQL Server. Avoid using this function in new development work, and start to modify the application that is currently using this function.

Named Pipes and TCP/IP socket 
In a fast LAN environment, the transmission control protocol or Internet Protocol (TCP/IP) Socket Client and the named pipes client have the same performance. However, the slower the network speed [for example, on a WAN or dial-up network], the more obvious the performance difference between the TCP/IP Socket Client and the named pipes client. This is because the mechanism of inter-process communication (IPC) has different communication modes among peers.

For Named PipesGenerally, network communication is more interactive. A peer sends data only when the other peer uses the READ command to request data. Before reading data, the network generally reads a series of information that looks at named pipes. This may cause a high overhead in the slow network and cause excessive network traffic. Other network clients will also be affected.

It is also important to clarify whether the local or network pipeline is discussed. If the server application runs locally on the computer that runs the SQL server instance, you can select the local Named Pipes protocol. Local Named Pipes runs in kernel mode and is fast.

For TCP/IP socketsThe data transmission efficiency is higher and the cost is less. Data transmission can also take advantage of the performance enhancement mechanism of TCP/IP sockets, such as window setting and delay confirmation. This may be useful in slow networks. For different types of applications, such performance differences may be very large.

TCP/IP socket also supports a backlog of queues. When trying to connect to SQL Server, this queue can bring limited stability compared to named pipes that may cause a busy pipeline error.

Generally, TCP/IP works better in a slow LAN, Wan, or dial-up network. When the network speed is not a problem, named pipes is a better choice, because it is more powerful, easier to use, and has more configuration options.
Enable Protocol

This protocol must be enabled on both the client and server to work properly. The server can listen to all enabled Protocol requests at the same time. The client computer can select a protocol or try these protocols in the order listed in the SQL Server Configuration Manager. Note:

Microsoftsql server does not support Banyan vines ordered packet protocol (SPP), multi-protocol, appletalk, or nwlink IPX/SPX network protocol. In the past, clients connected using these protocols must select other protocols to connect to SQL Server.

For a brief tutorial on how to configure the protocol and connect to the database engine, see Tutorial: getting started with the database engine.

========================================================== ==========================================

1. What is a named pipe?
Like TCP/IP (transmission control protocol or Internet Protocol), a named pipe is a communication protocol. It is generally used in Lan, because it requires the client to have the permission to access server resources.
To answer this question, I 'd better extract the official Microsoft documents (see "network protocols in SQL Server ")

2. Why use the named pipe?
There are two reasons for using named pipelines:
Increase speed
Assuming that the domain name is also in the LAN, the use of the named pipe protocol is faster than the TCP/IP protocol.
Increase security
Because the named pipe can only be used for LAN, if the server disables the TCP/IP protocol and only enables the named pipe, some security risks can be avoided.

 

3. How to use a named pipe?
Connect to the SQL server and client using the Named PipeCodeThe approximate syntax of the connection string in is as follows:
"Server =.; database = northwind; uid = sa; Pwd = pass @ word; network library = dbnmpntw"

If the server is named
"Server =./InstanceName; database = northwind; uid = sa; Pwd = pass @ word; network library = dbnmpntw"

[Note] the named pipe protocol must be enabled on the server and the broswer service must be started.
To avoid the hassle of connecting strings, and to avoid leaking some confidential information about our connection data. SQL Server native client also supports alias Definition

If you have created an alias, you can directly write it in the connection string.
"Server = myserver; database = Master; Integrated Security = true ;"
It looks like we used to define an ODBC data source, isn't it?
If the connection string is leaked, it is impossible for someone with ulterior motives to guess what the name of our server is and what protocol is used for connection.

========================================================== ==========================================

Select the network protocol:

Http://msdn.microsoft.com/zh-cn/library/ms187892.aspx (Microsoft official)
SQL Server native client programming:

Http://msdn.microsoft.com/zh-cn/library/ms130892.aspx (Microsoft official)

Named Pipe in SQL Server and Its Usage:

1) http://www.cnblogs.com/chenxizhang/archive/2009/04/23/1441913.html (cnblogs)