To configure linked server pointing to a SQL server, there are 4 possible options.

  1. Server Type: SQL Server

  2. Server Type: Other data source; Microsoft OLE DB Provider for SQL Server

  3. Server Type: Other data source; SQL Server Native Client 11.0

  4. Server Type: Other data source; Microsoft OLE DB Driver for SQL Server

In this question I want to specifically ask when is the recommended to use either of the above in terms of a) use case; and b) deprecated/recommended not to be used.

  • 1) and 2) are the same thing. 4) is the current driver and the successor to 3). Oct 22 at 15:12

I don't think there's much specific variance on why to use one over the other, as far as SQL Server is concerned, rather they're just multiple options to accomplish the same thing. Likely it just depends on which drivers you have installed on the host server to your SQL instance, so not all of those options may always be applicable.

Additionally, the different options were probably added at different times in history as other methodologies became deprecated. My advice would be to generally always use the "Server Type: SQL Server" option first on any modern SQL Server instance.

You will probably find a lot of helpful information in Microsoft's Driver history for Microsoft SQL Server. This communicates some of the key differences of each option.

Aside from that, those other options (under the "Other data source" selection) are used for connecting to heterogeneous SQL instances such as Oracle, PostgreSQL, and MySQL servers too.

  • I want to give my linked server a name that is different to the actual server name, so I have to choose between 1 of the other 3 options from under Server Type: Other data source. Any specific order of use is recommended, like prefer this over that?
    – variable
    Oct 22 at 12:11
  • @variable Please see my updated answer for a link to a Microsoft doc with more in depth information on the differences.
    – J.D.
    Oct 22 at 12:45

If you have access to DNS you can create a CNAME, or alias, with the name you want and point it to your server. If you don't have access to DNS you could also add an entry to your "Hosts" file on the server which will create the same kind of redirect as DNS only locally. If you decide to use a Hosts file make sure you document it well so in the future when the destination server name changes everyone will know where to go to make the change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.