Problem solve Get help with specific problems with your technologies, process and projects.

ODBC drivers replacing OLE DB? Say what?

After years of telling SQL Server users to avoid ODBC in favor of OLE DB, Microsoft has suddenly reversed its position and advised going back to ODBC. Why? And what should you do about it?

Recently, Microsoft made an announcement that sounded like a cruel joke. After years of telling users that Open Database Connectivity (ODBC) was an old technology that should no longer be used and would be phased out in favor of Object Linking and Embedding Database (OLE DB), they declared ODBC the new king of data access and deprecated OLE DB.

For many years, hundreds of articles, white papers and blogs have told us to stay away from ODBC and instead use native providers or OLE DB. Despite that, ODBC never really went away. It was used mainly in older, “legacy” applications, as well as for accessing proprietary database systems from Windows applications. These ODBC drivers were usually developed by the same company that developed the particular database system or by a company that specialized in writing data connectivity components. Since Microsoft had been discouraging developers from using ODBC for years, it should come as no surprise that this announcement left many people puzzled.

For more on ODBC and OLE DB

How do you use OLE DB, SQLXML, and ODBC member classes?

Troubleshooting your ODBC IP connection

Get the guidelines for managing data updates to optimize ODBC performance

The explanation is pretty clear -- ODBC has survived and prevailed mainly because there is a strong need to access database systems from multiple platforms. OLE DB worked only in the Windows world and an OLE DB provider was usually a Microsoft product. In the last few years, the need for cross-platform and cross-database communication greatly increased. There are lots of new database products, many of them based on NoSQL technologies. It became clear that these heterogeneous database systems on multiple platforms such as Windows, Linux and Unix need to communicate with each other to satisfy the needs of business users. Structured data systems based on relational databases often need to connect to unstructured data stores like Hadoop or Cassandra to update or extract data, so the hottest current trend is connectivity and interoperability. It’s in Microsoft’s best interest to make sure SQL Server can be accessed from a variety of programming environments and operating systems. They eventually realized, based on surveys and customer feedback, that ODBC is the most established cross-platform data access technology. 

ODBC has survived and prevailed mainly because there is a strong need to access database systems from multiple platforms. OLE DB worked only in the Windows world and an OLE DB provider was usually a Microsoft product.

--Roman Rehak

Ready your applications for ODBC
How will this affect your existing applications? First, there is no reason to panic. OLE DB for SQL Server will continue to be supported for several years. Mainstream support for SQL Server 2012 will last until July 2017. This gives you plenty of time to modify your applications as needed. The main thing to understand is that in most applications the change will be as simple as modifying the connections used. In applications like SQL Server Integration Services (SSIS) or Reporting Services you will just need to change the connection type in the user interface from OLE DB to ODBC. For example, to modify the connection type in an SSIS package, create a new ODBC connection using the ODBC Connection Managers tool and replace any existing connections you created using the OLE DB Connection Managers.

In Reporting Services, you’ll need to change your data sources to use ODBC connection string type. A little more work will be required in custom .NET applications. If you were using the System.Data.SqlClient assemblies in ADO.NET, you probably won’t have to do anything because Microsoft will just replace the underlying code in this native provider. If you were using the System.Data.OleDb classes, you’ll have to replace them with whatever Microsoft ends up recommending in their new guidelines (likely System.Data.SqlClient with ODBC under the hood).

I doubt that many people were using System.Data.OleDb classes, however, since Microsoft has encouraged the use of native providers. It is also unlikely there’s a lot of existing code using System.Data.Odbc classes against a SQL Server database, because this data access method was discouraged. If you were using this, you won’t have to modify the code at all. The biggest change will be modifying C/C++ applications that were coded to use SQL Server Native Client OLE DB API to access SQL Server. These applications will need to be rewritten to use the ODBC application programming interface. Microsoft has released a white paper to assist migration efforts.

As a result of this data access shift, Microsoft’s SQL Server team has a big job to do. Many components of SQL Server use OLE DB, including linked servers, distributed queries, SQLCMD utility or OLE DB for OLAP. They will now need to re-architect these components and figure out how to make them work either with ODBC or some other way of communicating at the SQL Server level.



Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He contributes to Visual Studio Magazine, SQL Server Magazine and other publications and presents at user groups and conferences in the U.S. and Canada.

Dig Deeper on Microsoft SQL Server Tools and Utilities