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

Load Data Using Oracle Database Gateway Server Running in Azure VM

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 data from an Azure SQL Server using a database link via Oracle Database Gateway for SQL Server software running in an Azure virtual machine.

Architecture

This architecture uses Oracle Database Gateway for SQL Server running in an Azure virtual machine 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-azure-vm.png follows
Description of the illustration exadata-db-azure-odsa-azure-vm.png

exadata-db-azure-odsa-azure-vm.zip

Note the following about this architecture:

  • Oracle Database Gateway for SQL Server software can run on an Azure virtual machine. The number of CPUs, memory, and disk size must be assigned to the Azure virtual machine according to the Oracle Database Gateway for SQL Server requirements and sizing guidelines. See System Requirements for Oracle Database Gateway for SQL Server below for more information.
  • Oracle Exadata Database Service on Dedicated Infrastructure needs to resolve the hostname and IP address of the Azure virtual machine running Oracle Database Gateway. 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:

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

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, modify DNS to resolve to Azure endpoint and Azure VM.
Azure Virtual Machine: root Create oracle 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 Azure Virtual Machine

Running the Oracle Database Gateway for SQL Server in an Azure virtual machine is ideal for those customers who are familiar with Azure services, but not very familiar OCI services.

Advantages of Oracle Database Gateway Running in an Azure VM

  • 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 easier to update and maintain. In addition, the solution might be more affordable, as the Oracle Database Gateway software runs on less expensive Azure 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 Azure SQL Server is encrypted (enforced by Azure SQL Server default configuration).
  • Data type conversion and mapping between Oracle Database and Azure SQL Server Database is automatically managed by Oracle Database Gateway software.
  • Hostname resolution for the Azure SQL Server is automatically managed by Azure at the Azure virtual machine level.

Disadvantages of Oracle Database Gateway Running in an Azure Virtual Machine

  • 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 the Oracle Database Gateway server. Additional configuration in Oracle Database Gateway sqlnet.ora is required to enforce encryption from Oracle Exadata Database Service on Dedicated Infrastructure to the Oracle Database Gateway server.

Configure Oracle Database Gateway Running in Azure Virtual Machine

  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 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 form 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 or 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. Create an Azure virtual machine running Linux 7.9 on the vnet configured for the OracleDB for Azure. This virtual machine will be running the Oracle Database Gateway for SQL Server. Take note of the hostname and IP address (on Azure vnet) assigned to this virtual machine, this information is required to tell the Oracle Exadata Database Service on Dedicated Infrastructure database how to connect to the Oracle Database Gateway Server. Hostname is usually in the form servername.windows.net.
  3. From the OCI main menu select Networking/DNS Management to create a Private DNS Zone. In the new Private DNS Zone, add a record hostname and IP address of the Azure VM created on previous step.
  4. From the OCI main menu select Networking/DNS Management to create a DNS Private View. Add the Private DNS Zone created in the previous step to this newly created DNS Private View, and activate the changes.
  5. From the OCI main menu select Networking/Virtual Client Network (VCN). Select the VCN created when the Oracle Database Gateway Oracle Exadata Database Service on Dedicated Infrastructure service was provisioned (change compartment if required). Click the DNS resolver link, then add the DNS Private View created from previous step to the DNS resolver. This will allow name and IP address resolution from Oracle Exadata Database Service on Dedicated Infrastructure to the Azure VM running Oracle Database Gateway.
  6. Log in to one of the Oracle Exadata Database Service on Dedicated Infrastructure VM Cluster nodes to use nslookup to verify the hostname to IP address resolution is working for the Azure VM that will run Oracle Database Gateway.
    For example:
    [opc@exa_dbnodex ~]$ nslookup servername.windows.net
Server: 169.254.169.254
Address: 169.254.169.254#53
Non-authoritative answer:
Name: servername.windows.net
Address: IP_address_of_Azure_VM_from_step_2
[opc@exa_dbnodex ~]$
    The variables in the example have the following values:
    • servername is the name of the Azure SQL Server private endpoint from step 1.
    • IP_address_of_Azure_VM_from_step_2 is the IP address of the Azure VM from step 2.
  7. Install the Oracle Database Gateway software you downloaded earlier on the new Azure virtual machine.
  8. Configure the Oracle Database Gateway for Azure SQL Server to connect to the Azure SQL Server database using the Azure SQL Server private endpoint.
    Note the Azure virtual machine running Oracle Database Gateway and the Azure SQL Server private endpoint are in the same Azure vnet.
  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:
    [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=Azure_VM_Hostname_From_Step_2)(PORT=15
21))
(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.
    • Azure_VM_Hostname_From_Step_2 is the hostname of the Azure VM from step 2.
  10. Use tnsping to test connectivity between the Oracle Exadata Database Service on Dedicated Infrastructure database and Oracle Database Gateway for SQL Server running in the Azure virtual machine (this connection uses the fast-low latency network link established by OracleDB for Azure between Azure and OCI).
    [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=Azure_VM_Hostname_From_Step_2)(PORT=15
21)) (CONNECT_DATA=(SID=Database_Name_From_Step_1)) (HS=OK))
OK (0 msec)
[oracle@exadbnodex ~]$
    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.
    • Azure_VM_Hostname_From_Step_2 is the hostname of the Azure VM 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 (PDB) 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;
    The variables in the example have the following values:
    • some_table is the name of a table in your database to query.
    • DatabaseLink_Name 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 Azure virtual machine running Oracle Linux 7.9.

  1. Use yum to install Oracle prerequisites 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 Server 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 Azure virtual machine 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:1433//Database_Name_From_Step_1
# alternate connect format is hostname/serverinstance/databasename
HS_DB_NAME=Database_Name_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 hostname of the Azure SQL Server database, from step 1.
    • Fully_Qualified_Server_Name is the fully qualified hostname for SQL server where the Azure SQL Server database is running, from step 1.
    • Database_Domain_Name is the name of the domain the database resides from step 1, 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
Azure_SQL_Server_Database_Name =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST= 
Azure_VM_Hostname_From_Step_2)(PORT=1521))
(CONNECT_DATA=(SID=Azure_SQL_Server_Database_Name))
(HS=OK)
)
    The variables in the example have the following values:
    • Azure_SQL_Server_Database_Name is the name of the Azure SQL Server database from step 1.
    • Azure_VM_Hostname_From_Step_2 is the hostname of the Azure VM from step 2.
  3. On the Oracle Database Gateway Server, modify the listener.ora to include the Oracle Database Gateway information.
    For example:
    [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=
Azure_VM_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:
    • Azure_VM_Hostname_From_Step_2 is the hostname of the Azure VM 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 Azure VM 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= 
Azure_VM_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.
    • Azure_VM_Hostname_From_Step_2 is the hostname of the Azure VM from step 2.