2018-08-07

Migrate from OLEDB to ODBC - or not

August 11, 2011 (2011-08-11) Microsoft announced with the blog post "Microsoft is Aligning with ODBC for Native Relational Data Access" that they recognized ODBC as the de-facto standard for data access and that OLE DB was scheduled for deprecation. The blog post was later updated with links to tools that should help the migration. Also Microsoft made the FAQ "Microsoft is Aligning with ODBC for Native Relational Data Access - FAQ" to help with the details in the migration.
This was at the time big news as Microsoft for several years had promoted OLE DB for ODBC when building applications involving Microsoft technology. And of course this blog post and it's consequences was discussed a lot, e.g. by Dan Guzman in the blog posts "RIP OLE DB" (2011-09-04) and "Deprecated SQL Server Data Access Technologies" (2017-02-04).
If you are interested in some background then Hal Berenson wrote a blog post about "OLE DB and SQL Server: History, End-Game, and some Microsoft “dirt”" where he gives some great insight as a driving force behind OLE DB.

But then in October 2017 Microsoft released a new OLE DB driver for SQL Server. In the blog post "Announcing the new release of OLE DB Driver for SQL Server" (2019-10-06) Microsoft also announced that OLE DB data access technology was un-deprecated.
A somewhat wobbling course on a rather important subject. But actually I do think it makes sense as both technologies are very widespread and we in reality are beyond the point of no return on each technology.

It is was highly recommended to migrate your solutions and applications from the COM-based OLEDB to ODBC to be in support as described in the document "Data Access Technologies Road Map".

A minor detail is that SQL Server Native Client (SNAC) and MDAC/WDAC continues to be deprecated. So when using OLE DB you should still migrate but to another OLE DB driver with msoledbsql.

Unfortunately there is not much literature on neither OLE DB or ODBC, but with some luck you can find the book "Inside ODBC" by Kyle Geiger (1995, Microsoft Press). The book holds a lot of examples on ODBC are in C/C++ as ODBC is designed for native code.
The "Windows Data Access Components SDK" has a lot of interesting documentation on both OLE DB, ODBC and ADO.

OLE DB

OLE DB is a COM technology with low-level interfaces in a provider for at database service like the  SQL Server Database Engine RDBMS. Back in the days before .NET a Microsoft application could either use OLE DB direct or use the high-level interface ADO. There are still a lot of ADO connectivity in the real world. Especially with Office like Excel in VBA solutions I often meet OLE DB by ADO.
There are several OLE DB providers for various data platforms in WDAC, formerly called MDAC. For SQL Server there are also OLE DB providers in SNAC, but as .NET providers was included by default in .NET then SNAC in general was obsolete for .NET.

The blog post "OLE DB Supportability Guidelines" (2017-08-15) on the SQLNCli blog is written before October 2017 but recommend to continue using OLE DB if no blocking issues are experienced. This I think is the first hint on OLE DB continuation with Microsoft.

SQLNCli blog: "Released: Update to Microsoft OLE DB Driver for SQL Server" (18.1)

ODBC

ODBC is a cross-platform interface for data sources. The interface is as CLI defined as ISO standard (ISO/IEC 9075-3:2016).

When ODBC was announced as the de-facto standard on SQL Server connectivity, Microsoft released some material on ODBC and how to convert applications. A general introduction to ODBC was given in the document "ODBC How-to Topics". The technical article "Converting SQL Server Applications from OLE DB to ODBC" gives some details on converting from OLE DB to ODBC e.g. with a description on mapping OLE DB objects to ODBC APIs.

The Microsoft document "Download ODBC Driver for SQL Server" lists the various downloads for the SQL Server ODBC driver. There are several as Microsoft not only deliver a driver for Windows but also for other operating systems like macOS and a collection of Linux.

Microsoft SQLNCli team blog: ODBC Driver 17.2 for SQL Server Released

SNAC

SQL Server Native Client (SNAC) was created to support special SQL Server Features like database mirroring. Actually the installation set is a single dynamic-link library (DLL) with a special version of SQL Server OLE DB provider and ODBC driver. Also the library contain a special version of the ADO API. That is some versions of SNAC...

SNAC is now deprecated, and the document "SNAC lifecycle explained" gives a short description of the SNAC versions, their general contents and support.

No comments: