Understanding SQL Server Net Libraries

By:   |   Updated: 2011-03-08   |   Comments (2)   |   Related: > SQL Server Configurations


Problem

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others?

Solution

SQL Server provides different Net-Libraries, known as Dynamic-Link Library (DLL), to let clients communicate with SQL Server using different network protocols. These Net-Libraries specify which networking protocols to use while exchanging data back and forth between SQL Server and the clients. SQL Server provides Net-Libraries for almost all the commonly used networking protocols namely TCP/IP, Named Pipes, Shared Memory, etc... These Net-Libraries transparently send the requests from the client to SQL Server and return responses back from SQL Server to the clients.

Depending on what Net-Libraries you have enabled on the SQL Server, it starts listeners for these Net-Libraries. You can configure SQL Server to listen simultaneously on any combination of Net-Libraries. In other words, when the net-library listener on the server receives the TDS (Tabular Data Stream which is the format in which client requests are broken/formatted before sending it to the server as well as server responses broken/formatted before sending it back to clients) packets from the network, it reassembles them into original client requests and passes it up for processing. Please note each specific type of SQL Server Net Library listener will listen for the client requests if they are coming from the same type of Net Library (a matching pair of Net-Libraries must be enabled on both SQL Server and clients) and a connection from the client can be done using a single protocol only. For example, if the client sends the request to SQL Server using TCP/IP then on the server only the TCP/IP Net Library listener will pick up this request. Also, SQL Server will use the same network stack, over which the request was received, to send the response back to the client.

These are the different Net-Library options that are available:

Shared Memory
When you have a client which is on the same physical machine as your SQL Server, you can use this simplest net library which provides faster communication than other Net-Libraries as it bypasses the physical network stack. The reason is, it uses the Windows Shared Memory feature to communicate between SQL Server and the client. You might use this Net-Library while troubleshooting if other Net-Libraries are not working properly. This Net-Library is by default enabled and used when you specify either a period or (local) as your machine name or localhost or machine name or by prefixing machine\instance name with lpc: when connecting to a SQL Server instance, you can find more details here.

TCP/IP
TCP/IP (Transmission Control Protocol/Internet Protocol) is the default protocol on the clients running on the Windows platform. It allows communication across interconnected networks of computers with diverse hardware and operating systems. TCP/IP is considered as the first choice after Shared Memory, because of its inherent network traffic routing standards and advanced security features.

Named Pipes
Named Pipes protocol uses a part of memory of one process to store/pass the information to another process, it means the output of one process becomes the input for another process. The other process could be either on the local machine or could be on a remote machine (networked machine). This protocol is used in fast local area networks for better performance.

VIA
VIA stands for Virtual Interface Adaptor. This protocol works with VIA hardware. Please refrain from using this protocol in your newer development as it has been deprecated and will be removed in future releases of SQL Server.

Normally TCP/IP is the preferred protocol in a slow LAN, WAN or dial up network whereas Named Pipes is more considerable in fast local area networks, to learn more about these differences click here.

The script provided in script #1 below gives the details about existing connections like when the connection was established and what protocol is being used by that particular connection.

--Script #1 - Identifying the protocols being used by current connections
SELECT connection_id, connect_time, net_transport, net_packet_size, client_net_address
FROM sys.dm_exec_connections

Configuring Net-Libraries

You use SQL Server Configuration Manager to configure the client net libraries as well as server net libraries. As shown in the image below, go to SQL Native Client Configuration (on SQL Server 2008 and R2) and specify a default Net-Library for all the connections being made by this client or specify a specific Net-Library to connect to a specific SQL Server instance:

using sql server configuration manager to configure the client net libraries

Likewise you can use SQL Server Configuration Manager to configure server net libraries as well. As shown in the image below, go to SQL Server Network Configuration (on SQL Server 2008 and R2), choose the specific instance name for which you want to configure Net-Libraries and then enable or disable the Net-Libraries (network protocols) on which this particular SQL Server instance will listen for incoming requests.

go to sql native client configuration or sql server network configuration (on sql 2008 and r2)

Notes

  • SQL Server needs to be restarted to enable or disable a protocol.
  • In failover clustered environments only TCP/IP and Named Pipes network libraries are supported.
  • Once you have installed SQL Server native client on your client machine you are ready to connect to your SQL Server. By default all protocols except VIA (its deprecated and should not be used in newer development) are enabled on the client, and connections are attempted in the following protocol order: shared memory, TCP/IP, named pipes.
  • After enabling or disabling protocols, SQL Server must be restarted for the changes to take effect.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-03-08

Comments For This Article




Wednesday, September 17, 2014 - 8:06:37 AM - nagesh Back To Top (34561)

Recently we have faced an issue from application end where they are trying to fetch the data from application server to database server , but its resulted below error.

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.

I have gone through the database server and observed below information.

  • Remote query timed out is 600 secs
  • SQL Server 2008 Network Configuration protocols of defaltand also observed that namedpipes was in disabled state
  • In client network protocols list namedpipes are in enabled state.

Is named pipe should enabled in Local server protocols also in database server???Kindly guide me as early as possible.

 

 

Tuesday, July 16, 2013 - 8:01:37 AM - parker Back To Top (25853)

In my computer because of another one s/w instllation the sql server client protocals got deleted.

Could you please advice me how to restored the protocols  in my computer?

 

Thank you very much in Advance,

 

Parker Antony














get free sql tips
agree to terms