sticky
Microsoft is Aligning with ODBC for Native Relational Data Access - FAQ

    常规讨论

  • For more information please see our blog post at: http://blogs.msdn.com/b/sqlnativeclient/archive/2011/08/29/microsoft-is-aligning-with-odbc-for-native-relational-data-access.aspx

     

    FAQ:

     

    Question1: Microsoft created OLE DB after ODBC, isn’t deprecating OLE DB a step going backwards?

    Answer: Microsoft has been supporting ODBC through all the releases of SQL Server for relational data access. This includes adding new functionality to the ODBC drivers whenever a corresponding feature is added to SQL Server. OLE DB was introduced primarily to provide uniform data access to non-relational data as well as relational data. But it is a Microsoft proprietary technology that worked only on Microsoft platforms. When it comes to uniform data access to SQL Server from different platforms, ODBC has always been a better choice and that was consistently quoted by all of our customers in various surveys, SDRs and forums. By fully aligning with ODBC, Microsoft will be focusing on one set of industry standard APIs that are widely used by many of our customers.

     

    Question2: Why is Microsoft making this change now? What are the advantages?

    Answer: More customers are adopting cloud these days. To enable client applications running on various different platforms to connect to cloud, Microsoft chose ODBC as the de-facto standard client connectivity API for native client applications connecting to SQL Azure. If you are writing applications that run against standalone SQL Server and SQL Azure, or if you are planning to port your application to SQL Azure, using ODBC APIs make the transitions pretty seamless. ODBC APIs use industry standard interfaces and are very simple and straight forward to use. Using multiple result sets, memory management and specifying required/optional properties in OLE DB is more complex. Overall, customers get the benefit of easy programming and cross-platform support with this alignment and can now build applications that can be uniformly ported between various platforms.

     

    Question3: How long will the current SQL Server OLE DB provider be supported?

    Answer: SQL Server OLE DB provider will be supported on Denali throughout its lifecycle. Support details for the release version of SQL Server “Denali” will be made available within 90 days of release to manufacturing. For more information on Microsoft Support Lifecycle Policies for Microsoft Business and Developer products, please see Microsoft Support Lifecycle Policy FAQ.

     

    Question4: What is being deprecated? What happens to other Microsoft products that use and depend on OLE DB?

    Answer: This announcement is about the deprecation of Microsoft SQL Server OLE DB provider only. Other Microsoft OLE DB providers, 3rdparty OLE DB providers and the OLE DB standard will be supported as per their respective terms. Other Microsoft SQL Server features and products that are built on top of OLE DB or use OLE DB, like Distributed Query (Linked Server), SQL Server Integration Services, SQL Server Analysis Services etc., will continue to be supported as per their respective terms. Microsoft will take care of replacing the underlying dependency. Providers like ADO.Net which can run on top of OLE DB will not support OLE DB once the latter is deprecated. At that time the clients using the underlying provider need to update their application to use a different provider. For more information, please see,  http://blogs.msdn.com/b/adonet/archive/2011/09/13/microsoft-sql-server-oledb-provider-deprecation-announcement.aspx 

     

    Question5: Are there any features in OLE DB that are missing in ODBC? How about performance?

    Answer: Most of the features related to relational data access in SQL Server are already available in ODBC. The specific implementation and usage may be different between these providers and if there are features that have better implementation in the current OLE DB, Microsoft will port such functionality to ODBC based on customer demand and feedback. In most of the performance comparison tests we have done, ODBC seem to perform better. We will publish a more detailed performance analysis document in the coming months.

     

    Question6: If I have an OLE DB application that I write for Denali, will it be supported on a post Denali version of SQL Server that is released during the life of Denali?
    Answer: No, in fact we may explicitly block the OLE DB applications on post-Denali versions of SQL Server. It is recommended that you plan your migration soon to ODBC, if you want to start using newer versions of SQL Server as soon as they release.

     


    Rohan Lam - MSFT



    2011年8月30日 1:46
    版主

全部回复

  • Hi Amina,

     

    You mentioned the following in your post (sorry, you've locked the post so I can't comment directly):

     

    Question4: What is being deprecated? What happens to other Microsoft products that use and depend on OLE DB?

    Answer: This announcement is about the deprecation of Microsoft SQL Server OLE DB provider only. Other Microsoft OLE DB providers, 3<sup>rd</sup> party OLE DB providers and the OLE DB standard will be supported as per their respective terms. Other Microsoft SQL Server features and products that are built on top of OLE DB or use OLE DB, like Distributed Query (Linked Server), SQL Server Integration Services, SQL Server Analysis Services etc., will continue to be supported as per their respective terms.Microsoft will take care of replacing the underlying dependency. Providers like ADO.Net which can run on top of OLE DB will not support OLE DB once the latter is deprecated. At that time the clients using the underlying provider need to update their application to use a different provider.

     

     

    Can you please ask the SSIS, SSAS, SSRS, etc. teams to clarify their respective terms for support of OLE DB.

     

    Thanks

    Michael

     

    2011年8月31日 3:08
  • Hi Michael,

    OLE DB Provider for SQL Server is still supported on Denali for the following 7 years. Other OLE DB providers (for instance, Microsoft SQL Server Native Client OLE DB Provider,Microsoft OLE DB Provider for Jet ) will continue to be supported for these features such as SSIS, SSAS, SSRS and so on.  You may consider to migrating those applications to ODBC as a part of your future roadmap.

    For more information:

    OLE DB Providers Tested with SQL Server
    Microsoft is Aligning with ODBC for Native Relational Data Access 

     

     


    Best Regards,
    Stephanie Lv

    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    2011年9月1日 5:35
  • Please post your questions and comments here.

    Rohan Lam - MSFT

    2011年9月2日 20:54
    所有者:
  • Let me summarize what I understand from the OLE DB deprecation announcement as I understand it.  Please confirm or refute.

    Denali will be the last SQL Server version that will ship a new OLE DB provider (SQLNCLI11.1) as part of SQL Server Native Client.  The OLE DB driver for SQL Server that ships with Windows/MDAC (SQLOLEDB) has been already deprecated for some time so we shouldn't be using that anyway.

    Since no new SQL Server OLE DB drivers will be available after Denali, new applications should instead use ODBC or SqlClient going forward.  Plans should be made to migrate current applications that use OLE DB for SQL Server data access to ODBC or SqlClient within 7 years after Denali is released.

    SSIS, SSAS and SSRS are basically like other applications from a data access perspective.  So we should similarly use ODBC for those features too.  But doing so before Denali will be a challenge in since there is some gap between OLE DB and ODBC in the current SQL Server versions.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    2011年9月3日 17:56
    版主
  • Thanks for the attempt Stephanie. Obviously you can't replace SNAC OLE DB connectivity from SSAS to SQL Server with ODBC until SSAS to SQL Server connectivity via ODBC becomes supported. No one seems ready to provide any specific guidance around this ("We will support SSAS to SQL Server ODBC in the next n years"). Until Microsoft provides specific guidance I will advise clients planning their future roadmaps to read the previously mentioned blog entries and contact Microsoft directly with specific questions. Thanks Michael
    2011年9月3日 23:10
  • Thanks Dan, Unfortunately planning for migration and actually migrating some applications to ODBC will still be difficult for an unspecified time period after the Denali release, until the specifics for ODBC support are made public. Thanks Michael
    2011年9月3日 23:15
  • Yes, Mike, I agree it's hard to make firm plans to migrate to other data access technologies until the path is clear.  At least we now know that we shouldn't develop new applications that use OLE DB for SQL Server data access an alternative method exists.  That will at least avoid the need to migrate later. 

    Migration from OLE DB to the SQL Server Native Client ODBC driver should be easy for existing ADODB apps that use OLE DB.  A simple connection string change ought to do the trick, albiet with the slight overhead of going through the Microsoft OLE DB Provider for ODBC drivers.

    The think the real pain will be in converting C++ apps that use the COM OLE DB interfaces directly to the ODBC call-level interfaces.  7 years doesn't seem long enough for that :-)

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    2011年9月4日 1:52
    版主
  • Hi Dan, I agree that custom application conversion should be the simplest--albeit the most extensive--part of this process. Even folks who have used the native C++ COM OLE DB interfaces shouldn't have too much trouble if they've designed and built their apps properly. This will make a very good test of whether an app was designed using proper OOP methodologies or not :) The issue I'm running into now is a flurry of questions regarding Microsoft apps like SSAS, SSIS, etc., and the timelines for ODBC to become a first-class citizen in that space. I can't answer those questions, and no answer seems to be forthcoming in the immediate future. If ODBC were not a first-class citizen for SSAS until Denali+n years (as an example), that leaves about (7-n) years to get that portion of the conversion done. As n approaches 7 we have to deal with compressed timelines. Without specific guidance I can't even prioritize. For now I'm just directing clients to the blog post. Thanks Michael
    2011年9月4日 14:17
  • Like Mike C_1 above, I am eager to know more about the statement

    Other Microsoft SQL Server features and products that are built on top of OLE DB or use OLE DB, like Distributed Query (Linked Server), SQL Server Integration Services, SQL Server Analysis Services etc., will continue to be supported as per their respective terms.

    While I would appreciate details on SSAS/SSIS/SSRS, at this point I am particularly concerned about the Linked Server technology - do you plan to remove support for third-party OLE DB providers in Linked Servers?

    Thanks,
    Steve

    2011年9月6日 20:05
  • You are right overall.

    For the SSIS, SSAS, SSRS support, if you are using SQL Server Native Client (SNAC) OLEDB provider, you need to plan your migration to ODBC, if you are using any other OLEDB provider, it is not impacted by this announcement.

    For the SNAC OLEDB provider, we are actively investigating feature gaps as well as potential perf issues and our goal is to address them in ODBC well before stop support for OLEDB. And if you find any of those issues, we request you to forward them to us so we can add them to our list.

    Thanks,

    Rohan

    2011年9月6日 21:02
    所有者:
  • Hi Rohan, We seem to be talking right past each other here and my questions are still not answered :( We already know SSIS is going to take an ODBC performance hit because it leans heavily on COM. I'm not 100% convinced ODBC will support all the functionality exposed by OLE DB (granted I have to do some research when I get the time, but it'd carry a lot more weight if it came from Microsoft). SSAS doesn't support ODBC at all. How do you performance test that? The question is simple: when are these tools going to be rearchitected to make ODBC a first-class citizen? At that point we'll be able to do some actual useful testing. An answer like "sometime in the next seven years" doesn't help anyone with their planning. Thanks Michael
    2011年9月6日 21:54
  • Hi Rohan,

    I agree with Mike C_1's comments - this quite a concerning announcement.  All my clients have built up successful Microsoft SQL Server BI applications over several years which rely heavily on OLE DB.  None of them have any plans to move these applications to the cloud that I am aware of - and that seems really, really unlikely to me.

    The SSAS situation seems almost farcical - deprecating the only connection option for a major component within the same product?

    I don't think there is a major impact for SSRS - almost everyone would be using the Microsoft SQL Server Data Processing Extension to access SQL Server.

    On SSIS my specific concerns are:

    SSIS currently has limited support for ODBC. I haven't heard of any changes to this in Denali. For details you can ref MSFTs own whitepaper on the subject:

    http://msdn.microsoft.com/en-us/library/dd299429(SQL.100).aspx

    OLE DB is cited over ODBC by a factor of 9:1, and ODBC support is indirect and limited at best, and essentially discouraged.  

    The prospect of changing connectivity fills me with dread, as SSIS is notoriously fussy on datatypes etc.

    More importantly, several commonly used SSIS Data Flow Transformations (e.g. Lookup, OLE DB Command, Slowly Changing Dimension) currently only support OLE DB connections. So this would be not just a connectivity change but would be a "breaking change" for 99% of my clients' existing SSIS packages. 

    These issues wont be fixed in Denali, so by the time the next release of SQL (after Denali) is out, is stable and is being deployed by enterprise-scale sites, most of the 7 years will have gone.

    I'd like to suggest that the SQL Server team should postpone deprecation of OLE DB, at least until ODBC is fully supported (and proven to perform) within those two major SQL Server product components (SSIS and SSAS).

    Thanks - Mike

    2011年9月13日 1:34
  • Hi Rohan,

    I agree with Mike C_1's comments - this quite a concerning announcement.  All my clients have built up successful Microsoft SQL Server BI applications over several years which rely heavily on OLE DB.  None of them have any plans to move these applications to the cloud that I am aware of - and that seems really, really unlikely to me.

    The SSAS situation seems almost farcical - deprecating the only connection option for a major component within the same product?

    I don't think there is a major impact for SSRS - almost everyone would be using the Microsoft SQL Server Data Processing Extension to access SQL Server.

    On SSIS my specific concerns are:

    SSIS currently has limited support for ODBC. I haven't heard of any changes to this in Denali. For details you can ref MSFTs own whitepaper on the subject:

    http://msdn.microsoft.com/en-us/library/dd299429(SQL.100).aspx

    OLE DB is cited over ODBC by a factor of 9:1, and ODBC support is indirect and limited at best, and essentially discouraged.  

    The prospect of changing connectivity fills me with dread, as SSIS is notoriously fussy on datatypes etc.

    More importantly, several commonly used SSIS Data Flow Transformations (e.g. Lookup, OLE DB Command, Slowly Changing Dimension) currently only support OLE DB connections. So this would be not just a connectivity change but would be a "breaking change" for 99% of my clients' existing SSIS packages. 

    These issues wont be fixed in Denali, so by the time the next release of SQL (after Denali) is out, is stable and is being deployed by enterprise-scale sites, most of the 7 years will have gone.

    I'd like to suggest that the SQL Server team should postpone deprecation of OLE DB, at least until ODBC is fully supported (and proven to perform) within those two major SQL Server product components (SSIS and SSAS).

    Thanks - Mike


    this!!!!   Are you trying to get everyone to switch to Informatica/Cognos who are stable with this stuff? 
    The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to me and deleting it from your computer. Thank you
    • 已编辑 sbogar 2011年9月13日 13:38
    2011年9月13日 13:37
  • We will support the new version of OLE DB (to be released with Denali) with the other SQL Server components (like SSIS, SSAS etc) throughout the life of Denali. I will have more details on the specific timeline questions when the support details for Denali are posted. Support details for the release version of SQL Server “Denali” will be made available within 90 days of release to manufacturing.

    Thanks,

    Rohan - MSFT

    2011年9月14日 19:41
    所有者:
  • @Microsoft SQL Server team: please see my question above... I really need to know what's going to happen with support for third-party OLE DB providers in the Linked Server technology.

     

    Thanks in advance,

    Steve

    2011年9月16日 6:53
  • I hope someone in the SQL Server group will remember to tell the MS Office dev. teams of this change. From my perspective (focus on Word MailMerge), yet another thing that makes Word data access/mailmerge work badly is not good news: at present

     a. Word basically defaults to OLE DB providers wherever possible

     b. Word ODBC connectivity is substandard - for example, Word does not retrieve Unicode text data when it connects using ODBC. Also, Word used to be able to retrieve data from multi-statement Transact-SQL procedures, but not since around Word 2002.

     c. Word does not allow DSN-less connections, which makes distribution of MailMerge applications with ODBC connections unnecessarily difficult

     d. the "Office Data Source Object" is OLE DB-oriented, badly coded and has a number of deficiencies when it comes to accessing data via ODBC.

    Not everyone uses .NET+ADO.NET to do data-oriented stuff in Office. They might, of course, if it was actually easy to do.

     


    Peter Jamieson
    2011年9月21日 22:53
  • I'm using ADO and SNAC for some code, since the only way to do an asynchronous query of Sql Server from within VBA is to use (legacy) ADO.  As fas as  I know, that requires an OLE DB connection (to the SNAC oledb provider--see below), and that is what I'm using in the connection string.  This is not an issue if (1) ADO is upgraded to support snac odbc, or (2) another way to do an asynchronous query of sql server from vba is provided (e.g. via DAO).    

    This is the connection string I use for ADO to sql server (denali):
    "Provider=SQLNCLI11; DataTypeCompatibility=80; Database=MySqlServerDbsName; Server=(local); MARS Connection=True; Integrated Security=SSPI;"
    What should it be replaced with for compatibility with post-denali releases?

    Should I use the slower MSDASQL (oledb provider for ODBC)? (I thought MSDASQL had been deprecated? Moreover MS says "SQL Server Native Client is not supported from the Microsoft OLE DB provider for ODBC (MSDASQL). If you are using the MDAC SQLODBC driver with MSDASQL or MDAC SQLODBC driver with ADO, use OLE DB in SQL Server Native Client"):
    "Provider=MSDASQL; Driver={SQL Server Native Client 11.0}; Server=(local); Database=MySqlServerDbsName; Trusted_Connection=yes; MARS_Connection=yes;"

    http://msdn.microsoft.com/en-us/library/ms131035(v=sql.110).aspx

    2011年9月28日 21:34
  • There should not be any impact on third party OLE DB providers. Thanks.

    2011年10月4日 20:16
    所有者:
  • I think this is a retrograde step. There are a plethora of applications out there especially in the financial world that use OLEDB queries to join across multiple datasources. This may not be apparent to the guys in Redmond who may not see how their products are being used in the great financial centres of London, New York, Singapore, Hong Kong and Tokyo.

    The ability to perform heterogenous joins is a major asset in system integration.

    I think Microsoft have made a big mistake here: Big development shops are either Microsoft based (in which they use .Net with SqlClient and/or OleDb for connectivity) or they're java/linux/other DB based in which case they use JDBC. Nobody from the Java world is going to jump ship to use ODBC. If Microsoft wants a slice of the open-source client pie, it should invest properly in the JDBC driver.

    What we in the corporate world want is a clear migration path over time without having to throw the baby out because Microsoft thinks it can make a few bucks more by reverting to an ancient technology.

    This decision is on a par with that other stupid decision to end support for the Itanium platform. Like that decision, this decision will leave large corporations high-and-dry with stacks of legacy code (rather than a huge hardware investment down the pan) holding back the upgrades of SQL Server.

    Redmond guys: That's what big corporations do - they don't spend money re-engineering code so they can upgrade the server - they don't upgrade the server at all. And they'll keep it on old versions of SQL Server for years and years depriving you of your upgrade revenue. A real smart decision... What would be a far smarter decision is to re-evaluate what would occur if 50% of your SQL Server licencees failed to upgrade. Then work out what it will cost you to maintain and further develop OleDb.


    Ian Posner
    2011年10月10日 22:03
  • Hello Rohan Lann,

    Please, could you explain what you mean when you are writing " Support details for the release version of SQL Server “Denali” will be made available within 90 days of release to manufacturing" ?

    release to manufacturing ==> does it mean the date that the RTM  version is considered as finished , so the production of the dvd used to install SQL Server 2012 will begin to permit to make them available when the 2012 version will be available as downloads or as sellers ?


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    2012年1月3日 13:10
    版主
  • Keep in mind, this isn't Microsoft that's saying this, it's only the MS SQL team, and they clearly have not looked beyond their silo at the impacts of this decision, or even within it very well.

    There is still plenty of time to turn this decision around.

    Keep pointing out all of the problems with this approach to your Microsoft reps.  If you don't work directly with a Microsoft rep but someone in your company does, be sure to educate them on the impact this decision will have on your business -- the amount of rework necessary, the performance hits, the lack of support for ODBC in many MS products, etc..

    If Microsoft SQL doesn't support other Microsoft applications and development tools and we have to rewrite a decade of legacy code, my shop might as well switch to Oracle.

    2012年2月3日 15:51
  • Hi,

    I am new to Sql server native client programming. I am developing a module in C++ to fetch data from SQL server using SQL Native client.

    Can you please guide me regarding good resources or tutorials on SQL server native client with ODBC provider? If we use SNAC with ODBC, do we still need to create DSNs using the ODBC manager? or can we connect to the database directly?

    Please guide.

    Many thanks in advance for your time and help.

    Best Regards,

    ganesh

    2013年2月7日 1:40
  • Hi,

    I'm confused about migrating linked servers from OLEDB to ODBC.  We are also trying to support multisubnet availability groups in SQL 2012.  So if I'm referencing the VNN, and my current connection is this:

    EXEC master.dbo.sp_addlinkedserver @server = N'myLinkedServer', @srvproduct=N'myAGListener', @provider=N'SQLNCLI', @datasrc=N'myAGListener', @catalog=N'myDB'

    Then what should it be instead?  SQLCLI11 doesn't support MultiSubnetGailover = True, but MSDASQL is slower, and according to Microsoft as TechvsLife2 pointed out, is also being deprecated.  It seems like whichever way I go, we will have a problem.

    Regards,

    Diane Sithoo


    Diane


    2013年3月14日 17:01

  • Question6: If I have an OLE DB application that I write for Denali, will it be supported on a post Denali version of SQL Server that is released during the life of Denali?
    Answer: No, in fact we may explicitly block the OLE DB applications on post-Denali versions of SQL Server. It is recommended that you plan your migration soon to ODBC, if you want to start using newer versions of SQL Server as soon as they release.

     


    Rohan Lam - MSFT



    Microsoft is so myopic, OLE DB is native, by your own definition. Embracing ODBC for cross platform development is perfectly logical, but there are millions of reasons for ETL processes between SQL servers. To deprecate and block the native connection type between two SQL servers is lunacy. Please reconsider.

    2013年11月8日 15:15
  • Agree - an appalling decision. This is cloud cuckoo land thinking: MS please engage with the real world where we have enough to focus on without having to revisit, re-understand and rework all our legacy applications. 
    2013年11月14日 4:18
  • Microsoft is so whimsical. That is reason our company is moving away from it's technologies.
    2014年2月5日 19:49
  • Question5: Are there any features in OLE DB that are missing in ODBC? How about performance?

    Answer: Most of the features related to relational data access in SQL Server are already available in ODBC. The specific implementation and usage may be different between these providers and if there are features that have better implementation in the current OLE DB, Microsoft will port such functionality to ODBC based on customer demand and feedback. In most of the performance comparison tests we have done, ODBC seem to perform better. We will publish a more detailed performance analysis document in the coming months.

    We're coming up on the third anniversary of this post and I've yet to see the detailed performance analysis document... While I understand this is no longer an arguable point for SQL 2014 (since OLE DB is now deprecated), was there ever anything released for SQL 2012 and earlier versions?  If there was, I'm sure not finding it.


    John Eisbrener - http://dbaeyes.com/

    2014年6月28日 3:30
  • I stopped worrying about it. This post was from when MS probably thought SQL Azure was going to render local installations of SQL Server completely obsolete within the first 180 days. In the meantime SSAS and SSIS connectivity support hasn't really changed all that much, and eliminating OLE DB support would instantaneously mean tens of thousands of customers (including several Fortune 500 companies) simultaneously hit a brick wall on their Microsoft upgrade path.

    Take OLE DB support out of SQL 2014 and a dozen of the world's largest banks start looking for a replacement DBMS tomorrow.

    I suspect this was just an announcement of a very stupid decision made by someone who didn't really think through the implications of how much money it would cost Microsoft when customers started jumping ship because of it.

    2014年6月28日 16:56
  • When will applications built with the SQL Server Native Client OLE DB provider not be able to connect to SQL Server? When will applications built with the SQL Server Native Client OLE DB provider not be supported?

    Where is the document mentioned in answer to question #5 “We will publish a more detailed performance analysis document in the coming months.”?

    2014年7月30日 13:41