In-Depth

SQL Server 2012 Marks the End of an Era

Microsoft bids "adios" to OLE-DB.

• By Stephen Swoyer
• 03/27/2012

Microsoft's latest and greatest version of SQL Server -- SQL Server 2012 (aka "Denali") -- ships with the requisite assortment of enhancements, including an improved and extended PowerPivot facility; a new visualization component, dubbed "PowerView;" and (at last!) SQL Server Data Quality Services, among others.

For many data management professionals, however, SQL Server 2012 will mark the end of an era. To wit: it's the last release of Microsoft's flagship database platform that will support the venerable OLE-DB API. Microsoft plans to deprecate OLE-DB in favor of ODBC.

"The commercial release of Microsoft SQL Server, codename 'Denali,' will be the last release to support OLE DB," confirmed Rohan Lam, program manager for SQL Server Connectivity, in a posting last August on Microsoft's SQLNCLi team blog. "[E]xisting … OLE DB [applications] … will continue to be supported on Denali throughout its lifecycle," Lam continued, arguing that Microsoft is giving customers a "large window of opportunity" to port any existing OLE-DB-based applications over to ODBC. "[Y]ou may want to consider migrating those applications to ODBC as a part of your future roadmap," he suggested.

The irony, of course, is that Microsoft once proposed OLE-DB as an enhanced alternative to the more limited ODBC standard. (True, ODBC was a Microsoft-spearheaded effort. By the mid-1990s, however, the API on which ODBC is based -- which Microsoft did not develop -- had been incorporated into the SQL standard.) In the late-1990s, in fact, Microsoft positioned the then-new OLE-DB API as its preferred alternative to ODBC. At its 1998 Professional Developers Conference (PDC), in fact, Microsoft went so far as to indicate that it would no longer be actively developing its ODBC driver; instead, the company said, its focus would shift to OLE-DB.

It's a measure of how much things have changed.

Fifteen years ago, for example, critics routinely charged Microsoft with hijacking standards: Redmond's proprietary "extensions" to Java prompted the former Sun Microsystems Inc. to file suit in late 1997, charging that Microsoft's incomplete implementation of the Java 1.1 standard subverted the very premise (i.e., cross-platform consistency and compatibility) of Java itself.

OLE-DB vs. ODBC wasn't ever a case of "embrace, extend, and extinguish," of course, but it's nonetheless an ironic development. After all, a decade and a half ago, who could have predicted that the software giant would at some point voluntarily relinquish a popular, all-but-proprietary standard in favor of a less-feature-rich open one?

That's the rub: OLE-DB isn't just a popular API, it's a feature-rich and data-source-agnostic one, too. Whereas ODBC is a procedural interface that traditionally has been used to get at relational or structured data, OLE-DB is a component-based implementation that can be (and has been) used to get at all kinds of data.

For at least half a decade, Microsoft explicitly encouraged SQL Server and other developers to use OLE DB -- and not ODBC -- to connect to relational and other data sources in Win32 contexts, so there's a lot of OLE-DB code out there. That's one reason Microsoft in October introduced a tool designed to scan software code and detect OLE-DB dependencies. (Redmond plans to offer the tool until April of this year.)

OLE-DB is perhaps best known as an enabling technology. Consider the OLAP engine that Microsoft has been shipping with SQL Server since 1998. Before it was called SQL Server Analysis Services (SSAS), it was called "SQL Server OLAP Services," and before that it was known by its code name: "Plato."

Plato, of course, was based on Microsoft's OLE-DB for OLAP technology, or ODBO. Nowadays, ODBO is the de facto standard for getting at multi-dimensional data on Windows platforms. The good news is that Microsoft's deprecation of OLE-DB in favor of ODBC won't affect ODBO. As Lam stressed in his August, 2011 posting, it "applies to the Microsoft SQL Server OLE DB provider only. Other OLE DB providers as well as the OLE DB standard will continue to be supported until explicitly announced."

Nevertheless, ODBO -- like OLE-DB -- is a Microsoft-centric technology. On Windows platforms, it's arguably superior -- in terms of features or performance -- to alternatives such as XML for Analysis (XML/A). Yes, Microsoft did at least try to establish ODBO as an industry standard, and yes, ODBO is supported in non-Microsoft contexts -- e.g., it's still a prerequisite for using Excel Pivot Tables with OLAP data sources. However, it doesn't have the kind of vendor-agnostic industry backing that XML/A enjoys. In 2000, Microsoft officially threw its weight behind XML/A; Redmond, along with OLAP supporter Hyperion Solutions Corp. (now an Oracle Corp. company) and SAS Institute Inc. combined to create the XMLA Advisory Council, which today totals 25 member companies.

Is the writing on the wall for ODBO, too? Given a sufficiently long timeline, yes -- but what about sooner? That's not likely: Excel 2010, for example, still uses ODBO to support access from Pivot Tables to OLAP sources, and PowerPivot versions 2010 and 2012 likewise use ODBO.

What's more, a dizzying array of non-Microsoft vendors -- from analytic database appliance specialist Kognitio to open source software (OSS) BI player JasperSoft Inc. -- support ODBO precisely because of its Excel tie-in. Although plain-vanilla OLE DB likewise enjoys widespread support outside of the Microsoft world, it's uniformly perceived as anterior to (as deprecated in practice by) ODBC. Furthermore, it doesn't have a killer app -- such as Excel or PowerPivot -- to sustain or even buoy its popularity; that is to say that some technologies (OLE-DB) are more insular -- if not proprietary -- than others (such as ODBO).

As Lam made clear in his blog pos, Microsoft's decision to ditch OLE-DB has everything to do with its perceived insularity. ODBC hasn't just become the universal standard for accessing relational data; it has become the de facto means of getting at (relational) data in the cloud, too.

"Cloud is universal and in order to support all client applications connecting from any platform to the cloud, Microsoft has been fully aligned with ODBC on SQL Azure, as ODBC is the only set of APIs that are available on all platforms including non-Windows platforms," Lam wrote. "From our surveys, cross-platform support is one of the main reasons indicated by our partners for aligning their applications with ODBC. The other reason often mentioned in the surveys is the ease of programming with ODBC. "