SQL Server 2008 and Clients – the long story

Let's talk about clients and SQL Server 2008. First, a little history…

Every new version of SQL Server seems to include a new feature that requires a change to the protocol that SQL Server uses to talk to clients, the TDS protocol. TDS stands for tabular data stream, and is a propriatary protocol used by both SQL Server and Sybase. Since version 4.21, SQL Server's version of TDS and Sybase's version have "grown apart". Because TDS includes version negotiation you can still use old libraries to talk to newer versions, albeit at reduced functionality.

Even if they don't update the TDS protocol, there will always be new features that require changes to the client stack at some level.

SQL Server comes with support for the following client stacks:
ODBC – Open Database Connectivity, related to the ANSI SQL standard, vendor neutral
OLE DB – a COM-based vendor neutral library
ADO.NET – a .NET-based vendor neutral library

ADO (classic) is an IDispatch-friendly library over OLE DB
LINQ to SQL and Entity Data Model use ADO.NET to talk to the database. Remember LINQ to SQL is actually LINQ to SQL Server at present.

In addition, Microsoft ships a JDBC (which does not stand for Java Database Connectivity, the docs say so 😉 driver separately. DBLIB also still works, but not shipped with SQL Server any more. It's functionality is limited to features that existed in SQL Server 6.5, IIRC. Other vendors (e.g. DataDirect) ship SQL Server clients too. DataDirect licenses the TDS stack.

Before SQL Server 2005, providers and drivers were part of MDAC. MDAC (Microsoft Data Access Components) was once a separate install, but now its part of the OS. TDS libraries were separate DLLs (e.g. DBMSSOCN.dll). This stopped in

SQL Server 2005, and there are two main distribution vehicles:
SQL Native Client (SQLNCLI): OLE DB, ODBC, and network libs
ADO.NET System.Data.dll: SqlClient provider and network libs

So how does this happen in SQL Server 2008? For OLE DB and ODBC, there is a new version of SQL Native Client, version 10. Since MDAC is now part of the operating system, if you install SQL Server 2008 over SQL Server 2005, you'll now see three ODBC drivers, on my CTP4 + VS 2008 beta 2 system:

SQL Server          version 2000.86.1830.00
SQL Native Client   version 2005.90.3042.00
SQL Server Native Client 10.0 version 2007.100.1049.14

For OLE DB there are three providers:
Microsoft OLE DB Provider for SQL Server
SQL Native Client
SQL Server Native Client 10.0

For ADO.NET, the situation is a bit more interesting. There's a revision to System.Data.dll to include the new functionality. The new version currently ships with Visual Studio 2008 Beta 2 and its version number currently is 2.0.50727.1378. It simply replaces the version that's installed on the system (and in the GAC) at the time. There's only one ".NET 2.0" version registered in the GAC, as "Version Number 2.0.0.0". Hmmm….seems very "MDAC-like with MDAC as part of the operating system" (that is, the operating system includes .NET 2.0).

If you want to use the new SQL Server 2008 functionality, like date, time, datetime2, dateoffset, table-valued parameters and large UDTs/UDAggs (and perhaps FILESTREAM support when it arrives) with OLE DB or ODBC, you need to use the new driver/provider. This means changing the connection string. AND RETESTING. This also applies to SSIS packages, Reporting Services reports and anywhere else you used OLE DB or ODBC.

If you only install the operating system and SQL Server 2008, you won't have the original (version 9.0) provider and driver. These will be provided as a separate download, for folks that have installed only 2008 but haven't restested.

BTW, once upon a time, there was talk of "multiple versions of SQL Native Client running side-by-side, using the fusion SxS loader". That didn't happen. The new providers are separate and are registered in the registry separately. No SxS magic needed.