TDS Connections and supported versions

Table of Contents

1. What is TDS Connection?
2. What version of TDS does ScaleArc support?
3. What are various TDS-.NET-SQL Server version mapping
4. What could be the next step if Applications are using TDS 7.1 or lower

01. What is TDS Connection?

TDS is a protocol, a set of rules describing how to transmit data between two computers. Like any protocol, it defines the types of messages that can be sent, and the order in which they may be sent. Protocols describe the "bits on the wire", how data flow.

It is an application-level protocol to transfer data between a database server and its client.

Below article from microsoft has details about Tabular data Stream Protocol (TDS)

https://msdn.microsoft.com/en-us/library/dd304523.aspx

02. What version of TDS does ScaleArc support?

ScaleArc is optimised for handling TDS 7.2 and greater revisions.

ScaleArc gives you an option to turn ON or OFF "TDS 7.1 Connections Pass-through" under "CLUSTERS>DEBUG" by which you can control complete pass through mode for TDS 7.1 connections. TDS 7.1 is typically seen on pre-SQL 2005 client drivers.

For any applications running TDS version 7.0 or lower, it is highly recommeded to have drivers updated.

03. How to determine the TDS version for all live connections to SQL?

The following is executed on the SQL command line session (i.e. SQL Server Management Studio)

SELECT session_id, protocol_type, driver_version =
CASE SUBSTRING(CAST(protocol_version AS BINARY(4)), 1,1)
   WHEN 0x70 THEN 'SQL Server 7.0'
   WHEN 0x71 THEN 'SQL Server 2000'
   WHEN 0x72 THEN 'SQL Server 2005'
   WHEN 0x73 THEN 'SQL Server 2008'
   WHEN 0x74 THEN 'SQL Server 2012'
   ELSE 'Unknown driver'
END
FROM sys.dm_exec_connections;

04. What are various TDS-.NET-SQL Server version mapping

Below article from microsoft shows the TDS version to .Net framework version mapping : 

https://msdn.microsoft.com/en-us/library/dd339982.aspx 

Below article has information on how to choose TDS protocol version

http://www.freetds.org/userguide/choosingtdsprotocol.htm

05. What could be the next step if Application are using TDS 7.1 or lower?

1. It is highly recommeded to check if your applications are as per microsoft recommended versioning as mentioned in above article.
2. For compatibility reasons microsoft advises to use latest TDS version supported by your application, you should avoid using older versions as it could be removed in a future version of FreeTDS or it could clash with a future SQL server version.
3. If your environment behaviour is not matching with the version mapping, it is highly possible that their may be a need to rebuild application with newer version of .NET framework.
4. In other words it might need recompiling of applications with newer .net versions or updating drivers that IIS use for ASP.NET.
5. It is highly recommended to get .NET, Microsoft and application owners advise prior to performing any upgrades.