Welcome to another blog post on SQL Server! Today, we will be discussing the Polybase feature and how it can be used to integrate SQL Server with Oracle databases. This feature is available in SQL Server 2019 and provides a seamless way to consume data from Oracle.

Before we dive into the details, it’s important to note that SQL Server on Linux only allows you to create a linked server to another SQL Server instance. Therefore, if you want to connect to an Oracle database, you need to utilize the Polybase feature.

In this article, we will walk through the process of setting up a Polybase reference to an Oracle database using Docker images. This will allow us to test each step and highlight any potential errors you may encounter.

Configuring Docker Desktop

If you’re using Windows 10, you’ll need to install Docker Desktop and make some adjustments to the default settings. By default, Docker Desktop limits the memory to 2 GB, but the Polybase feature requires 1 GB for itself and the SQL Server image requires 2 GB. To avoid errors during the setup process, you’ll need to increase this limit.

Creating the Oracle Container

Once Docker Desktop is configured, we can proceed with creating the Oracle container. We’ll be using the latest stable image from Oracle. After downloading the image, you can confirm its running status and view the logs to ensure that the resources have stabilized.

Next, we’ll need to retrieve the IP address assigned to the Oracle container. This information will be used later when creating the Polybase reference. Additionally, we’ll need to gather the service name and port of the Oracle database by inspecting the tnsnames.ora file.

There are a few additional steps required, such as increasing the connectivity compatibility and resetting the password of the SYSTEM user. These steps are necessary to avoid authentication and authorization errors when connecting to the Oracle database.

Finally, we’ll create a test table in the Oracle database that we’ll be consuming data from.

Creating the SQL Server Container and Installing Polybase

With the Oracle container set up, we can now move on to creating the SQL Server container and installing the Polybase feature. We’ll be using the latest stable image from Microsoft SQL Server 2019.

After downloading the image and starting the container, we’ll need to log in as root to configure the instance. We’ll then run a series of commands to make the Polybase feature discoverable and install it.

Once the installation is complete, we’ll need to restart the SQL Server container to enable Polybase. After the restart, we can confirm the running status and view the logs to ensure that the resources have stabilized.

Next, we’ll log in to the SQL Server container and use sqlcmd to finish the setup process. We’ll create a database from which we’ll access the Oracle table and create a test table and a loopback data source to test the functionality of Polybase.

Creating the Polybase Reference for Oracle

Now that everything is set up, we can create the Polybase reference for the Oracle source. This involves creating a database scoped credential and an external data source. We’ll specify the location of the Oracle database using the IP address and port obtained earlier.

Finally, we’ll create an external table that maps to the Oracle table we want to consume data from. We can then query this external table to confirm that the Polybase integration with Oracle is working successfully.

It’s important to note a few things when working with Polybase and Oracle integration. When creating the external data source, you can specify the PUSHDOWN option to control whether certain operations are pushed down to the Oracle database. Additionally, when creating the external table, you need to provide a three-part identifier for the location, consisting of the database (and domain), user schema, and table name.

That wraps up our exploration of Polybase and Oracle integration in SQL Server. We’ve covered the setup process using Docker images and highlighted some important considerations along the way. With Polybase, SQL Server becomes even more versatile, allowing you to seamlessly integrate with other databases like Oracle.

For more information on Polybase on Linux and detailed Polybase configuration for Oracle, you can refer to the official documentation provided by Microsoft.