SQL Server Connection Protocols

Amidst of all the jazzy terms and technical stuff, there are times when we simply miss out taking a note of building blocks on top of which our IT systems are breathing. One such topic is that of SQL Server Connection Protocols. Am sure most of us remember the four key protocols, but at times must have felt a void around what they really mean or what’s their clear line of segregation. That’s what pushed me to put this note together.

Four Key connection Protocols for SQL Server are/were:

- Shared Memory

- Named Pipes

- TCP/IP

- VIA (to be depreciated in future releases).

Below is a quick write up for more details on each of them.

TCP/IP

By default, the default protocol for SQL Server clients is TCP/IP. If the connection cannot be made using TCP/IP, the other enabled protocols are attempted.
Connection String Format:
Alias Name   <serveralias>
Port              <blank or Pipe name>
Protocol        TCP/IP
Server          <servername or IP or Localhost>\<InstanceName>

Named Pipes

Both on Windows and POSIX systems, named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack.
By setting up additional named pipes, you can have multiple DB servers running, each with its own request listeners. The advantage of named pipes is that it is usually much faster, and frees up network stack resources.
– BTW, in the Windows world, you can also have named pipes to remote machines — but in that case, the named pipe is transported over TCP/IP, so you will lose performance. Use named pipes for local machine communication.
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.
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.
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.
Default Pipe for a named Instance: \\<computer_name>\pipe\MSSQL$<instance_name>\sql\query
Default Pipe for a default Instance: \\.\pipe\sql\query
Connection String Format:
Alias Name  <serveralias>
Pipe Name   <blank or Pipe name>
Protocol    Named Pipes
Server      <servername or IP or Localhost>

Shared Memory

When you connect to the Database Engine from a local client (the client application and SQL Server are on the same computer), the connection uses shared memory protocol by default. Formats: “”, “.”, “\”, “(local)\”. “localhost”, “.\namedInstance”

For connecting locally but using a different protocol, use:
“servername.domain.com”, “tcp:*”, “np:\\<computer_name>\pipe\<pipename>” etc.

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.

VIA

Virtual Interface Adapter (VIA) protocol works with VIA hardware. The VIA protocol is deprecated. This feature will be removed in a future version.
Connection String Format:
Alias Name    <serveralias>
Port        0:1433
Protocol    VIA
Server      <servername >\<InstanceName>

Why run 64-bit Windows?

Am sure by now majority of the platforms are and considering moving on to 64-bit platforms. If you aren’t yet sure of why’s – boy, you really are planning for a retirement, aren’t you !? No, its not mandated, but if scalability, vendor supportability and performance bothers you, then better think about it.

Now, there’s no point in re-writing what more experienced people have already summarized in way more details. But, here’s the reference for your considerations:

- Why run 64-bit Windows?

- Why run 64-bit Linux?

Now, amidst x86 and 64-bit decision, Itanium yet remains a question mark? HP and Intel have committed that they will continue to develop on it. But they dont seem to be showing anything promising beyond next planned version. Couple of Database Vendors (Oracle, Microsoft) have called out for Itanium phase-out already. Though others (IBM DB2, PostgreSQL, Sybase) are absorbing this as an opportunity to increase their footprint. If you ask me, I never liked IA platform much. Not that its bad anyhow, but it broke the most crucial backward compatibility. That’s a big no-no when you talk of Enterprise platforms. And that proved to be one reason that Industry was slow to get along with this Intel-HP development. Specifically, given today’s situation, it would be wise to look at what your data-vendor is supporting and what goes well within your existing/planned server farm.