Inside Data Streams, Again

In the next chapter-in the section “Inside SQL Server Data Streams”-we will discuss the TDS, packet-moving mechanisms, that encapsulate data in a special transmission format that is sent to and from the server. TDS, however, is a constantly evolving protocol, and as SQL Server matures like the finest sauvignon, so will you have to deal with ensuring that legacy clients configured or developed for down-level TDS access can send and receive data to the server.

For the most part, down-level support requires no special intervention or labor on your part because SQL Server 2005, which is version 9.0 TDS, talks to TDS version 8.0 and lower versions. However, there are a few points that can trip you up.

The advanced data type support in TDS 9.0 can choke a TDS 4.2 client. TDS 4.2 was originally built for SQL Server 4.2, and the protocol was inherited by versions 6.0 and 6.5. It became obsolete with the great rewrite “event” of SQL Server in 1997 and 1998, which begot SQL Server 7.0 with its TDS 7.0 protocol. The TDS changed again with the advent of SQL Server 2000.

Specifically, TDS 4.2 clients do not support Unicode data types. When it happens that a TDS 4.2 client pulls Unicode data out of a version 8.0 table, the relational engine will have to convert the data to legacy character data before it can be sent onward to the client. What will happen is that Unicode data that has no ASCII equivalent, and there are more than 65,000 such characters, will draw a blank, literally, when it sends the data back. The result set data will then be full of holes back at the client.

Note 

Unicode, or the Unicode Character System (UCS), is a two-byte (16-bit) system that represents all the languages of the world in a collection of 65,536 characters. ASCII is a 7- or 8-bit (1-byte) system that can only support 256 characters. Comparing TDS 4.2 and 9.0 is like comparing the vocabulary of a two-year-old with the vocabulary of an adult.

TDS 4.2 clients do not support the version 7.0 and version 8.0 char, varchar, binary, and varbinary data types that are larger than 255 bytes, and SQL Server will thus truncate any values it needs to return to the client through TDS 4.2. As mentioned in Chapter 2, the preceding data types can now hold data up to 8,000 bytes.

TDS 7.0 clients also come up a little short against SQL Server 2005 and TDS 9.0. The sql_variant data type (discussed at length in Chapter 10 and Appendix) is not new to SQL Server 2005 but is essentially “Greek” to TDS 7.0. The relational engine will need to convert any data type of sql_variant to Unicode before transmission. TDS 4.2 clients need not apply

What then is your strategy for ensuring that your clients can interpret and correctly present TDS 9.0 data? If you are new to SQL Server support and administration and are taking over a DBA function, you will need to know this.

First, as I will often repeat in this book, “thinner is better.” The thinner the client, the less work you have to do later-as both a developer and a DBA-when new versions of SQL Server ship (roughly every five years). There is nothing worse for a DBA than to have to upgrade hundreds or thousands of fat clients that have more components than an early twentieth-century chronometer. And we’ll debate client versus server processing again in Chapter 16, as well as n-tier deployment strategies in Chapter 17.

Second, when a client connects to SQL Server, its TDS packets are built according to the logic programmed into the data provider it is using. The data provider could be either the Microsoft OLE DB provider for SQL Server, or the ODBC driver, or the DB-Library DLL (which is currently in the sunset years of its life). So you need to ensure that your clients are calling the correct versions of these drivers or providers on their computers; otherwise, the TDS packets they will be sending will be targeted to earlier versions of SQL Server. This does not cause as many problems for data on the way in, because the server-side libraries will be able to understand the inbound packets. The problems arise, as discussed, when the packets are sent back to the client and data get transformed beyond recognition. A blob of thousands of bytes will just not make it to the other side, because SQL Server will see that you connected on TDS 4.2 and make the necessary adjustments.