SQL Server Native Client 11.0 vs ODBC Driver 13 for SQL Server driver

[alt_tush]

Hello,

I have two servers where in Server1 there is only one DRIVER installed "SQL Server Native Client 11.0"

and in another server SERVER2 there are two DRIVERS are installed "SQL Server Native Client 11.0" and "ODBC Driver 13 for SQL Server driver".

I have one Alteryx workflow where i connected the SQL Server database (SSMS 17).

In this workflow i have 240 MB size .yxdb database file which i append this data in SQL Database using output tool 

using alias aka:DATABASENAME|||dbo.TABLENAME

File format ODBC Database (odbc:) and output options : Append existing

Using Server1, append process run completed in 30 Min using below string because Server1 has only one driver installed i.e. "SQL Server Native Client 11.0"

DRIVER={SQL Server Native Client 11.0};DATABASE=DATABASENAME;SERVER=SERVERNAME;Trusted_Connection=yes|||DATABASE.dbo.TABLENAME

Using Server2 same workflows takes almost 7-8 Hrs which is drastically increased by 7 Hrs which is not appropriate.

After observation i have found that in Server2 workflow considered the "ODBC Driver 13 for SQL Server driver" instead of "SQL Native driver". 

As per me that is the reason where it takes lot of time to run in Server2

DRIVER={ODBC Driver 13 for SQL Server};DATABASE=DATABASE;SERVER=SERVERNAME;Trusted_Connection=yes|||dbo.TABLENAME

1. My First question is what is the different between those two drivers.

2. How to run the workflow using "SQL Server Native Client 11.0 " instead of use "ODBC Driver 13 for SQL Server driver" in Server2 without uninstalling "ODBC Driver 13 for SQL Server driver" 

Please note : Both are the Windows server and server configuration of both the servers are exactly same in term of Processor and RAM.

It would be great help if you can help me on this.

Thank you in advance.

[danilang]

Hi @alt_tush 

Using the "Quick connect" option is quick, but it can also make it difficult to get equivalent behaviour on all systems.    What I would suggest is to explicitly set up your odbc connection using a named data source.  Create a new System DSN in ODBC Administrator that uses the SQL Server Native Client 11.0 driver on all the affected servers and your desktop as well.    In your workflow, use a new data source using the MS ODBC option 

Then pick the named ODBC DSN that you previously defined

Input connections should now look like "odbc:DSN=Music" and output connections will include the table name like "odbc:DSN=Music|||albums"

This removes any ambiguity from the process.  If the named ODBC source is configured on all the servers with the same underlying driver, the workflow will always have the same performance

Dan