SQLNCLI10.x vs 11.x – Connecting SSIS 2008R2 to SQL Server 2012

connectivitysql serversql-server-2012ssis

Starting from the question and working backwards.

My question: is it OK to use SQLNCLI10.1 as a provider for SSIS 2008 R2 to connect to SQL Server 2012 database(s)?

I have a SSIS box running SQL Server & SSIS 2008 R2. The SSIS packages connect variously to different data sources including different SQL Server databases, and some of these databases are on SQL Server 2012. In line with the general principle that you would use the right provider for the source you are connecting to, I would expect that the "right" choices are SQLNCLI11 to connect to SQL Server 2012, and SQLNCLI10 to connect to SQL Server 2008 & 2008 R2.

However using SQLNCLI10.1 connects fine to SQL Server 2012 and appears to be fully functional and bug free.

Couple this with these other factors:

  1. The SSIS box I refer to is one element of the dev lifecycle so to change this box means also installing v11 on several other boxes.

  2. The added complexity of having to choose the right provider for the right DB Connection instead of a single SQLNCLI (there are no SQL Server 2000 / 2005 sources to worry about). Especially since the ConnMgr doesn't allow you to change it once set.

  3. Issues such as this which imply there are greater risks in using 11 over 10.

I realise open or discussion type questions are frowned upon, so, Q&A:

  1. Are there any RISKS in staying using SQLNCLI10 to connect from SQL Server 2008 R2 (SSIS) to SQL Server 2012?

  2. Would there be any performance loss for the same, over using v11 instead?

  3. Am I losing functionality or risking breakage by using 10.1 instead of 11.x?

Thanks.

Best Answer

According to this and this, there are upgrades to the sqlcmd.exe utility that I am assuming are reflective of underlying changes in the libraries. For instance,

Beginning in the ODBC Driver 11 for SQL Server, there is no limit on the amount of data that retrieved in a single column when –y0 is specified.

I realize that this says the ODBC driver, but according to another link (that I can't post because of reputation limits), the SQLNCLI is some kind of wrapper for the ODBC drivers.

The SQL Server Native Client...contains the SQL Server ODBC driver for Linux and ODBC/ OLE DB provider for Windows , supporting native connectivity to Microsoft SQL Server.

From my limited searching, there don't appear to be any issues with using 10 to connect to a later sql server instance, but if there are SQL server features from that instance that you're taking advantage of, and you need access to them from your packages, then you're going to want to upgrade.

I can't speculate on performance, and would recommend benchmarking the two methods on your dev box to see what the differences are.

You are certainly losing functionality. I can't find any release notes that would indicate what functionality, per se, but the links above seem to hint at their being sql server specific functionality that is included in the NCLI APIs.

Related Question