Configuring GoldenGate For SQL Server and load data into Autonomous Database

Eloi Lopes

5 min readJun 22, 2022

Oracle GoldenGate is a comprehensive software for enabling the replication of data in heterogeneous data environments.

In this article, I’m going to show you how you can load data from a SQL Server into an Autonomous Data Warehouse.

Pre-requisites

Oracle GoldenGate 21c for Non-Oracle Databases (SQL Server)
Oracle Cloud Infrastructure (OCI) GoldenGate deployment
SQL Server database (in my case I used 2019)
Autonomous Data Warehouse

Configuring GoldenGate for SQL Server

On GoldenGate instance, apply this configuration:

sudo vi /etc/passwd#Edit the file and replace this line
#root:x:0:0:root:/root:/usr/sbin/nologin
By this one:
root:x:0:0:root:/root:/bin/bashsudo sucurl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repoexitsudo yum remove unixODBC-utf16 unixODBC-utf16-devel
sudo ACCEPT_EULA=Y yum install msodbcsql17
sudo ACCEPT_EULA=Y yum install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc#Edit the file again a put the default value
sudo vi /etc/passwd
root:x:0:0:root:/root:/usr/sbin/nologin

Creating the ODBC file:

vi odbc_template_file.ini#content of ODBC[sqlserver2019]            
Driver = ODBC Driver 17 for SQL Server            
Server = <IP or Hostname>,1433            
Database = DBEloi            
User = oggadmin            
Password = <password>odbcinst -i -s -f odbc_template_file.ini

Usually the default port is 1433 but SQL Server can be running in a different port, to check the port run this on SQL Server:

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on' 
GO

Preparing SQL Server

Create schema:

CREATE SCHEMA ggadmin;

Add sysadmin role to oggadmim user:

Enabling CDC

#SQL Server instance
EXECUTE sys.sp_cdc_enable_db

Purge CDC Staging table:

#SQL Server instance
EXECUTE sys.sp_cdc_drop_job 'cleanup'

Creating GoldenGate CDC Cleanup job:

oggadmin is the database user
DBEloi is the database
ggadmin is the schema

#From GG instancecd /u02/deployments/Marketplace/etc/conf/ogg
./ogg_cdc_cleanup_setup.sh createJob oggadmin <password> DBEloi <ip or hostname>,<port> ggadmin

Access to GoldenGate for SQL Server, add a new credential and make sure the DNS has the same name as you defined previously on ODBC file:

In case you have this error:

Make sure you have the ports open at VCN and SQL Server instance. Also, check of the protocol TCP/IP is enabled.

If not, enable it and restart the SQL Server Configuration manager:

Add Trandata:

Creating an Extract:

Creating distribution path

We need to set up a secure connection between GoldenGate SQL Server and OCI GoldenGate. For that, we need to have the OCI GoldenGate certificate.

Go to your OCI GoldenGate instance and download the certificate:

Duplicate the file and change the extension from .cer to .pem

Go to Service Manager on GoldenGate for SQL Server and click on certificate manager:

Upload the OCI GG certificate (.pem):

Now, go to OCI GoldenGate and create a new user to be used for Distribution Path. Choose a username and select the role operator:

On your GoldenGate for SQL Server add a new credential using the same username and password you put previously:

Go to Distribution Server and create a new path. All parameters for target are the ones you defined previously (except trail name). Click create and run.