The TDS 4.2 protocol is an application-level protocol used for the transfer of requests and responses between clients and database server systems. In such systems, the client will typically establish a long-lived connection with the server. Once the connection is established using a transport-level protocol, TDS 4.2 messages are used to communicate between the client and the server. A database server can also act as the client if needed, in which case a separate TDS 4.2 connection needs to be established. Note that the TDS 4.2 session is directly tied to the transport-level session, meaning that a TDS 4.2 session is established when the transport-level connection is established and the server receives a request to establish a TDS 4.2 connection. It persists until the transport-level connection is terminated (for example, when a TCP socket is closed). In addition, TDS 4.2 does not make any assumption about the transport protocol used, but it does assume the transport protocol supports reliable, in-order delivery of the data.
The TDS 4.2 protocol includes facilities for authentication and identification, channel encryption negotiation, issuing of SQL batches, stored procedure calls, returning data, and transaction manager requests. Returned data is self-describing and record-oriented. The data streams describe the names, types, and optional descriptions of the rows being returned. The following figure depicts a (simplified) typical flow of communication for TDS 4.2.
Figure 1: Communication flow in the TDS 4.2 protocol
The following example is a high-level description of the messages exchanged between the client and the server to execute a simple client request, such as the execution of a SQL statement. It is assumed that the client and the server have already established a connection and authentication has succeeded.
The server executes the SQL statement and then sends back the results to the client. The data columns being returned are first described by the server (represented as column metadata that contains COLNAME and COLFMT) and then the rows follow. A completion message is sent after all the row data has been transferred.
Server:COLNAMEdata stream COLFMTdata stream ROWdata stream . . ROWdata stream DONEdata stream
See section 2.2.4 for additional information on the correlation between the data stream and TDS 4.2 buffer.