SQL Server Architecture - SQL Server 2012 Internals and Troubleshooting - page 7

A Basic SELECT Query
The details of the query used in this example aren't important — it's a simple SELECT statement with
no joins, so you're just issuing a basic read request. It begins at the client, where the i rst component
you touch is the SQL Server Network Interface (SNI).

SQL Server Network Interface
The SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection
between the client and the server. It consists of a set of APIs that are used by both the database
engine and the SQL Server Native Client (SNAC). SNI replaces the net-libraries found in SQL Server
2000 and the Microsoft Data Access Components (MDAC), which are included with Windows.

SNI isn't coni gurable directly; you just need to coni gure a network protocol on the client and the
server. SQL Server has support for the following protocols:

➤

Shared memory — Simple and fast, shared memory is the default protocol used to connect
from a client running on the same computer as SQL Server. It can only be used locally,
has no coni gurable properties, and is always tried i rst when connecting from the local
machine.

➤

TCP/IP — This is the most commonly used access protocol for SQL Server. It enables you
to connect to SQL Server by specifying an IP address and a port number. Typically, this hap-
pens automatically when you specify an instance to connect to. Your internal name resolu-
tion system resolves the hostname part of the instance name to an IP address, and either you
connect to the default TCP port number 1433 for default instances or the SQL Browser
service will i nd the right port for a named instance using UDP port 1434.

➤

Named Pipes — TCP/IP and Named Pipes are comparable protocols in the architectures in
which they can be used. Named Pipes was developed for local area networks (LANs) but it
can be inefi cient across slower networks such as wide area networks (WANs).
To use Named Pipes you i rst need to enable it in SQL Server Coni guration Manager
(if you'll be connecting remotely) and then create a SQL Server alias, which connects to the
server using Named Pipes as the protocol.

Named Pipes uses TCP port 445, so ensure that the port is open on any i rewalls between
the two computers, including the Windows Firewall.

➤

VIA — Virtual Interface Adapter is a protocol that enables high-performance
communications between two systems. It requires specialized hardware at both ends and a
dedicated connection.

Like Named Pipes, to use the VIA protocol you i rst need to enable it in SQL Server
Coni guration Manager and then create a SQL Server alias that connects to the server using
VIA as the protocol. While SQL Server 2012 still supports the VIA protocol, it will be
removed from a future version so new installations using this protocol should be avoided.
Regardless of the network protocol used, once the connection is established, SNI creates a secure
connection to a TDS endpoint (described next) on the server, which is then used to send requests
and receive data. For the purpose here of following a query through its life cycle, you're sending the
SELECT statement and waiting to receive the result set.