SQL Gateway: 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, regardless of any criteria or filters present in the original query.
• OLE DB ODBC syntax does not scale
  Using OPENQUERY (which is an alternative to the limitations above) requires complex subselects for every table.
• Server installation and security
  To use the OLE DB Provider, the ODBC Driver must be running in-process with the SQL Server instance, which could have implications to normal SQL Server query load, impacting stability and performance.

The CData SQL Gateway is a vastly superior tool for SQL Linked Server connectivity. With SQL Gateway, 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 Gateway 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 Gateway

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 Sys].[Salesforce].[Account] AS Linked 
  ON LocalOrder.AccountID = Linked.ID
WHERE Linked.Name = 'Company A';

By using the TDS remoting services from the SQL Gateway, you get queries that are both familiar and high performance. The SQL Gateway is currently available for machines running Windows. Download the SQL Gateway and create TDS (and MySQL) remoting services for bidirectional access to live data from any CData 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 Gateway, 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:

• Article: Configuring a SQL Linked Server using SQL Gateway