OLEDB, ODBC or JDBC?

[Former Member]

Given a choice of OLEDB, ODBC or JDBC for use in our BI 4.1 environment we are implementing, do you have a particular recommendation for which is better?  Traditionally (in our XI 3.1 system), we've used ODBC, but having to maintain the ODBC connections on all the connection servers is not easy, so we were thinking of moving to OLEDB for BI 4.1.  But now that OLEDB is not going to be available going forward, we're rethinking that decision.

My environment:  BI 4.1 SP6 installed on a clustered Windows Server 2012 R2 environment, and 99% of all databases that it will connect to are SQL Server 2012.  Migration to SQL Server 2016 is planned for December 2016/January 2017.  We have not yet migrated from our current XI 3.1 system, so I have some time to "make the right choice".

[Former Member]

SQL Server 2012 will be the last version of SQL Server to use OLEDB. As such, ODBC seems the better option. Microsoft have announced the deprecation of the Native Client OLE DB provider and released a document on it:

https://msdn.microsoft.com/en-us/library/hh967418.aspx

[former_member193452]

Mark is correct and this will be deprecated after 2012.  SAP will no longer offer the support of Oledb after 2012.

SAP BusinessObjects will continue to provide the OLE DB connection for reporting connections for the 2008 and 2012 Sql Server platforms.  The SAP BusinessObjects Product Availability Matrix lists this connection support as "Compatible."  There will be no future developments on this connection option.

Please see kb 2043153 - Microsoft Deprecating OLE DB Connectivity To Sql Server

Jacqueline

[former_member285534]

Hi Adrian,

OLE DB allows access to information from a wide range of databases. It is a more generalized and more efficient strategy for data access than ODBC because it is based on the Component Object Model (COM).  It is also newer, more advanced and designed to be compatible with a wider selection of DB technologies.  It encompasses ODBC functionality.

I think this link will be helpful.  It discusses extensively the pros and cons of each technology.

OLE DB or ODBC? | SQL Server content from SQL Server Pro

Best Regards

Fernando