A Linked Server is a feature of Microsoft SQL Server that allows users to execute federated queries that combine data stored in SQL Server with other linked data sources. When creating a linked server the OLE DB Provider is commonly promoted as an interface to ODBC.

Unfortunately SQL Server's connectivity through OLE DB has some significant pitfalls, and users often become frustrated due to performance and scalability. Common issues include:

• Querying ODBC via the OLE DB Provider is slow - ODBC performs a SELECT * on every table that is queried.
• Querying ODBC via the OLE DB Provider uses syntax that does not scale - Using OPENQUERY requires complex sub-selects for every table.
• Server installation and security - To use the OLE DB Provider, the ODBC Driver must be installed on the same machine running SQL Server.

The CData SQL Broker is a better tool for SQL Linked Server connectivity. With SQL Broker, you can create a TDS (tabular data stream) remoting service that allows SQL Server to communicate with an ODBC data source (whether installed locally or on a separate machine) as if it were a linked, remote SQL Server instance.

In this article, we discuss why creating a linked server using a TDS remoting service in SQL Broker is a better way to create a linked server. We will work in the context of writing a JOIN query where we are searching for all of the orders in a local SQL Server database tied to a specific Salesforce account exposed by an ODBC driver.

Performance

While using the OLE DB Provider, you can send a simple INNER JOIN. Unfortunately, with this kind of linked server, SQL Server does not pass any column filters and/or search criteria from the QUERY to the ODBC driver, instead sending a SELECT * query. Because SQL Server requests all of the data from the ODBC table, any filters and criteria are only applied client-side, which can cause performance issues, especially when the ODBC table has a large number of rows.

SELECT LocalOrder.ID, ODBC.Name
FROM CData.dbo.[Order] AS LocalOrder
INNER JOIN [SF_ODBC].[CData].[Salesforce].[Account] AS ODBC
  ON LocalOrder.AccountID = ODBC.ID
WHERE ODBC.Name = 'Company A';

Complex Queries

You can also send a more complex INNER JOIN, using OPENQUERY and sending a sub-query the ODBC driver for only the data your want. This method offers better performance, but you are deviating from simple, familiar SQL syntax and are required to use more complex sub-selects as your data needs grow in complexity.

SELECT LocalOrder.ID, ODBC.Name
FROM CData.dbo.[Order] AS LocalOrder
INNER JOIN (SELECT * FROM OPENQUERY(SF_ODBC, "SELECT ID, Name FROM Account WHERE Name = 'Company A'")) AS ODBC 
  ON LocalOrder.AccountID = ODBC.ID;

Required Installation

Linked Servers created using the OLE DB Provider require you to install the ODBC driver on the same server that is hosting your SQL Server instance.

Using TDS Remoting with CData SQL Broker

In stark contrast to using the OLE DB Provider, when you create a linked server using TDS remoting, SQL Server treats the linked server as a linked SQL Server instance, so you can write simple, familiar JOIN queries without losing any performance and while connecting to remote ODBC drivers. SQL Server passes the column filters and search criteria for the remote data on to the ODBC driver, improving the query's performance. And with the SQL Broker, you can host your ODBC drivers on a separate machine. With a linked server created using TDS remoting you can quickly and easily get exactly the data that you want in SQL Server.

SELECT LocalOrder.ID, Linked.Name
FROM CData.dbo.[Order] AS LocalOrder
INNER JOIN [SALESFORCE].[CData Salesforce Source Sys].[Salesforce].[Account] AS Linked 
  ON LocalOrder.AccountID = Linked.ID
WHERE Linked.Name = 'Company A';

By using the TDS remoting services from the SQL Broker, you get queries that are both familiar and performant. The SQL Broker is currently available for machines running Windows. Download the SQL Broker and create TDS (and MySQL) remoting services for bi-directional access to live data from any ODBC data source. This remoting gives you unparalleled freedom in where and how you work with data from your ODBC data sources!