Note:

This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Set up Real-Time Data Replication using OCI GoldenGate from On-Premises Microsoft SQL Server to Oracle Autonomous Data Warehouse

Introduction

Oracle Cloud Infrastructure GoldenGate (OCI GoldenGate) is a fully managed service that helps data engineers move data in real-time, at scale, from one or more data management systems to OCI databases. Design, run, orchestrate, and monitor data replication tasks in a single interface without having to allocate or manage any compute environments. OCI GoldenGate supports several sources and targets including Microsoft SQL Server, MySQL, PostgreSQL and Oracle database service.

In this tutorial, we will guide you how to set up real-time data replication from on-premises Microsoft SQL Server database to Oracle Autonomous Data Warehouse (ADW) database using OCI GoldenGate.

Image showing Architecture

Limitations

Few Data types present in Microsoft SQL Server are not available in Oracle and equivalent data type present in Oracle has to be used for table Data Definition Language (DDL). For example, VARBINARY data type in Microsoft SQL Server and BLOB data type in Oracle.
DDL replication limitations.

Objectives

Set up real-time data replication from on-premises Microsoft SQL Server database to ADW database using OCI GoldenGate.

Prerequisites

OCI GoldenGate 23ai for Non-Oracle databases (SQL Server).
OCI GoldenGate 23ai for Oracle databases.
SQL Server database (in this case, we will use 2016).
Oracle Autonomous Data Warehouse 23ai.
Review supported data types. For more information, see SQL Server: Supported Data Types, Objects, and Operations.
OCI FastConnect/IpSec/VPN, ingress rules and updated security lists to allow communication between source on-premises Microsoft SQL Server database , target Oracle Autonomous Data Warehouse database, OCI Bastion and OCI GoldenGate.

Task 1: Prepare Microsoft SQL Server for OCI GoldenGate

The OCI GoldenGate Microsoft SQL Server deployment type includes support for the following databases:

Microsoft Azure SQL Database (target only).
Microsoft Azure SQL Server Managed Instance (source/target).
Microsoft SQL Server 2012, 2014, 2016, 2017, 2019 (source/target).
Amazon Relational Database Service (Amazon RDS) for SQL Server 2012, 2014, 2016, 2017, 2019 (source/target).

Follow the steps:

The source usually runs on the default port 1433 but SQL Server can be running in a different port, to check the port run the following command on SQL Server.
```
USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO
```

Create database user.

CREATE login GGADMIN with password = '<<Password>>'
Create user GGADMIN for login GGADMIN
ALTER SERVER ROLE sysadmin ADD MEMBER GGADMIN

Create database.
```
Create database SRC_OCIGGLL
```
Create schema.
```
Create schema SRC_OCIGGLL
```

Enable Change Data Capture (CDC).

#SQL Server instance
EXECUTE sys.sp_cdc_enable_db

Purge CDC staging table.

#SQL Server instance
EXECUTE sys.sp_cdc_drop_job 'cleanup'

Task 2: Create OCI GoldenGate Deployment and Set up Source and Target Connections

Log in to the OCI Console and click Create GoldenGate deployments.
Click Create deployment and enter the required information as shown in the following images.

Note: In the same way, create OCI GoldenGate deployment for Oracle.
Add Source and Target connection details on the deployment page.

Note: The following images show the reference to create OCI GoldenGate generic connections which allows communication between two different deployments.
To assign the connections to the OCI GoldenGate deployment, click the connection name and assign deployment.

Note: The following images shows how we have to assign generic OCI GoldenGate connection to other deployments and establish connection between them to trail file flow using distribution path.
In the OCI GoldenGate Console of source/target deployment under User Administration, we have to create a new OCI GoldenGate user to establish communication between the two deployments.

Task 3: Create Extract and Replicat Processes

In the OCI GoldenGate Console, add Trandata for the schemas.
In the OCI GoldenGate Console, create Initial Load Extract and CDC Extract for Microsoft SQL Server 2016 database server.

Task 4: Create Distribution Path to Move the Trail Files Between Deployments

Create Target-Initiated Path from target OCI GoldenGate deployment and enter the required information as shown in the following image.
Enter the default values or change as per your requirement.
Click Create path and run.

Target-initiated path for initial load extract is created and ready for pushing the trail files from Microsoft SQL Server deployment to Oracle deployment.
Similarly, create a distribution path for CDC extract and push the trail files from Microsoft SQL Server deployment to Oracle deployment.

Target ADW already has the GGADMIN user, we have to unlock the user and create the application schemas for replication.

Note: Schema and Tables DDL/Metadata eligible for real time data replication between source and Target has to be manually created and made ready before starting the replicat process.
In the target Oracle deployment create the initial load and CDC replicat with trail files pushed from Microsoft SQL Server deployment.
Once initial load sync up is completed, we can start the CDC replicat and allow the real time data replication to flow between Microsoft SQL Server database and Oracle Autonomous Data Warehouse database.

Acknowledgments

Author - Saravanadurai Rajendran (Lift Implementation Expert - Database)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

Title and Copyright Information

Set up Real-Time Data Replication from On-Premises Microsoft SQL Server Database to ADW Database using OCI GoldenGate

G36145-02