Rohan Lam, the Program Manager for SQL Server Connectivity, has officially stated that the next version of SQL Server,Denali, will be the last that will support OLE DB. The SQL Server OLE DB provider will then be deprecated in favour of SQL Server Native Client ODBC. In seven years’ time OLE DB will be a dead, unsupported technology for SQL Server. What does one read into this remarkable handbrake-turn? Microsoft still publishes White Papers exhorting us to abandon ODBC in favour of OLE DB. Is it is now time to abandon OLE DB?
The announcement only says that the SQL Server OLE DB provider will be with the angels, not the entire technology. However, it doesn’t say much for the health of OLE DB. SQL Server has always been OLE DB’s bastion. All its data connectivity is based on OLE DB technology: it is used throughout as the glue for all the components such as SSIS and SSRS. Even SQLCMD uses it! Denali's successor will have to have all this plumbing ripped out to replace it with the older ODBC technology. From the data access viewpoint, it might seem like a 'Steam Punk' version of SQL Server. Presumably, the conversion of any app that currently communicates with these components via OLE DB will be dependent on the timelines for ODBC to take over.
It is obvious that the move for SQL Server makes sense. Everyone can use the ODBC (Open Database Connectivity) for relational data access. Whether you're using Windows, .NET, Linux, OSX or the cloud, there is a single standard. Even SQL Azure uses it. If you’re on a 64-bit system, then yes, there is likely to be an ODBC driver. If you have an ODBC interface to your data in your application then you should be able to switch databases pretty easily (well, that depends on the detail). ODBC is an open standard which developed from SQL's ANSI standard for the CLI (Call-Level Interface), and the interfaces are well-enough known. Nobody wants to have to develop more than one set of APIs for all their native client applications, even if developing for the Cloud, so using ODBC seems a pretty good choice, especially as it is one of the faster routes to your data.
A rationalisation of Microsoft's road map for Data Access Technologies always looked more like a half-eaten bowl of alphabet soup with added spaghetti dropped on the floor. You have an acronym wonderland in there, with MDAC, WDAC, ADO, ADOMD, ADOX, OLE DB, SQLOLEDB, SqlClient, SQLODBC, MSDASQL, MDADS, RDS, JRO, MSDAOSP and so on. OLE DB looks more and more like one that Microsoft should abandon.
OLE DB had nothing to do with OLE, of course. It was developed in the late nineties when COM was Microsoft’s grand 'vision'. It had ambitions to connect with any data, not just relational databases. With it, we were told, you would be able to connect to "file systems, message stores, directory services, workflow, and document store". Whatever the source of data, everyone used to tell you that OLE DB was the best way to hook into the Microsoft platform. However, COM never caught on outside Microsoft and so OLE DB couldn't either. It didn’t offer anything more than ODBC. It was slower. Why bother with it?
So what's to do? Surprisingly little for most of us, in fact, beyond resisting the urge to write OLE DB drivers for a living. I'd advise choosing ODBC or SqlClient for your application's underlying data provider rather than OLE DB for SQL Server, but surely you’re doing that anyway if there is any chance of your application becoming Azure-based.
Phil Factor (guest editor)
