“The SQL Guy” Post # 1: BEST PRACTICES FOR USING SQL SERVER NETWORK PROTOCOL

  • Article

SQL08_v_webWhen I first joined my team at Microsoft, I was labelled “the SQL guy” for my SQL Server and Oracle background by others on the team.  It didn’t help that the first major event that I was involved with at Microsoft was the SQL Server 2005 Launch Tour.  Over the years I have done many things and worked with great technologies, but I still have a passion for databases and, since SQL Server codename “Denali” will be launching later this year, I wanted to bring back “the SQL guy” with some blog posts on SQL Server based on content that I get sent to me by the SQL Server product team.  I hope you enjoy this first one in “the SQL guy” series:

WHICH IS THE BEST PROTOCOL TO CONNECT TO SQL SERVER?

This is a question often asked from the community and the answer to this question depends based on your network environment whether you are on a local computer with no networks or whether you are on a Local Area Network or whether you are on a Wide Area Network. Here is  some guidance / recommendation below that works best for many SQL Server installations.

In order to connect to SQL Server, your client machine must have a network protocol enabled that can communicate with SQL Server database engine. By default, SQL Server can listen on 4 different protocols that come installed with SQL Server. However, not all the installed protocols will be enabled and you would need to enable them at the server using SQL Server Configuration Manager to be able to use them from your client machine.

THE FOUR NETWORK PROTOCOLS IN SQL SERVER ARE:

Protocol

Description

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

(DEPRECATED)

DO NOT USE

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.

DamirB-BlogSignature