Network protocol in SQL Server

Network Protocol :

-------------------------

To connect to SQL Server Database Engine you must have a network protocol enabled. There are 4 types of protocol available with sql server.

1.       Shared memory
2.       TCP/IP
3.       Named pipes
4.       VIA

For any changes done in protocol properties requires sql server restart.

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.

·         You cannot create an alias using the shared memory protocol.
·         if shared memory is enabled, then connecting to the Database Engine by name, creates a shared memory connection.
·         Connection name (except ip address) is same for TCP/IP(for remote client and local client) and shared memory(local client).If both enable local client always use shared memory.
·         Specifying an IP Address in the Server box will result in a TCP/IP connection.

The following names will connect to the local computer with the shared memory protocol if it is enabled:

·        
·         \
·         (local)
·         localhost

The following query will return the protocol used for the current connection.
SELECT net_transport FROM sys.dm_exec_connections WHERE session_id = @@SPID;

 
TCP/IP : (Transmission Control Protocol/Internet Protocol)

TCP/IP is a common protocol widely used over the Internet. 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.

At the time of connection, the SQL Server Native Client component reads the server, protocol, and port values from the registry for the specified alias name, and creates a connection string in the format tcp:[\], or tcp:[\],.

To create a valid connection string using TCP/IP, you must:

• Specify an Alias Name.
• For the Server, enter either a server name to which you can connect using the PING utility, or an IP address to which you can connect using the PING utility. For a named instance append the instance name.
• Specify TCP/IP for the Protocol.
• Optionally, enter a port number for the Port No. The default is 1433, which is the port number of the default instance of the Database Engine on a server. To connect to a named instance or a default instance that is not listening on port 1433, you must provide the port number, or start the SQL Server Browser service.

At the time of connection, the SQL Server Native Client component reads the server, protocol, and port values from the registry for the specified alias name, and creates a connection string in the format tcp:[\], or tcp:[\],.

 TCP/IP parameters:

·         Enabled  : Possible values are Yes and No.
·         Keep Alive : Specify the interval (milliseconds) in which keep-alive packets are transmitted to verify that the computer at the remote end of a connection is still available.
·         Listen All : Specify whether SQL Server will listen on all the IP addresses that are bound to network cards on the computer. If set to No, configure each IP address separately using the properties dialog box for each IP address. If set to Yes, the settings of the IPAll properties box will apply to all IP addresses. Default value is Yes.
·         No Delay : SQL Server does not implement changes to this property.
·         TCP Dynamic Ports  :Blank, if dynamic ports are not enabled. To use dynamic ports, set to 0.
For IPAll, displays the port number of the dynamic port used.
 

The default instance of SQL Server listens for incoming connections on port 1433. The port can be changed for security reasons or because of a client application requirement. By default, named instances (including SQL Server Express) are configured to listen on dynamic ports. To configure a static port, leave the TCP Dynamic Ports box blank and provide an available port number in the TCP Port box.

SQL Server Database Engine can listen on multiple ports on the same IP address, list the ports, separated by commas, in the format 1433,1500,1501. This field is limited to 2047 characters.

To configure a single IP address to listen on multiple ports, the Listen All parameter must also be set to No, on the Protocols Tab of the TCP/IP Properties

Since the dynamic port number can change each time SQL Server starts, SQL Server provides the SQL Server Browser Service, to monitor the ports, and direct incoming connections to the current port for that instance. Using dynamic ports complicates connecting SQL Server through a firewall because the port number may change when SQL Server is restarted, requiring changes to the firewall settings. To avoid connection problems through a firewall, configure SQL Server to use a static port.

 

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).

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.

Unless changed by the user, when the default instance of Microsoft SQL Server listens on the named pipes protocol, it uses \\.\pipe\sql\query as the pipe name. The period indicates that the computer is the local computer, pipe indicates that the connection is a named pipe, and sql\query is the name of the pipe. To connect to the default pipe, the alias must have \\\pipe\sql\query as the pipe name. If SQL Server has been configured to listen on a different pipe, the pipe name must use that pipe. For instance, if SQL Server is using \\.\pipe\unit\app as the pipe, the alias must use \\\pipe\unit\app as the pipe name.

To create a valid pipe name, you must:

• Specify an Alias Name.
• Select Named Pipes as the Protocol.
• Enter the Pipe Name. Alternatively, you can leave Pipe Name blank and SQL Server Configuration Manager will complete the appropriate pipe name after you specify the Protocol and Server
• Specify a Server. For a named instance you can provide a server name and instance name.

At the time of connection, the SQL Server Native Client component reads the server, protocol, and pipe name values from the registry for the specified alias name, and creates a pipe name in the format np:\\\pipe\ or np:\\\pipe\.

 For a named instance, the default pipe name is \\\pipe\MSSQL$\sql\query.

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

VIA:

Virtual Interface Adapter (VIA) protocol works with VIA hardware. For information about how to use VIA, contact your hardware vendor.

The VIA protocol is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.