Choosing a Network Protocol

To connect to SQL Server Database Engine you must have a network protocol enabled. Microsoft SQL Server can service requests on several protocols at the same time. Clients connect to SQL Server with a single protocol. If the client program does not know which protocol SQL Server is listening on, configure the client to sequentially try multiple protocols. Use SQL Server Configuration Manager to enable, disable, and configure network protocols.

Shared Memory

Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

Note

Clients that use MDAC 2.8 or earlier cannot use shared memory protocol. If these clients try this, they are automatically switched to the named pipes protocol.

TCP/IP

TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems. TCP/IP includes standards for routing network traffic and offers advanced security features. It is the most popular protocol that is used in business today. Configuring your computer to use TCP/IP can be complex, but most networked computers are already correctly configured. To configure the TCP/IP settings that are not exposed in SQL Server Configuration Manager, see the Microsoft Windows documentation.

Named Pipes

Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer).

Named Pipes vs. TCP/IP Sockets

In a fast local area network (LAN) environment, Transmission Control Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are comparable with regard to performance. However, the performance difference between the TCP/IP Sockets and Named Pipes clients becomes apparent with slower networks, such as across wide area networks (WANs) or dial-up networks. This is because of the different ways the interprocess communication (IPC) mechanisms communicate between peers.

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.

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 that is running an instance of SQL Server, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is very fast.

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. Depending on the type of applications, such performance differences can be significant.

TCP/IP Sockets also support a backlog queue. This can provide a limited smoothing effect compared to named pipes that could lead to pipe-busy errors when you are trying to connect to SQL Server.

Generally, TCP/IP is 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.

Enabling the Protocol

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.

For a short tutorial about how to configure protocols and connect to the Database Engine, see Tutorial: Getting Started with the Database Engine.