Oracle GoldenGate Classic for SQL Server

6 Oracle GoldenGate Classic for SQL Server

Parent topic: Using Oracle GoldenGate Classic on Oracle Cloud Marketplace

Deploying Oracle GoldenGate Classic Architecture for SQL Server Platforms

Using Oracle GoldenGate Classic Architecture on Marketplace, you can deploy and manage your deployment from a single location.

Here are the prerequisites which are required before deployment:

Oracle Cloud Account
Access to compute node resources within Oracle Cloud Tenant

Parent topic: Oracle GoldenGate Classic for SQL Server

Finding Oracle GoldenGate for Non-Oracle (SQL Server) within the Oracle Cloud Marketplace

The following steps help you to deploy Oracle GoldenGate for Non-Oracle (SQL Server) from Oracle Cloud Marketplace:

Log in to Oracle Cloud Marketplace.
From the Oracle Cloud Marketplace home page, use the search box under Applications and search for the keyword GoldenGate.
From the Search Results, select Oracle GoldenGate for Non-Oracle.

Parent topic: Oracle GoldenGate Classic for SQL Server

Launching Oracle GoldenGate Classic for Non-Oracle (SQL Server)

The following steps help you to launch Oracle GoldenGate Classic for Non-Oracle (SQL Server) on Oracle Cloud Marketplace:

From the application page, select Get App.
Select OCI Region or Log in using your OCI account login credentials.
1. OCI Region – Select the OCI Region for which you want to install the compute node.
2. Click Sign In.
In the Sign In to the Cloud Tenant page, provide the OCI tenant details.
You may be asked to sign in to your identity provider.
On the Oracle GoldenGate for Non-Oracle (SQL Server) page, find the selection box and provide the following information:
1. Select Version – It provides a list of versions that are available in the listing. It is set to Oracle GoldenGate for Non-Oracle (SQL Server) by default.
2. Select Compartment – Specifies the compartment where the compute node will be built. It is generally the location that you have access to build the compute node.
3. Terms of Use – This check box is selected by default. Oracle recommends to review the licenses before proceeding with the instance creation.
4. Launch Stack – It launches the stack in the OCI environment.
Fill in the required Stack information:
- Name - Name of the Stack. It has a default name and provides a date time stamp. You can edit this detail, if required.
- Description - Description that you provide while creating the Stack.
- Create In Compartment – It defaults to the compartment you have selected on the Oracle GoldenGate 19c for Oracle page.
- Tags (optional) – Tags are a convenient way to assign a tracking mechanism but are not mandatory. You can assign a tag of your choice for easy tracking. You have to assign a tag for some environments for cost analysis purposes.
- Click Next.
Fill in the required details to Configure Variables. This information is required to build the compute node for for Non-Oracle (SQL Server):
1. Name for New Resources -
  - Display Name - Display Name used to identify all new OCI resources.
  - Host DNS Name – Domain Name Service for the new compute node.
2. Network Settings -
  - Create New Network – Select this check box, if you wish to create a new network resource.
    If you select this check box, the Create New Network wizard appears allowing you to add and edit the new network information such as:
    - Network Compartment
    - New VCN DNS Name
    - New VCN CIDR
    - New Subnet DNS Name
    - New Subnet CIDR
    If you do not select this check box, the Create New Network wizard does not appear and the compute node is created with the existing network options in the VCN.
  - Network Compartment (optional) – Compartment for new or existing network resources.
  - VCN (optional) – Existing VCN to use for the newly created instance if you are not creating a new network.
  - Subnet (optional) – Existing subnet to use for the newly created instance if you are not creating a new network. The subnet that you have selected must match the same Availability Domain set in the Instance Settings.
3. Instance Settings -
  - Availability Domain – It specifies the availability domain for the newly created Oracle GoldenGate Instance. It must match the Subnet that you have selected in the Use Existing Network settings.
  - Compute Shape – Shape of new compute instance. Supported shapes are VM.Standard2.4, VM.Standard2.8, VM.Standard2.16, VM.Standard2.24
  - Assign Public IP – Indicates if the new VM should have a public IP address.
  - Custom Volume Sizes – Use this checkbox to customize the size of new block storage volumes.
4. SSH Public Key - Public Key for allowing SSH access as the ‘opc’ user.
Click Next.
On the Review page, review the information you provided then click Create.
After clicking Create, you will be taken to the Stacks page. You can monitor the creation of the compute node using this page.
Upon completion, you can view the Oracle GoldenGate for Non-Oracle (SQL Server) compute node under Compute -> Instances.

Parent topic: Oracle GoldenGate Classic for SQL Server

Connecting to Oracle GoldenGate Classic for Non-Oracle (SQL Server) Compute Node

To access your new Oracle GoldenGate deployment, log in to the compute node and access Oracle GoldenGate Software Command Interface (GGSCI). For this, you need to identify the public IP address of the compute node where Oracle GoldenGate Classic is running. The following steps help you to identify the public IP address:

Log in to your Oracle Cloud Console.
Select Compute -> Instances.
Select the hyperlink name of the compute node. The public IP Address is listed under Primary VNIC Information.
To access the compute node where Oracle GoldenGate is running, connect as the opc account using SSH. For more information on how to access a node using the opc account refer to Connecting to an Instance.

A public SSH key is specified as a part of the deployment process and you can use the private key when you are connecting to the Oracle GoldenGate instance. The following example illustrates how you connect to the Oracle GoldenGate compute node:

ssh -i <private-key-filename> opc@<public-id-address>

Parent topic: Oracle GoldenGate Classic for SQL Server

Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

After deploying Oracle GoldenGate Classic for Non-Oracle (SQL Server) on Oracle Cloud Marketplace, you can access the latest release of Oracle GoldenGate Classic for Non-Oracle (SQL Server).

Before you can start using Oracle GoldenGate Classic for Non-Oracle (SQL Server), there are a few tasks that you must perform to ensure that your environment is complete and ready to replicate your data. Before you begin data replication, you must perform the following tasks:

Topics:

Parent topic: Oracle GoldenGate Classic for SQL Server

Establishing Connectivity

The Oracle GoldenGate Classic for Non-Oracle (SQL Server) on Oracle Cloud Marketplace compute node acts as a hub where you can manage your connections to source and target SQL Server databases. To establish connectivity from an Oracle GoldenGate Classic for Non-Oracle (SQL Server) compute node to your on-premises SQL Server database, use the following options:

IPSec VPN
FastConnect

For establishing an IPSec VPN with OCI, refer to IPSec VPN documentation. For establishing a FastConnect connection, refer to the FastConnect documentation.

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Oracle GoldenGate for SQL Server Requirements

Before you can begin replicating data, for a SQL Server database, review the instance, database and user requirements available in Installing Oracle GoldenGate for SQL Server Databases chapter of Installing Oracle GoldenGate Guide and Using Oracle GoldenGate for SQL Server chapter of Using Oracle GoldenGate for Heterogeneous Databases. These sections contain important information regarding database requirements and supported features.

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Creating Database User and Schema

To support remote capture and apply for a SQL Server database, you must create a SQL Server login and schema.

For capturing data from a SQL Server source database, create a SQL Server login and schema that can be used by Oracle GoldenGate, and grant sysadmin access privileges for login. Oracle GoldenGate requires sysadmin access privileges to perform several prerequisites, but you can set the account to dbowner afterwards,which is required for the capture process to run. Create a schema in the database to be used as the source database.

Example for a Source Database :

CREATE LOGIN [oggsourceuser] WITH PASSWORD=N'password';
ALTER SERVER ROLE [sysadmin] ADD MEMBER [oggsourceuser];
USE [source_dbname];
CREATE SCHEMA [oggsourceschema];

If you are applying data to a SQL Server target database, create a SQL Server login that has dbowner access privileges within the target database, as well as a schema to be used for a Replicat’s checkpoint table.

Example:

CREATE LOGIN [oggtargetuser] WITH PASSWORD=N'password';
USE [target_dbname];
ALTER ROLE [db_owner] ADD MEMBER [oggtargetuser];
CREATE SCHEMA [oggtargetschema];

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Installing Microsoft ODBC Drivers for Linux

The Microsoft ODBC Drivers for Linux are required to connect to a remote source or target SQL Server database that runs on Windows. The following tasks are required to install the Linux drivers.

Edit the file /etc/passwd, to grant temporary shell access to the root user.
```
$ sudo vi /etc/passwd
```
In the file /etc/passwd, change the value for the root user from /usr/sbin/nologin to /bin/bash. Save and close the file.

Using Microsoft’s RedHat Enterprise Server installation instructions for adding the ODBC Drivers for Linux, perform the following steps with default values by answering 'y' when prompted.

$ sudo su

$ #RedHat Enterprise Server 7
$ curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

$ exit
$ sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
$ 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

After installing the Linux drivers, you can reset the original shell access values for the root user.
```
$ sudo vi /etc/passwd
```
Change the value for the root user from /bin/bash to /usr/sbin/nologin. Save and close the file.

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Configuring ODBC Connectivity

Create and install an ODBC connection template file that contains the database connection string by using the login that you created for Oracle GoldenGate. Ensure that the TCP/IP protocol is enabled for the SQL Server instance as Oracle GoldenGate will connect over TCP/IP for establishing the connection.

Create a template file for your data source:
```
$ vi odbc_template_file.ini
```
Describe the data source in the template file. In the following example, myserver_sql2017_source is used as the DSN name with DBLOGIN and SOURCEDB/TARGETDB to connect to the database.
```
[myserver_sql2017_source] 
Driver = ODBC Driver 17 for SQL Server
Server = myserver,1433
Database = dbname
User = oggsourceuser
Password = password
```
Save and close the file.

Install the data source using the command.

$ odbcinst -i -s -f odbc_template_file.ini

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Creating GLOBALS File and Starting GGSCI

Oracle GoldenGate Classic for Non-Oracle (SQL Server) allows you to quickly access the GoldenGate Service Command Interface (GGCSI) and is preconfigured with a running Manager process. After logging in to the compute node, you can find GGCSI from the mssql directory (/home/opc/mssql).

Create a GLOBALS file (with no extension) in the Oracle GoldenGate installation directory.
```
$ cd ~/mssql
$ vi GLOBALS
```
Add the following (minimum required) parameter to the GLOBALS file, substituting the correct value for the schema that you created earlier in the database:
```
GGSCHEMA oggsourceschema
```
Save and close the GLOBALS file.
To start GGSCI, execute the following command:
```
$ ./ggsci
```

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Enabling Supplemental Logging for a Source SQL Server Database

You must enable the database and tables for SQL Server Change Data Capture, to capture transactional data from a source SQL Server database. Perform the following steps to enable database and table level Change Data Capture and install the Oracle GoldenGate CDC cleanup tasks.

From GGSCI, connect to the source database with a SQL Server login that has sysadmin access privileges.

Example:

GGSCI> DBLOGIN SOURCEDB myserver_sql2017_source USERID oggsourceuser PASSWORD password
GGSCI> ADD TRANDATA dbo.table1
GGSCI> ADD TRANDATA dbo.table2

Using SQL Server Management Studio, drop the SQL Server CDC Cleanup job by running the following against the database. Alternatively, you can manually disable cdc.dbname_cleanup job within SQL Server Management Studio.
```
exec sys.sp_cdc_drop_job N’cleanup’;
```

Execute the shell script to install the Oracle GoldenGate CDC. cleanup tasks.

Syntax:

 ogg_cdc_cleanup_setup.sh createJob userid password dbname server,port ggschema

Example:

GGSCI> shell ./ogg_cdc_cleanup_setup.sh createJob oggsourceuser password source_dbname myserver,1433 oggsourceschema

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Configuring and Creating a Remote Extract for SQL Server

To capture transactional data from a source SQL Server database, you must configure a capture process, also known as an Extract. To build an Extract, you can perform the following steps with Oracle GoldenGate for Non-Oracle.

Create an Extract parameter file.

GGSCI> EDIT PARAMS extsql

Sample Extract parameter file:

EXTRACT extsql
SOURCEDB myserver_sql2017_source USERID oggsourceuser PASSWORD password
EXTTRAIL ./dirdat/et
TABLE dbo.table1;
TABLE dbo.table2;

Save and close the file.

Add the Extract and Extract’s local trail.

Note:
While adding the Extract with the BEGIN NOW option, ensure to check if both the database server and the server running Oracle GoldenGate have the same system time.
```
GGSCI> ADD EXTRACT extsql, TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/et, EXTRACT extsql
```

Verify the connectivity to the SQL Server source database and start the Extract.

GGSCI> DBLOGIN SOURCEDB myserver_sql2017_source USERID oggsourceuser PASSWORD password
GGSCI> START EXTRACT extsql

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)

Configuring and Creating a Remote Replicat for SQL Server

The apply process for replication, also known as Replicat, is used to deliver data to a target database. To build a Replicat, perform the following steps with Oracle GoldenGate for Non-Oracle.

From GGSCI, log in to the target database and create a checkpoint table.

GGSCI> DBLOGIN SOURCEDB myserver_sql2017_target USERID oggtargetuser PASSWORD password
GGSCI> ADD CHECKPOINTTABLE oggtargetschema.oggcheck

Create a Replicat parameter file.

GGSCI > EDIT PARAMS repsql

Sample Replicat parameter file:

REPLICAT repsql
TARGETDB myserver_sql2017_target USERID oggtargetuser PASSWORD password
MAP dbo.table1, TARGET dbo.table1;
MAP dbo.table2, TARGET dbo.table2;

Save and close the file.

Add and start the Replicat.

GGSCI> ADD REPLICAT repsql, EXTTRAIL ./dirdat/et, CHECKPOINTTABLE oggtargetschema.oggcheck
GGSCI> START REPLICAT repsql

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (SQL Server)