by Dibyendu Datta | November 20, 2023

Unlocking Data Potential: Virtualization in Diverse Database Environments

Data virtualization is a technique that allows organizations to access and integrate live data from diverse sources in their enterprise databases, regardless of their formats or locations. It decreases the need to physically move or replicate data into a central repository, reducing complexity, and ensuring live access to up-to-date information.

Data virtualization bridges the gap between data silos, promoting efficient and holistic data management. Connecting an ensemble of data sources used in different industries to prominent relational databases like Microsoft SQL Server, My SQL, and PostgreSQL enables businesses to harness the full spectrum of their data assets to improve decision-making, analytics, and agility while minimizing data redundancy and maintenance costs.

Linked Server in SQL Server facilitates data virtualization by connecting it to external data sources, such as other databases, files, or APIs, as if they were local tables. By defining a linked server, SQL Server can retrieve, query, and even modify data on these remote sources. This simplifies data consolidation, reporting, and analysis, and is crucial for achieving comprehensive data virtualization within the SQL Server environment. MySQL and PostgreSQL databases can connect to external data sources through MySQL Remoting and Foreign Data Wrappers (FDWs). These tools create direct connections to live data from these sources, enabling unified querying, federated data access, real-time integration, and data aggregation.

CData connectivity solutions allow you to establish connections to a wide range of data sources and integrate them into your SQL Server, MySQL or PostgreSQL environment for any of more than 250 supported SaaS, big data, and NoSQL sources. In this article, we briefly describe how to access data with linked servers in SQL Server, MySQL Remoting, and TDS (tabular data stream) foreign data wrapper for PostgreSQL while utilizing CData ODBC Drivers and  CData Connect Cloud.

In this article, we’ll use Salesforce as a data source, but the principles apply to any supported source.

SQL Server: Connecting to any ODBC data source as a linked server

1.  Connect to a data source using the CData ODBC Driver

Fig 1: Connect to a data source using ODBC (open database connectivity) driver DSN (data source name) configuration

2.  Configure the TDS remoting service

Fig 2: Configure the SQL Gateway by adding a new service using the TDS protocol and the previously configured DSN

3.  Create a linked server for the ODBC data source

Fig 3: Create a new linked server from the SQL Server Management Studio object explorer

4.  Query from external data through SQL Server

SELECT * FROM [LINKED_SERVER].[CATALOG].[SCHEMA].[table_name]

Fig 4: Query remote data from SQL Server Management Studio

Full knowledge base article: Connecting to Any ODBC Data Source as a Linked Server

MySQL: Remote data access with CData ODBC Drivers and MySQL Remoting

1. Connect to a data source using ODBC Driver

Fig 5: Connect to a data source using ODBC driver DSN configuration

2. Configure the MySQL remoting service

Fig 6: Configure the SQL Gateway by adding a new service using the MySQL protocol and the previously configured DSN

For a step-by-step explanation of configuring the CData SQL Gateway service that listens for requests from clients in MySQL protocol, read our setup guide.

3. Create a connection in MySQL for the ODBC data source

Use SQL queries to create a federated server, federated tables, and to query data:

a. Create a federated server

CREATE server fed_datasource 
FOREIGN DATA wrapper mysql 
OPTIONS (USER
'sql_gateway_user', PASSWORD 'sql_gateway_password', host 'sql_gateway_host',
port ####, DATABASE 'Federated_Table'); 

b. Create a federated table

CREATE TABLE fed_table 
      ( 
       …  
            name   VARCHAR (32) NOT NULL DEFAULT '', 
            other  INT(20)      NOT NULL DEFAULT '0', 
       … 
    ) 
ENGINE=FEDERATED DEFAULT  
charset=latin1  
connection='fed_datasource/remote_table'; 

c. Execute queries

SELECT fed_table.industry, 
       local_table.custom_field 
FROM   local_table 
       JOIN fed_table 
         ON local_table.foreign_industry = fed_table.industry; 

Full knowledge base article: Connect to MySQL Data as a Federated Table in MySQL

PostgreSQL: Remote data access with CData ODBC Drivers and TDS foreign data wrappers

1. Connect to a data source in CData Connect Cloud

Fig 7: Add a new data source connection from the Connections page in CData Connect Cloud

2. Build a TDS foreign data wrapper

The foreign data wrapper (FDW) can be installed as an extension to PostgreSQL without recompiling. The tds_fdw extension is used as an example in this article.

3. Connect to a Data Source as a PostgreSQL Database

The final step is using the extension to connect to the data source through Connect Cloud, creating a server object, mapping the Connect Cloud credentials, creating a local schema, and creating the foreign table(s) in your local PostgreSQL database. Afterwards, you can execute read and write queries to the remote data source directly from PostgreSQL.

Fig 8: Query data from the remote data source using the PostgreSQL CLI (command line interface)

Full knowledge base article: Connect to Live QuickBooks Online Data in PostGresSQL Interface through CData Connect Cloud

Get more information and a free trial

Now that you have seen how to use a linked server, MySQL remoting, and foreign data wrappers to create connectivity to live data for virtualization in SQL Server, MySQL, and PostgreSQL with any ODBC data source, visit our ODBC Driver page to read more information and get a free 30-day trial. 

Have you joined the CData Community? Ask questions, get answers, and share your knowledge in CData connectivity tools. Join us!