SQL Server 2005 or 2008: SQL Native Client or MDAC - which is best one to use?
SQL Native client or MDAC - which is best one to use?
This was the question I was asked during my presence at DAT booth at the Tech-Ed ITPRO conference in Orlando. The sensible answer will be 'it depends'. Practically I have seen that there are a number of differences between SQL Server Native Client and MDAC. Although both provide native data access to SQL Server databases, SQL Server Native Client has been specifically designed to expose the new features of SQL Server 2005, while at the same time maintaining backward compatibility with earlier versions. By design the MDAC will have usage for OLE DB, ODBC & ADO and SQLNative client will have only OLE DB & ODBC, though the ADO functionality can be accessed easily. Microsoft's Technet documentation has given much of gist on the subject as follows:
SQL Server Native Client and MDAC differ in the other following areas:
- Users who use ADO to access a SQL Server Native Client provider may find less filtering functionality than when they accessed a SQL OLE DB provider.
- If an ADO application uses SQL Server Native Client and attempts to update a computed column, an error will be reported. With MDAC the update was accepted but ignored.
- SQL Server Native Client is a single self-contained dynamic link library (DLL) file. The publicly exposed interfaces have been kept to a minimum, both to ease distribution, as well as to limit security exposure.
- Only OLE DB and ODBC interfaces are supported.
- The SQL Server Native Client OLE DB provider and ODBC driver names are different from those used with MDAC.
- User-accessible functionality supplied by MDAC components is available when using SQL Server Native Client. This includes, but is not limited to, the following: connection pooling, ADO support, and client cursor support. When any of these features are used, SQL Server Native Client supplies only database connectivity. MDAC provides functionality such as tracing, management controls, and performance counters.
- Applications can use OLE DB core services with SQL Server Native Client, but if using the OLE DB cursor engine, they should use the data type compatibility option to avoid any potential problems that might arise because the cursor engine has no knowledge of the new SQL Server 2005 data types.
- SQL Server Native Client supports access to previous SQL Server databases starting with SQL Server 7.0 and newer versions.
- SQL Server Native Client does not contain XML integration. SQL Server Native Client supports SELECT … FOR XML queries, but does not support any other XML functionality. However, SQL Server Native Client does support the xml data type introduced in SQL Server 2005.
- SQL Server Native Client supports configuring client-side network libraries using only connection string attributes. If you need more complete network library configuration, you must use SQL Server Configuration Manager.
- SQL Server Native Client is not compatible with odbcbcp.dll. Applications that use both ODBC and bcp APIs must be rebuilt to link with sqlncli10.lib in order to use SQL Server Native Client.
- SQL Server Native Client is not supported from deprecated MDAC components, such as the Microsoft OLE DB provider for ODBC (MSDASQL). For more information about deprecated MDAC components, see the Data Access Technologies Roadmap.
- Minor changes have occurred to warnings and errors. Warnings and errors returned by the server now retain the same severity when passed to SQL Server Native Client. You should ensure you have thoroughly tested your application if you depend on trapping particular warnings and errors.
- SQL Server Native Client has stricter error checking than MDAC, which means that some applications that do not conform strictly to the ODBC and OLE DB specifications may behave differently. For example, the SQLOLEDB provider did not enforce the rule that parameter names must start with '@' for result parameters, but the SQL Server Native Client OLE DB provider does.
- SQL Server Native Client behaves differently from MDAC in regards to failed connections. For example, MDAC returns cached property values for a connection that has failed, whereas SQL Server Native Client reports an error to the calling application.
- SQL Server Native Client does not generate Visual Studio Analyzer events, but instead generates Windows tracing events.
- SQL Server Native Client cannot be used with perfmon. Perfmon is a Windows tool that can only be used with DSNs that use the MDAC SQLODBC driver included with Windows.
- When SQL Server Native Client is connected to SQL Server 2005, server error 16947 is returned as a SQL_ERROR. This error occurs when a positioned update or delete fails to update or delete a row. With SQL Server 2000 and earlier versions, and with MDAC when connecting to any version of SQL Server, server error 16947 is returned as a warning (SQL_SUCCESS_WITH_INFO).
- SQL Server Native Client implements the IDBDataSourceAdmin interface, which is an optional OLE DB interface that was not previously implemented, but only the CreateDataSource method of this optional interface is implemented. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
If you have involved in any new application development projects then its better to make use of SQLNative client, as the data types such as VARCHAR(MAX) (NVARCHAR & VARBINARY) including the new XML & UDT data types cannot be returned or accessed when you have usage of earlier clent versions of SQL Server 2005, usage of large object types such as IMAGE/TEXT and BLOBs has become common within the web applications and if you wish to use such type of data then best to go with SQL Native client only. While mentioning the usage of TEXT column the maximum size of data is 2,147,483,647, whereas SQL Native Client (underlying ODBC & OLE DB) reports that the maximum size of varchar(max) is similar in size or even -1 that depends on the platform of web components.
Its worth mentioning about usage of ODBC or OLEDB properties when you are using them within transactions mode, as there is much difference between SQL Native client & MDAC. Say in the terms of starting the transaction process immediately SQLNative client will have upper hand to begin immediately as compared to MDAC, as it will begin after first access to the database. This is where the performance or fetching quicker results will be varied within stored procedures and batches when such transactions usage is initiated. In similar to this approach the underlying functionality of ITransactionLocal::BeginTransaction will cause a transaction to be started immediately when SQLNative client is used and within MDAC the transaction start was delayed until the application executed a statement which required a transaction in implicit transaction mode. When it comes to the transaction isolation with row versioning both of these components supports this method, only advantage for SQLNative client is using the snapshot transaction isolation.
For all further information on transactions processing (with ROLLBACK & COMMIT) methods and support for snapshot isolation refer to the updated books online for SQL Server 2005 (the referred link is valid at the time of posting this blog post).