In every Server/Client model the first and foremost thing is the communication protocol through which the server and client talks to each other. Microsoft SQL Server offers different protocols for connectivity, In this article I will try to explain each protocol to make easier for you to choose the protocol that is right for you. I will also focus on how the connectivity mechanism works in various protocol scenario. What we will not be looking into this article is the security mechanism during connectivity which I will cover in my later posts.
Following are the network protocols commonly used in SQL Server, Other protocols include, VIA, HTTP, DAC which are used in special circumstances.
1. Shared Memory
Shared memory is the fastest connectivity protocol available as its name explains the client and server communicates through memory, and it works in a single server scenario only, which means the client and the server must exists on the same machine to use this protocol. Unfortunately this option in not applicable in most of the production scenarios as Database server and application server resides on different machines.
2. Named Pipes
SQL Server also offers connectivity through Named pipes which is a queuing mechanism, Named pipes are system persistent pipes which works in FIFO mode, Named pipes works in single server or Local area network scenarios.
TCP/IP is an industry wide network communication protocol used in LAN, WAN and internet scenarios. TCP/IP is the most widely used protocol for connectivity between database server & clients as it works in all different infrastructure topologies.
These protocols are installed as part of the Operating System installation and are not installed during SQL Server installation. If any protocol needs to be added it has to be first installed in the operating system. By default the above mentioned protocol comes with the Windows Operating system.
How the connectivity works.
Like in all Server Client models, there is a server application and multiple client application instances, in our case the Server application is the SQL Server Database engine. While the client application is called SQL Server Native Client (SQLCLI) Library, SQL Server Native Client (SQLNCLI10) is a data access technology that is new to Microsoft SQL Server, and it is a stand-alone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. All other applications that connect to SQL Server Data engine internally uses the SQL Server Native client library. Previously MDAC or DB Library used to fill this role. It is mandatory for a successful connectivity that both server and client are working on a common protocol.
From MSDN: “When the SQL Server Database Engine communicates with an application, it formats the communication in a Microsoft communication format called a tabular data stream (TDS) packet. The network SQL Server Network Interface (SNI) protocol layer, encapsulates the TDS packet inside a standard communication protocol, such as TCP/IP or named pipes. The SNI protocol layer is common to both the Database Engine and SQL Server Native Client. The SNI protocol layer is not directly configured. Instead, the server and SQL Server Native Client are configured to use a network protocol. Then, the Database Engine and SQL Server Native Client automatically use the appropriate protocol settings. The server creates a SQL Server object called a TDS endpoint for each network protocol. On the server, the TDS endpoints are installed by SQL Server during SQL Server installation. For the named pipes and shared memory protocols, there can only be one endpoint per instance. There are no configurable endpoints for these protocol types. For TCP/IP and VIA, there is a default endpoint, but additional endpoints can be created.”
When a connection to the Database engine is initiated by the SQL Server Native Client, the network protocol is selected based on the order defined in the SQL Client Configuration. And an attempt to establish a connection to the that protocols TDS endpoint is tried, if the connection fails then the next network protocol in the list is attempted.
SQL Server Browser Service
When an instance of SQL Server starts, if the TCP/IP or VIA protocols are enabled for SQL Server, the server is assigned a TCP/IP port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or "pipe," is used by that specific instance to exchange data with client applications. Because only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances. By default, when enabled, both named instances and SQL Server Express are configured to use dynamic ports, that is, an available port is assigned when SQL Server starts. If you want, a specific port can be assigned to an instance of SQL Server. When connecting, clients can specify a specific port; but if the port is dynamically assigned, the port number can change anytime SQL Server is restarted, so the correct port number is unknown to the client.
A SQL Server Browser services addresses this problem by assisting the client to locate the full address of the database engine, Upon startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all instances of SQL Server on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server.
When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.
If the SQL Server browser service is disabled, the client must provide the complete address of the database engine including the port number or pipe name in order to establish a connection, This only applies in the case of remote connections.
Another alternate to Full address problem is to create alias, you can define a friendly named alias for a database instance in the client configuration by specifying the protocol and the required details such as Server name or IP, and port or pipe address. And then using this alias in your client applications to address the database instance. Alias also gives you the flexibility of changing the Database instance’s address details without a need to change anything in the client applications, the only change would be to update the new address in the alias settings.