What do the different SQL Server Drivers mean?

Scenario

You have been asked to create an ODBC Data Source to connect an application to SQL Server. Most of the time you my not have a choice and the driver you will use to create a connection to SQL Server is dependant on your application requirements. But exactly what does each driver do and why do I see different drivers depending on the server I connect to?

Note: while you can choose between 32 bit and 64bit drivers for the purposes of this post I am going to concentrate on 64bit SQL Server drivers.

If we create a DSN on machine without SQL Server installed we will get the following driver option:

1

This is the Windows WDAC/MDAC driver which can be used to connect to SQL Server. Typically this would by used by generic application not taking advantage of SQL specific features. This driver is distributed as part of the Windows install. The roadmap for this driver can be viewed here

For SQL Server 2005 through to 2012 Microsoft SQL Server distributes and maintains a SQL Server driver called  SQL Server Native Client (SNAC). Different versions of SQL Server will have different SNAC numbers.

Taking the example of  SQL Server 2008R2 we can see version 10.0 of SNAC

2

The SNAC driver will be installed as part of the SQL Server install. On client machines where SQL Server will not be installed a standalone package is available as part of the Microsoft SQL Server 2008 R2 Feature Pack.

Why install SNAC?

When your application wishes to take advantage of SQL Server features like read only routing in availiability groups you will need to install SNAC 11  from the SQL Server 2012 feature pack.

With an install of SQL Server 2014 the new ODBC driver for SQL Server is installed. It also includes an install of the  SQL Server 2012 SNAC driver.

To summarise

4

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s