1. Load data from Azure SQL Server to Exadata Database Service on Dedicated Infrastructure
  2. Load Data Using Oracle Database Gateway Running in OCI Compute

Load Data Using Oracle Database Gateway Running in OCI Compute

OracleDB for Azure enables Azure applications to load and query data from OCI databases. This architecture describes how an Oracle Exadata Database Service on Dedicated Infrastructure can leverage the infrastructure provided by OracleDB for Azure to load and query data from Azure SQL Server using a database link via Oracle Database Gateway for SQL Server software running in an OCI Compute instance.

Architecture

This architecture uses Oracle Database Gateway for SQL Server running in an OCI Compute instance to load data from Azure SQL Server to Oracle Exadata Database Service on Dedicated Infrastructure. Oracle Database Gateway for SQL Server connects to Azure SQL Server using the fast-low latency network link using an Azure private endpoint.

The following diagram illustrates this architecture.


Description of exadata-db-azure-odsa-gateway.png follows
Description of the illustration exadata-db-azure-odsa-gateway.png

exadata-db-azure-odsa-gateway.zip

Note the following about this architecture:

  • A new security rule must be created within the Oracle Exadata Database Service on Dedicated Infrastructure client subnet to allow traffic to port 1521.
  • Oracle Database Gateway software can run on OCI Compute Virtual Machine or Bare Metal. The number of OCPUs, memory, and disk size must be assigned to the OCI Compute instance according to the Oracle Database Gateway for SQL Server Server requirements and sizing guidelines. See System Requirements for Oracle Database Gateway for SQL Server below for details.
  • Hostname to IP address resolution for the Azure SQL Server endpoint is required in OCI. Edit the /etc/hosts file on the Oracle Database Gateway server, or configure a Private DNS Zone on the OCI Virtual Cloud Network (VCN) where the Oracle Exadata Database Service on Dedicated Infrastructure is configured.
  • In Oracle Exadata Database Service on Dedicated Infrastructure databases, the parameter global_names is set to TRUE by default. Therefore, the fully qualified database name must be used when creating the database link to Azure SQL Server.

Before You Begin

Before you begin, check the versions of major software components used in this setup, download the required software package, and review the product documentation for later reference.

Review Software Requirements

These instructions are tested on the following software and hardware versions.

  • Oracle Exadata Database Service on Dedicated Infrastructure X8M-2
  • Oracle Database 19.16
  • Azure SQL Server

Download the following software package:

Review Documentation

About Required Services and Roles

This solution requires the following services:

  • Oracle Exadata Database Service on Dedicated Infrastructure with OracleDB for Azure
  • Oracle Cloud Infrastructure
  • Oracle Cloud Infrastructure Compute

These are the roles needed for each service.

Service Name: Role Required to...
Oracle Exadata Database Service on Dedicated Infrastructure: sys con Configure the database link.
Oracle Cloud Infrastructure: VCN Admin Add security rules and modify DNS.
Oracle Cloud Infrastructure Compute: admin Create Compute instance to install Oracle Database Gateway.
Oracle Cloud Infrastructure Compute: opc/root Create user to install and configure Oracle Database Gateway software. Modify local firewall to allow connections to port 1521.

See Learn how to get Oracle Cloud services for Oracle Solutions to get the cloud services you need.

Considerations for Oracle Database Gateway Running in an OCI Compute Instance

Review these considerations before implementing this solution. Running the Oracle Database Gateway for SQL Server in an OCI Compute instance is ideal for customers who are familiar with OCI services.

Advantages of Oracle Database Gateway Running in OCI Compute

  • Oracle Database Gateway for SQL Server is developed and supported by Oracle.
  • No additional third-party software is required to be installed on Oracle Exadata Database Service on Dedicated Infrastructure, making the environment easy to update and maintain.
  • Oracle Database Gateway for SQL Server runs on a separate compute instance from Oracle Exadata Database Service on Dedicated Infrastructure, which makes both environments easy to update and maintain. In addition, the solution might be more affordable, as Oracle Database Gateway software runs on less expensive OCI Compute OCPUs instead of running on the more specialized, higher priced Oracle Exadata Database Service on Dedicated Infrastructure OCPUs.
  • Communication between Oracle Database Gateway Server and Microsoft Azure SQL Server is encrypted (enforced by Microsoft Azure SQL Server by default).
  • Data type conversion and mapping between Oracle databases and Microsoft Azure SQL Server databases are automatically managed by Oracle Database Gateway software.

Disadvantages of Oracle Database Gateway Running in OCI Compute

  • An additional license for Oracle Database Gateway software may be required.
  • Communication between Oracle Exadata Database Service on Dedicated Infrastructure and Oracle Database Gateway Server is not encrypted by default when configuring Oracle Database Gateway Server. Additional configuration in Oracle Database Gateway Server sqlnet.ora is required to enforce encryption from Oracle Exadata Database Service on Dedicated Infrastructure to Oracle Database Gateway Server.

Configure Oracle Database Gateway Running in OCI Compute

  1. Log in to the Azure console to obtain the information for the Azure SQL Server database from which the Oracle Exadata Database Service on Dedicated Infrastructure database will load and query data. This information is required to tell the Oracle Database Gateway server how to connect to the Azure SQL Server database.
    Obtain the following details:
    • Database Name: Name of the Azure SQL Server database.
    • Fully Qualified Server Name: Fully qualified hostname for SQL server where the Azure SQL Server database is running, usually in the format servername.database.windows.net.
    • Azure SQL Server Private Endpoint: The name and IP address of the Azure SQL Server private endpoint in the Azure vnet that connects to Azure SQL Server. If no private endpoint exists yet, create one for the Azure SQL Server, and note the name and IP address.
    • Azure SQL Server Username: Name of an existing Azure SQL Server user allowed to connect to the Azure SQL Server database and schema, usually sqladmin.
    • Azure SQL Server User Password: Password for the Azure SQL Server user to connect to the Azure SQL Server database and schema.
  2. Log in to the OCI console and create an OCI Compute instance on the OracleDB for Azure Oracle Exadata Database Service on Dedicated Infrastructure client subnet running Oracle Linux 7.9. This compute instance will run Oracle Database Gateway for SQL Server.
    Note the hostname and IP address assigned to this compute instance. This information is required to tell Oracle Exadata Database Service on Dedicated Infrastructure how to connect to the Oracle Database Gateway server.
  3. From the OCI menu, select Networking/DNS Management, and create a Private DNS Zone. In the new Private DNS Zone, add a record for the Fully Qualified Server Name from step 1 to resolve to the Azure SQL Server private endpoint IP address from step 1.
  4. From the OCI menu, create a DNS Private View. Add the Private DNS Zone created in step 3 to this DNS Private View, and activate the changes.
  5. From the OCI menu, select Networking/Virtual Client Network (VCN). Select the VCN created when the OracleDB for Azure for Oracle Exadata Database Service on Dedicated Infrastructure was provisioned (change the compartment if required). Select the DNS resolver link, and add the DNS Private View created previously to the DNS resolver.
    This enables name and IP address resolution from OCI services (compute and Oracle Exadata Database Service on Dedicated Infrastructure) to the Azure SQL Server private endpoint.
  6. Log in to one of the Oracle Exadata Database Service on Dedicated Infrastructure VM Cluster nodes and use the nslookup command to verify the hostname to IP address resolution is working for the Azure SQL Server hostname and private endpoint IP address.
    For example:
    [opc@exa_dbnodex ~]$ nslookup servername.database.windows.net
Server: 169.254.169.254
Address: 169.254.169.254#53
Non-authoritative answer:
Name: servername.database.windows.net
Address: IP_address_of_Azure_SQL_Server_private_endpoint
 [opc@exa_dbnodex ~]$
    The variables in the example have the following values:
    • servername is the name of the fully qualified server name of the Azure SQL Server from step 1.
    • IP_address_of_Azure_SQL_Server_private_endpoint is the IP address of the Azure SQL Server private endpoint from step 1.
  7. Install the Oracle Database Gateway software you downloaded earlier on the new OCI compute instance.
  8. Configure the Oracle Database Gateway for Azure SQL Server running in the OCI Compute instance to connect to the Azure SQL Server database via the Azure SQL Server private endpoint using the fast-low latency network link configured by OracleDB for Azure between Azure and OCI.
  9. On all nodes of the Oracle Exadata Database Service on Dedicated Infrastructure VM Cluster, configure the Oracle Exadata Database Service on Dedicated Infrastructure database tnsnames.ora to resolve to the Oracle Database Gateway for SQL Server.
    For example:
    [opc@exa_dbnodex ~]$ sudo su - oracle
Last login: Tue Sep 13 20:06:30 UTC 2022
[oracle@ exa_dbnodex ~]$ . ./exadb_name.env
[oracle@ exa_dbnodex ~]$ cd $ORACLE_HOME
[oracle@ exa_dbnodex dbhome_1]$ cd network/admin
[oracle@ exa_dbnodex admin]$ cd exadb_name/
[oracle@ exa_dbnodex exadb_name]$ tail -10 tnsnames.ora
Database_Name_From_Step_1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=OCI_Compute_Instance_Hostname_From_Step_2)(PORT=1521))
(CONNECT_DATA=(SID=Database_Name_From_Step_1,))
(HS=OK)
)
    The variables in the example have the following values:
    • exadb_name is the name of the Oracle Exadata Database Service on Dedicated Infrastructure database.
    • Database_Name_From_Step_1 is the database name from step 1.
    • OCI_Compute_Instance_Hostname_From_Step_2 is the hostname of the OCI Compute instance from step 2.
  10. Use the tnsping command to test connectivity between the Oracle Exadata Database Service on Dedicated Infrastructure and Oracle Database Gateway running in OCI Compute (this connection is entirely within the OCI VCN).
    For example:
    [oracle@exajc-dbnodex ~]$ tnsping Database_Name_From_Step_1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2022 21:28:34
Used parameter files:
/u02/app/oracle/product/19.0.0.0/dbhome_1/network/admin/exadb_name/s
qlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= 
(ADDRESS=(PROTOCOL=tcp)(HOST==OCI_Compute_Instance_Hostname_From_Step_2)(PORT=1521)) (CONNECT_DATA=(SID=Database_Name_From_Step_1)) 
(HS=OK))
OK (0 msec)
[oracle@exadbnodex ~]$
    The variables in the example have the following values:
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database from step 1.
    • exadb_name is the name of the Oracle Exadata Database Service on Dedicated Infrastructure database.
    • OCI_Compute_Instance_Hostname_From_Step_2 is the hostname of the OCI Compute instance from step 2.
  11. In the Oracle Exadata Database Service on Dedicated Infrastructure pluggable database (PDB) (not in the CDB), create a database link to the Azure SQL Server database. The parameter global_names is set to TRUE in Oracle Exadata Database Service on Dedicated Infrastructure databases by default, therefore the fully qualified database name must be used to create the database link. After the database link is created, you can query and load data from the Azure SQL Server database to the Oracle Exadata Database Service on Dedicated Infrastructure pluggable database using this link.
    [opc@exadbnodex ~]$ sudo su - oracle
Last login: Tue Sep 13 20:06:30 UTC 2022
[oracle@exadbnodex ~]$ . ./exadb_name.env
[oracle@exadbnodex ~]$sqlplus / as sysdba
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 PDBName READ WRITE NO
SQL> alter session set container=PDBName;
Session altered.
SQL>create public database 
link Fully_Qualified_Server_Name_From_Step_1 connect to 
"Azure_SQL_Server_Username" identified by 
"Azure_SQL_User_password" using 
'Database_Name_From_Step_1';
Database link created.
    Note the username and password are enclosed in double quotes (“ ”), while the Azure SQL Server database name is enclosed in single quotes (‘ ‘).
    The variables in the example have the following values:
    • exadb_name is the name of the Oracle Exadata Database Service on Dedicated Infrastructure database.
    • PDBName is the name of the pluggable database.
    • Fully_Qualified_Server_Name_From_Step_1 is the fully qualified hostname for SQL server where the Azure SQL Server database is running, from step 1.
    • Azure_SQL_Server_Username is the name of the existing Azure SQL Server username, from step 1.
    • Azure_SQL_User_Password is the password for the Azure SQL Server user to connect to the database, from step 1.
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database, from step 1.
  12. Test the newly created database link by running a query. For example:
    SQL> select count(*) from 
some_table@DatabaseLink_Name_From_Previous_Step;
    The variables in the example have the following values:
    • some_table is the name of a table in your database to query.
    • DatabaseLink_Name_From_Previous_Step is the database link name from step 11.

Install Oracle Database Gateway Server

These steps describe how to install the Oracle Database Gateway Server software on an OCI Compute instance running Oracle Linux 7.9.

  1. Use yum to install Oracle prerequisite packages.
    [root@dbgw ~]# yum install oracle-database-preinstall-19c
  2. Create user, oracle, and dba group.
    [root@dbgw ~]# useradd -u 1001 -g dba -d /home/oracle -m -s /bin/bash oracle
[root@dbgw ~]# groupadd -g 1001 dba
  3. Create installation directory for Oracle Database Gateway 19.3.
    [root@dbgw ~]# mkdir /u01
[root@dbgw ~]# chown oracle:dba /u01
  4. Modify local firewall to allow connections to port 1521.
    [root@dbgw ~]# firewall-cmd --get-active-zones
public
interfaces: eth0
[root@dbgw ~]# firewall-cmd --zone=public --add-port=1521/tcp –permanent
[root@dbgw ~]# firewall-cmd --reload
[root@dbgw ~]# firewall-cmd --zone=public --list-ports
1521/tcp
[root@dbgw ~]# systemctl stop firewalld
[root@dbgw ~]# systemctl start firewalld
  5. Copy the Oracle Database Gateway 19.3 zip files you downloaded earlier to the /home/oracle directory in the linux VM, then unzip the Oracle Database Gateway 19.3 zip file.
    [oracle@dbgw ~]$ unzip LINUX.X64_193000_gateways.zip
  6. Create an installation response file from the included template.
    [oracle@dbgw response]$ cd /home/oracle/gateways/response/
[oracle@dbgw response]$ cp tg.rsp/home/oracle/tg-jc.rsp
  7. Modify response file with the following options.
    UNIX_GROUP_NAME=oracle
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_HOME=/u01/app/oracle/product/gateway
ORACLE_BASE=/u01/app/oracle
oracle.install.tg.msqlConStr={Fully_Qualified_Server_Name_From_Step_1,1433,
Database_Name_From_Step_1,Database_Name_From_Step_1}
    The variables in the example have the following values:
    • Fully_Qualified_Server_Name_From_Step_1 is the fully qualified hostname for SQL server where the Azure SQL Server database is running, from step 1.
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database, from step 1.
  8. Run silent install to install Oracle Database Gateway 19.3.
    [oracle@dbgw gateways]$./runInstaller -silent -noconfig -
responseFile /home/oracle/tg-jc.rsp
  9. Configure Oracle home and path for user oracle.
    [oracle@dbgw ~]$ tail -6 .bash_profile
ORACLE_HOME=/u01/app/oracle/product/gateway
export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin
export PATH
[oracle@dbgw ~]$

Configure Oracle Database Gateway for SQL Server

After Oracle Database Gateway is installed on the OCI Compute instance running Linux 7.9, it must be configured to connect to the Azure SQL Server database using the Azure SQL Server private endpoint.

  1. Add the Azure SQL Server database information to the Oracle Database Gateway for SQL Server init file running on the OCI Compute instance. This information tells the Oracle Database Gateway how to connect to the Azure SQL Server database.
    For example:
    [oracle@dbgw ~]$ cd $ORACLE_HOME/dg4msql/admin
[oracle@dbgw admin]$ cp initdg4msql.ora 
initDatabase_Name_From_Step_1.ora
[oracle@dbgw admin]$ cat initDatabase_Name_From_Step_1.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
# HS init parameters
#
HS_FDS_CONNECT_INFO=Fully_Qualified_Server_Name_From_Step_1:1433//Database_Name_From_Step_1
# alternate connect format is hostname/serverinstance/databasename
HS_DB_NAME=Database_Hostname_From_Step_1
HS_DB_DOMAIN=Database_Domain_Name #usually database.windows.net
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_ENCRYPT_SESSION=SSL
HS_FDS_VALIDATE_SERVER_CERT=0
    The variables in the example have the following values:
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database, from step 1.
    • Fully_Qualified_Server_Name_From_Step_1 is the fully qualified hostname for SQL server where the Azure SQL Server database is running, from step 1.
    • Database_Hostname_From_Step_1 is the hostname of the database, from step 1.
    • Database_Domain_Name is the name of the domain the database resides, usually database.windows.net.
    Note the parameters HS_FDS_ENCRYPT_SESSION and HS_FDS_VALIDATE_SERVER_CERT are required in order for the connection between the Oracle Database Gateway Server and the Azure SQL Server to be encrypted.
  2. On the Oracle Database Gateway server, modify the tnsnames.ora to resolve to the Gateway Services provided by this gateway, as follows:
    [oracle@dbgw ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
Database_Name_From_Step_1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= 
OCI_Compute_Instance_Hostname_From_Step_2)(PORT=1521))
(CONNECT_DATA=(SID=Database_Name_From_Step_1))
(HS=OK)
)
    The variables in the example have the following values:
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database, from step 1.
    • OCI_Compute_Instance_Hostname_From_Step_2 is the hostname of the OCI Compute instance from step 2.
  3. On the Oracle Database Gateway Server, modify the listener.ora to include the Oracle Database Gateway information.
    [oracle@dbgw-jc admin]$ cat ../../network/admin/listener.ora
# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=
OCI_Compute_Instance_Hostname_From_Step_2)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
 (SID_NAME=Database_Name_From_Step_1)
 (ORACLE_HOME=/u01/app/oracle/product/gateway)
 (PROGRAM=dg4msql)
)
)
    The variables in the example have the following values:
    • OCI_Compute_Instance_Hostname_From_Step_2 is the hostname of the OCI Compute instance from step 2.
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database from step 1.
  4. Start the listener in the Oracle Database Gateway server running in the OCI Compute instance and test connectivity to the gateway.
    For example:
    [oracle@dbgw admin]$lsnrctl start
[oracle@dbgw admin]$ tnsping Database_Name_From_Step_1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 16-SEP-2022 21:18:39
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST= 
OCI_Compute_Instance_Hostname_From_Step_2)(PORT=1521)) 
(CONNECT_DATA=(SID=Database_Name_From_Step_1)) (HS=OK))
OK (0 msec)
[oracle@dbgw-jc admin]$
    The variables in the example have the following values:
    • Database_Name_From_Step_1 is the name of the Azure SQL Server database from step 1.
    • OCI_Compute_Instance_Hostname_From_Step_2 is the hostname of the OCI Compute instance from step 2.