SQL SERVER NETWORK PROTOCOL

What is a Protocol?
• A uniform set of rules that enable two devices to connect and transmit data to one another.
• Protocols determine how data are transmitted between computing devices and over networks.

In this article, I am trying to understand how SQL Server communicates on the network.

THE FOUR NETWORK PROTOCOLS IN SQL SERVER ARE:

SQL SERVER NETWORK PROTOCOL

Shared Memory
This is the simplest protocol of all the other protocols available in SQL Server. The limitation is that the client applications must reside on the same machine where the SQL Server is installed.

Named Pipes
This protocol can be used when your application and SQL Server resides on a local area network. 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).

TCP/IP
This is the most popular and common protocol widely used throughout the industry today. It communicates across interconnected networks and is a standard for routing network traffics and offers advanced security features.

VIA
Virtual Interface Adapter (VIA) protocol works with VIA hardware. This protocol has been deprecated and will no longer be available in the future versions of SQL Server.

BEST PRACTICES FOR USING SQL SERVER NETWORK PROTOCOLS

WHAT PROTOCOL TO USE ON A LOCAL SERVER?
If you are running your server application on a local computer which also runs SQL Server 2008 then Shared Memory or Named Pipes protocol is your best choice. However, Named Pipes get loaded in kernel mode of the operating system and are much faster on the local machine.

Note: This is only true if both the application and SQL Server are running on the same machine and if not then it would use Network Pipes that can be used over Local Area Networks.

WHAT PROTOCOL TO USE ON A LOCAL AREA NETWORK (LAN)?
As long as your Local Area Network is fast, both TCP/IP and Named Pipes would perform with the same speed. However, if there are more requests waiting to hit the server then Named Pipes may encounter pipe-busy error when trying to connect to SQL Server whereas, TCP/IP sockets doesn’t experience this problem as it supports  backlog queue for any number of requests.

WHAT PROTOCOL TO USE ON A WIDE AREA NETWORK (WAN)?
Data transmission using TCP/IP sockets is much more streamlined and has relatively less overhead compared to other network protocols. TCP/IP protocol can also take advantage of many performance features supported by the operating system that includes windowing, delayed acknowledgement which can be very helpful on slow networks.


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.