Adieu, OLE DB

The writing has been on the wall for some time, but a few days ago Microsoft made it official. OLE DB will no longer be supported with SQL Server after the Denali (SQL Server 11.0) release of SQL Server. Microsoft is "embracing" ODBC. "OLE DB will be supported for 7 years from launch" (of SQL Server Denali). Included with the announcement was a whitepaper on converting from OLE DB to ODBC.

I thought I ought to say somthing… After all,  I remember the collalary whitepaper (converting from ODBC to OLE DB) in 1996.

I used to have a fairly strong personal stake in OLE DB. In 1999 I went for work for Don Box's company and he thought it would be nice if I'd write a class for OLE DB. This would have to be a provider-centric class and I envisioned the majority of the students would be writers of OLE DB providers. The reason for this was that OLE DB interfaces were not IDispatch-based and there was an IDispatch-based library, ADO (which I've always called "ADO classic" after the introduction of ADO.NET) which layered over top of OLE DB. Consumers of OLE DB were expected to use ADO rather than native OLE DB. I was surprised that anyone would want a class in Native OLE DB and wondered about the market for such a class. I shouldn't have wondered…the class soon became my center of my work life and there were at least three other folks who taught it. Most of the students seemed to be writing OLE DB providers for various purposes, although there were a class of hard-core C++ developers that preferred OLE DB over ADO. There was eventually a set of ATL (Active Template Library) templates for OLE DB providers and consumers.

You see, OLE DB wasn't just another database API; at the time, it was touted as "Universal Data Access". Every bit of data, not just relational databases, was to be eventually exposed through a OLE DB provider. COM, the component object model, was in its hayday, and everything HAD to be exposed through COM. Including data access. OLE DB was quite a weird name, as the set of standard CoTypes and Interfaces have nothing to do with Object Linking and Embedding (OLE). And it was supposed to embrace more than just databases (DB).

The ANSI SQL standard had already codified an interface (part 3, the Call-Level Interface). One of the first, if not the first implementation of this interface, was ODBC (an acronym which stands for Open Database Connectivity). The "urban legand" was that ODBC was invented by Microsoft, the reality was that Microsoft was a member of the consortium that invented it. There's another urban legend that OLE DB is faster than ODBC. From what I remember this was only true with the use of the Row cotype, meant for singleton rows, which very few programmers used. Otherwise OLE DB always trailed a bit slower. But starting around 1996, OLE DB was the thing. ODBC is very RDBMS-centric, and, at the time, seemed passe. There was a bridge provider, MSDASQL, the OLE DB provider for ODBC drivers, as a connection to the old ODBC-world.

In the COM era, OLE DB pushed past ODBC in the Microsoft space. Relational, that is Connection-Command-Rowset-Metadata constructs were embraced and enhanced. You could extend OLE DB at the CoType level, interface level, and method level, if need be. Custom "service components" and "service providers" were written for special purposes and hooked into the model. Microsoft introduced OLE DB for OLAP and OLE DB for Data Mining specifications. Folks that came to my class built OLE DB providers for just about everything, from machinery to databases to farming data beamed via satellites from GPS-enabled tractors. The reason for this was that OLE DB was THE way to hook into the Microsoft platform, if you had any kind of data. I came up with an estimate of over 70 providers (some very specialized) built by folks in my classes alone. The data access team (who estimated about 10 database vendors = 10 providers) was stunned that so many companies were building providers.

And, of course, every bit of its flagship database, SQL Server, embraced OLE DB. DTS (now SSIS) is COM-based, and the providers and consumers are OLE DB. As are SSAS and SSRS. And SQL Server approach to distributed query (aka linked servers) are based on OLE DB providers. The SQLCMD utility is OLE DB-based. Even distributed transactions are modeled with COM interfaces (MSDTC), and Microsoft Transaction Server (MTS). Microsoft's entry into the application server world, was based around declarative transactional COM objects.

So what happened? COM was replaced over time by runtime-based APIs; first Java and then (after the lawsuit) .NET. ADO.NET became the data access API and there were bridge providers, the OleDb and Odbc providers for the "older" APIs. The non-relational bits of OLE DB didn't make it into the .NET world and everything was either modeled as a .NET wrapper (e.g. ADOMD.NET for OLAP) or as XML. Interestingly, there was once an OLE DB provider for XML, as well as everything else. The BI components also supported .NET providers. I haven't personally coded OLE DB in earnest in years…

So, looking to consolidate on one "native" (i.e. non-runtime-based) API, post-COM, ODBC makes perfect sense. It's part of the ANSI relational database standard as well as being more cross-platform. (OLE DB couldn't be cross-platform because COM didn't catch on outside of Windows OSes). There has always been slgihtly more (at the hayday of OLE DB) to many more (pre-and-post-COM) ODBC drivers than OLE DB providers. Even when Microsoft didn't orginally port ODBC and MSDASQL to 64-bit (and that didn't last long).

But my big question at this point is what's going to become of SQL Server, that is firmly integrated in OLE DB in all bits, not just the database engine? What replaces OLE DB for OLAP, Reporting Services, SSIS, linked servers, and all the places where it was integrated from SQL Server 7 to present? And what's to become of the folks who used OLE DB (e.g. Office) as the SQL Server integration point? And the customers who embraced OLE DB and COM; do they trudge back to ODBC, to ADO.NET or ??? I guess we'll see in the first post-Denali SQL Server release and in 7 years time. And so, I bid OLE DB adieu, soon…although 7 years IS a long time.

So what's next on the deprecation list…ActiveX components?

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.