Native Data Access for SQL Server 2005 FAQ

Native Data Access for SQL Server 2005 FAQ

In SQL Server 2005 Microsoft decided to change the way it supports ODBC and OLE DB with the introduction of Microsoft SQL Server Native Client (SQL Native Client), a new component of SQL Server 2005. This FAQ answers some basic questions about SQL Native Client and about ODBC and OLE DB in general.

What is SQL Native Client?

Microsoft SQL Server Native Client (SQL Native Client) is a single native dynamic link library (DLL) that contains the ODBC driver and OLE DB provider which together support applications using native-code APIs (ODBC, OLE DB and ADO) to access Microsoft SQL Server. SQL Native Client is a component of SQL Server 2005 and is serviced in SQL Server 2005 service packs. It is also available for download from MSDN (https://msdn.microsoft.com/data/ref/sqlnative/default.aspx). In SQL Native Client, everything (APIs, TDS routines and netlibs) is packaged into a single DLL.

Previously ODBC and OLE DB support for SQL Server was provided via Microsoft Data Access Components (MDAC - https://msdn.microsoft.com/data/ref/mdac/). MDAC is now fully integrated into Windows and is available only in Windows releases and service packs – there will be no more standalone MDAC downloads on MSDN. MDAC continues to provide core data access functionality such as the ODBC Driver Manager and OLE DB Core Services, but the ODBC and OLE DB support for SQL Server is frozen at the level of functionality provided by SQL Server 2000.

Note that SQL Native Client supports SQL Server releases from SQL Server 7.0 onwards, so its use is not restricted to SQL Server 2005. However, new features such as MARS (https://msdn2.microsoft.com/en-us/library/ms131686.aspx) are only available when connected to SQL Server 2005.

Where do I find out more about SQL Native Client?

You can find information about SQL Native Client in Books Online and also on MDSN (https://msdn.microsoft.com/data/learning/sqlnative/).

For anything you can’t find there you can use the SQL Server Data Access forum (https://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1)

Must I upgrade clients to use SQL Native Client as soon as I upgrade my server?

Applications deployed before SQL Server 2005 was released can and should continue to use MDAC.

Applications must upgrade to take full advantage of the new features and data types introduced in SQL Server 2005 (https://msdn2.microsoft.com/en-us/library/ms131456.aspx ). When an application is upgraded, it should be thoroughly tested since there are some differences in behavior between MDAC and SQL Native Client (https://msdn2.microsoft.com/en-us/library/ms131035.aspx ). The vast majority of applications will not be affected by these changes, but this does not remove the responsibility to test before deployment.

Should I rewrite my native API applications to use ADO.Net and managed code?

In most cases the old adage “If it isn’t broken, don’t fix it” applies. The .Net platform has many compelling attributes and will be the right choice for new development in most cases, but this does not mean that it is the right choice in all cases. There are valid reasons for making other choices in some case: Visual Studio 2005 and C++/CLI (https://msdn.microsoft.com/msdnmag/issues/04/05/VisualC2005/)makes re-use of existing native data access code within a .Net deployment infrastructure a more viable option than ever before; there are some scenarios where native code and native APIs provide the best solution; Microsoft now has its own high quality JDBC driver for SQL Server 2005(https://msdn.microsoft.com/data/ref/jdbc/default.aspx); also, Native XML Web Services in SQL Server 2005 (https://msdn2.microsoft.com/en-us/library/ms191274.aspx ) provide an additional means of exposing database-dependent services in service oriented architectures.

Isn’t ODBC is on the way out? If you have to use a native API isn’t OLE DB better?

ODBC and OLE DB are supported equally in SQL Native Client. Neither is better, they service different requirements and communities. There is no need to convert from one to the other unless there are other compelling business reasons. While there was a time when Microsoft expected OLE DB to supersede ODBC that time has passed and we now recognize that each API has its place.

OLE DB is a good choice for COM applications and has broader functionality than ODBC in some areas. It is used extensively within SQL Server itself. However, it is quite complex and more difficult to program for basic operations than ODBC.

ODBC may have been out of the limelight in recent years, but Microsoft anticipates strong and continued interest in this API going forward. ODBC has a huge following across the entire software industry and will therefore remain an essential component of SQL Server to meet requirements for standards alignment and interoperability. When migrating applications from other databases to SQL Server, where companies choose to re-use existing code and applications rather than make a fresh start and re-develop from scratch, ODBC will be the natural choice in most cases.

There is little difference in performance between OLE DB and ODBC – application architecture is likely to have a bigger impact on performance than choice of API for well-written applications that follow best practice guidelines.

Isn't ODBC just a ‘wrapper’ API and inherently inefficient?

This may be true for some ODBC drivers, but not for SQL Native Client. ODBC in SQL Native Client and also in MDAC is a true native API for SQL Server.

What about DB-Library and Embedded SQL for C(ESQL/C)?

DB-Library and ESQL/C components are not included in SQL Server 2005 but connections from existing applications are supported. Microsoft has no plans for any new development work involving these APIs. Customers may continue to deploy existing applications, but development of new applications and extension of existing applications is not recommended. Continued support for DB-Library and ESQL/C is not guaranteed in future releases of SQL Server (https://msdn2.microsoft.com/en-us/library/ms143729.aspx ).

Microsoft is reviewing support for DB-Library and ESQL/C in future releases of SQL Server and welcomes your input as part of the review process.

Chris Lee
Program Manager, SQL Native Client