SQL Server Polybase and Oracle Exadata HA/DR connection strings-CodePudding

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. If we did this in a linked server, the connetion would look like this:

(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=ExaData1.domain.com)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=ExaData2.domain.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=OracleTNSName)))

Yet, I can't seems to define it this way in Polybase (at least I haven't figure out how to yet). 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, which is based upon the previously defined data source, listing the TNS name as part of the DATA_SOURCE properties:

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.

CodePudding user response：

Have you tried with this?

oracle//server1:1521; AlternateServers=(server2:1521,server3:1521,server4:1521); LoadBalancing=true

CodePudding user response：

The only other option to connect is by using ExaData SCAN name (configured by Oracle database administrator, which resolves to any of the nodes using a single name). From Oracle it looks like this:

(DESCRIPTION =
      (CONNECT_TIMEOUT=90) (RETRY_COUNT=20)(RETRY_DELAY=3) (TRANSPORT_CONNECT_TIMEOUT=3)
                 ( ADDRESS = (PROTOCOL = TCP)(HOST=sales-scan.mycluster.example.com)(PORT=1521))
                           (CONNECT_DATA=(SERVICE_NAME=oltp.example.com)))

Then from SQL Server you connect to sales-scan.mycluster.example.com