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/bash
sudo 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.

Creating Replicat for Autonomous Data Warehouse

Go to OCI GG and add a new replicat:

Make sure to put the same Trail that has been defined on Distribution Path:

Parameters:

Replicat, up and running:

I hope this can help you. If you have any doubt, please reach out to me through LinkedIn or Medium.

References

--

--

Eloi Lopes

Opinions expressed are solely my own and do not express the views or opinions of my employer Oracle. https://www.linkedin.com/in/eloilopes/