SQL Broker: Superior SQL Linked Server Integration through SQL TDS



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.
  • OLE DB ODBC syntax does not scale
    Using OPENQUERY requires complex subselects 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 vastly superior tool for SQL Linked Server connectivity. With SQL Broker, you can create a TDS remoting service that allows SQL Server to communicate with an ODBC data source (on the same or a separate machine) as if it were a linked 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 subquery to the ODBC driver for only the data you want. This method offers better performance, but you are deviating from simple, familiar SQL syntax and are required to use more complex subselects 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 for an ODBC data source 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 ODBC drivers, whether they are installed locally or on separate machines. SQL Server passes the column filters and search criteria for the data on to the ODBC driver, improving the query's performance. 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 high performance. The SQL Broker is currently available for machines running Windows. Download the SQL Broker and create TDS (and MySQL) remoting services for bidirectional access to live data from any ODBC data source. ODBC remoting gives you unparalleled freedom in where and how you work with data from your ODBC data sources!

Learn More

As always, our world-class support team is here to help. If you have any questions about the SQL Broker, or connectivity with any of the CData ODBC Drivers, please let us know. More information about Linked Server configuration can be found in the following resources:

 
 
Downloads