Polybase Oracle ExaData multiple Location string

Question

Hello,

Working with SQL Server 2019 Enterprise CU18 Polybase (On-premise). I have a working connection from SQL Server to Oracle that is defined as follows:

CREATE EXTERNAL DATA SOURCE [OracleDataSource]   
WITH (LOCATION = 'oracle://ExaData1:1521', CREDENTIAL = [OracleCredential])  

However, we have an HA/DR pair for our exadata system. ExaData1 and ExaData2.

I need to figure out how to list both possible servers in the External Data Source so when the DB fails over, it will follow it naturally. The other option is to figure out how to use the TNS Name, rather than the server name for the Location, but I've not figured that out either. Currently, when I define a table, it looks like this:

CREATE EXTERNAL TABLE [Polybase].[sample]  
(  
	[SID] [nvarchar](8) NULL,  
	[MESSAGE_DATE] [datetime2](7) NULL,  
	[MESSAGE_ID] [nvarchar](3) NULL  
)  
WITH (DATA_SOURCE = [OracleDataSource],LOCATION = N'[OracleTNSNAME.domain.com].OracleSchema.SAMPLE')  

Anyone have suggestions or options as I'm not finding anything in the MS documenation.

Appreciate any and all support.

Sincerely,

Nathan

Answer 1

Hi @Nathan Heaivilin ,

If you select TNSNAME mode, provide the following values:

1. In the Connect identifier box, enter connect identifier (TNS alias) of the database.
2. In the User name box, enter an Oracle account that has the necessary permissions.
3. In the Password box, enter the password for the specified user name.

For more details , please see:
https://learn.microsoft.com/en-us/sql/ssma/oracle/connecting-to-oracle-database-oracletosql?view=sql-server-ver16
https://www.sqlshack.com/link-sql-server-oracle-database/

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@NikoXu-msft

I am not seeing how the SSMA helps me define an SQL Server Polybase External Data Source for Oracle. They are two different tools with different functionality.

In regards to LinkedServers, we currently have those and I'm able to accurately define the ExaData HA/DR connection strings. However, we are moving away from LinkedServers to SQL Server Polybase for better security, scaling, and stability

What am I missing as I don't see how either of the articles above help me, given they use different connection string formatting?

Nathan

Answer 3

For anyone curious, I've found a way around this using the two following methods:

1. Use Oracle Connection Manager (Similar to an AG Listener in how it works). Then you only need 1 connection string
2. Setup an ODBC connection with the appropriate connection string and then use the DSN in the Polybase string