Connecting to SQL Server

Remember, in Chapter 1 I briefly discussed the client-side application development environments, APIs, and libraries you can use to write applications that connect to SQL Server. Now you will learn about the communications mechanisms and data streams that make it possible for any modern programming language and the management utilities to create applications that can connect to and exchange data with SQL Server.

Interprocess Communications

SQL Server 2005 communicates with its clients via interprocess communication (IPC) mechanisms. A facility of the Win32 API interprocess communications architecture, IPC allows separate or isolated processes to safely communicate with each other. IPC can be invoked between processes on the same host or between remotely placed hosts over a network, even one as large as the Internet. The process on one computer opens a connection to a process running on another computer, and data is transmitted and returned between the two processes. Such a facility makes it easy to communicate and interact with a SQL Server centrally and concurrently with many users.

SQL Server clients can connect to SQL Server, open connections, transmit data such as XML and T-SQL, and call functions and procedures (like .NET services and remote method invocation and other distributed processing architecture). IPC is an asynchronous facility and does not block the client from performing local duties, or even from opening up communications with other processes. If you wish to dig into IPC further, any good book that comprehensively covers the Win32 API will do.

There are two parts to an IPC: the API and the protocol. The API is the function set used to “speak” to and “listen” to the network IPC. When an IPC is between a client and server on the same host, the local Named Pipes or shared memory components are used for the IPC. If the processes are split across a network, the network IPC is used. SQL Server has facilities for a number of network IPC options. Now, let’s look into SQL Server’s so-called Net-Libraries, the staging ground for SQL Server IPC communications.

The Client and Server Net-Libraries

SQL Server makes use of a collection of libraries called Net-Libraries that provide the necessary communication between the client applications and SQL Server. The stack of services in which the Net-Libraries collection is located is illustrated in Figure 4–1, which shows the services listed in the Configuration Manager. Note that Banyan VINES, Multiprotocol, AppleTalk, and NWLink (IPX/SPX) are no longer supported. The Net-Libraries handle the task of calling the IPC APIs to send data from client to server and vice versa. The Net-Library essentially allows the service providers to hand off this task to a separate collection of functions.

Figure 4–1: SQL Server Configuration Manager for managing protocols and SQL Server protocols

The client applications you build or support make a call to one of a number of service providers-ADO.NET, ADO, OLE DB, ODBC, DB-Library, or the Embedded SQL API-which then exchanges data with one of the Net-Libraries supported by SQL Server 2005. Table 4–1 lists the supported Net-Libraries.

Table 4–1: Default Network Address that SQL Server 2005 Monitors

Net-Library	Default Instance Address	Named Instance Address
TCP/IP Sockets	TCP Port 1433	Chosen dynamically when the instance is started
Named Pipes	\\computername\pipe\sql\query	\\. \pipe \MSSQL$SQLNAME\sql\query

If SQL Server is installed on the local host (such as the free SQL Server Express or the SQL Server Developer’s Edition), as illustrated in Figure 4–1, the client’s shared memory Net-Library is used. If SQL Server is installed on a remote host, the client’s network Net-Libraries are used to establish IPC communications over the network. The clients can use any of the Net-Libraries available to them.

The Net-Library sits at the top of the network stack, where it filters and routes data to the correct Net-Library dedicated to a protocol. It allows SQL Server to support multiple protocols, which is why it is called the Super Socket Net-Library. At the server end you will find the identical architecture, which allows for communications from the server to the client. In addition, the Primary Net-Library routes packets over legacy networks to any secondary Net-Library routers that may exist on the network. This lets SQL Server support legacy network APIs.

The Net-Library-IPC communications functionality is positioned far below both the client and server processes, and you will have little to do with them. For legacy applications that will connect to SQL Server 2005, you need only configure your applications to support the respective service provider used by your code. ADO.NET and ADO, for example, sit above OLE DB. As a DBA, you might have more to configure, especially if you are not using a Microsoft network. For example, the Primary Net-Library is also used to implement the Secure Sockets Layer (SSL) for encrypted communications between client and server.

Each instance of SQL Server can listen in on a collection of Net-Libraries, and you might find that when you installed SQL Server, you installed the default Net-Library on a network that does not support it. This is where the Client Network Utility comes into the picture.

Working with Net-Libraries

The SQL Server Configuration Manager is used to set up and configure the client and server Net-Libraries. When you install the server, both the client and server Net-Libraries are installed, as is the SQL Server configuration manager. If you’re distributing client applications, you may need to ensure that the Microsoft Data Access Components (MDAC) you are supporting in your client are installed on the target computers.

If your applications support the .NET Framework and ADO.NET, then you need only ensure that a version of the .NET Framework that works with your apps is supported on your target computers. SQL Server installs all the Net-Libraries, but only one or two may actually be active. The client and server Net-Libraries can be enabled or disabled, as you need, for each instance of SQL Server and for every client, or proxy process. When you disable a Net-Library, or deactivate it, it remains on the system for later use.

On the client side, you will need to be involved in the following DBA work:

• Enabling or Disabling client Net-Libraries.

• Specifying the order in which client Net-Libraries are made available for all connections (except server aliases).

• Defining server aliases that define specific Net-Libraries and connection parameters to invoke when connecting to instances of SQL Server from version 7.0 and earlier.

The default instance of SQL Server listens on the same default, or preconfigured, network addresses as earlier versions of SQL Server. This allows clients that were once connected to earlier versions of the DBMS to maintain connectivity, albeit to a new version of the server. Table 4–1 shows the default network addresses that instances of SQL Server listen on.

What Table 4–1 also demonstrates is that when your client Net-Library connects to an instance of SQL Server 2005, it needs only the network name of the computer running the instance and the instance name. For example, when your application attempts to connect to SQL Server, dbnetlib.dll opens the connection to UDP port 1434 (the monitor) on the computer network name specified in the connection definition. The name need not be a UNC or NetBIOS name either, because the network name could be a TCP/IP address followed by the name of the SQL Server instance. Such hard coding is, however, not good practice or practical for software deployment. And if your network is unable to resolve the name, there is obviously a “pothole” in your network somewhere.

There are several possible causes for a connection failure related to the Net-Libraries over a network:

• The requested instance of SQL Server is not running. The host may be down, or the service may be stopped.

• The Net-Library being invoked on the client is not active on the server. (This is akin to trying to call someone who does not have telephone service.)

• The computer name cannot be resolved. (You could troubleshoot this by trying to attach to a TCP/IP address instead.)

• The SQL Server port 1433 is unavailable. This can happen if the network administrator prevents access to this port, or some other application is blocking the access. For example, over a wide area network, a firewall or a router may inadvertently filter out ports 1433 and 1434.

If the client and server are on the same machine, it is relatively easy to reference the default instance.

Inside SQL Server Data Streams

Now that you know how your clients open communications to SQL Server, the client/server communications architecture will become clearer when you find out what gets sent up and down the connection. Client and server make use of an application-level formatting protocol known as the Tabular Data Stream (TDS) to format data for transmission and reception. You might think of this protocol as the pattern-storing technology in your average Star Fleet transporter. Without the pattern protocol, we would not be able to reassemble a human or an object after transportation. TDS packets are encapsulated in the packets that are built by each protocol’s Net-Library. The TCP/IP Sockets Net-Library, for example, encapsulates the TDS packets inside the TCP/IP packets or the TCP/IP protocol.

With support for XML, the contents of the TDS packets can be either a relational result set or an XML document. If you specify the FOR XML argument in your T-SQL code, the database engine will stream an XML document back to the application (this is also supported in OLE DB). If FOR XML is not specified, the database engine will send a result set back to the client application.

Without FOR XML, the TDS packet contains the rows of the result set, with each row tabulated to show one or more columns according to what you specify in the SELECT statement. XML is packaged in the TDS packet as one long stream of data, with each packet restricted to being no more than 4KB in size.

SQL Server’s packet size can also be configured. The default is the same size as TDS packets. If SQL Server packets are configured smaller than TDS packets, the packets will be disassembled on the sending computer and packaged into smaller packages. The protocols on the client computer reassemble the inbound packets.