Edward Whalen
Chris Tijerina

2018-06-10

When configuring Data replication tasks for critical systems, it is considered a best practice to configure for high availability in both source and target systems when possible.  One common platform hosting critical data today is Microsoft SQL Server 2016.  One method to achieve high availability for GoldenGate in a Windows environment is creating a Windows 2016 cluster.    Configuring GoldenGate in a Windows 2016 cluster can be challenging without the proper guidance, this paper will provide you with the basic process for integrating a Windows 2016 cluster into your data ecosystem.

Pre-requisites

There are a few prerequisites necessary to build the GoldenGate Cluster.

Microsoft Failover Cluster Setup

A Microsoft Windows 2016 failover cluster must be created.  This failover cluster must have a shared disk and an IP address.

Install Prerequisite software

For GoldenGate 12.3.0.1.3 you must install the Visual C++ 2010 Redistributable Package.

Before you install Oracle GoldenGate on a Windows system, install and configure

the Microsoft Visual C ++ 2010 SP1 Redistributable Package. Make certain that

you install the SP1 version of this package. This package installs runtime

components of Visual C++ Libraries that are required for Oracle GoldenGate

processes. To download this package, go to

https://www.microsoft.com/en-us/download/details.aspx?id=13523

Starting with GoldenGate 12.3.0.1.5 an additional package has to be installed.  This is the Visual C++ 2013 Redistributable Package.

Before installing Oracle GoldenGate on a Windows system, install the Microsoft Visual C ++ 2010 SP1 Redistributable Package and the Microsoft Visual C++ 2013 Redistributable Package (vcredist_x64.exe). These packages install the runtime components of Visual C++ Libraries that are required for Oracle GoldenGate processes.

To download the Visual C++ 2010 SP1 package, go to:

https://www.microsoft.com/en-us/download/details.aspx?id=13523

To download the Visual C++ 2013 package, go to:

https://www.microsoft.com/en-us/download/details.aspx?id=40784

.

SQL Server Client Tools Connectivity features must be installed on the server

where Oracle GoldenGate is to be installed. These features are normally installed

by default when you install an instance of SQL Server. However, for a Windows

server that is to be used for a remote Replicat, a Classic Extract running off the

database server in an Archived Log Mode, or a remote CDC Extract, you can

obtain the required client connectivity drivers through the SQL Server installation

media or from the following links:

Microsoft SQL Server 2008 SP4 Feature Pack:

https://www.microsoft.com/en-us/

download/details.aspx?id=44277

Microsoft SQL Server 2008 R2 SP3 Feature Pack:

https://www.microsoft.com/en-

us/download/details.aspx?id=44272

For SQL Server 2012, 2014, and 2016, install the Microsoft SQL Server 2012

Native Client, which is part of the Microsoft SQL Server 2012 Feature

Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=29065

SQL Server Management Studio

Install SQL Server Management Studio (2017) on both nodes of the cluster.

Install GoldenGate software in the shared disk

GoldenGate should be installed (unzipped) on the shared disk.

Edit the GLOBALS file and add the following line:

MGRSERVNAME GGMGR

GGMGR will be the name of the service that is created.  If there is more than one manager on the server this should be more distinctive, such as GGMGR1, GGMGR2, etc.  If this is not present in the GLOBALS file the default name is GGSMGR.

Edit the mgr parameter file and add the following line:

ACCESSRULE PROG *, ALLOW

If you do not add the accessrule you will be unable to start the extract or replicat.

As Administrator (on both nodes while they are primary) in a cmd prompt run install addservice from the GoldenGate home directory.  Validate that the service has been created.

Note:  Even though the creation of the GoldenGate cluster apparently copies the registry entry over, something in the install addservice command makes it work.  If you only run install addservice on one node, that’s the only node that it will start on.

From the Failover Cluster Manager select Configure Role.  Select Generic Service.

Select the GGMGR service and click Next.

Give it the IP name/Address that has been allocated for this cluster service.

Note:  It will try to use DHCP for this name.  This will have to be fixed later.

Select the storage that has already been allocated.

From the registry screen click Add, then type in the Registry Key.  It is SYSTEM\CurrentControlSet\Services\GGMGR.

Click OK, then Next to see the Confirmation screen.  Click Next.

You will then see the Summary screen.

Click Finish.

You will see the Role.  Highlight the Role to see details.

Expand the Server Name and IP address.  Because it tried to use DHCP the IP address is incorrect.

Right-Click and select Properties.  Change the Name and set the IP address to be static to the correct address.

Click OK.

The mssqlggmgr Role should now start on both nodes.  If it doesn’t start on node 2, re-run install addservice to fix the registry entry.

Right-Clicking on the mssqlggmgr Role and selecting Move->Select Node you can move the cluster resource to either node.  During a failover this will be done automatically.

Final Steps

You must delete or disable the SQL Server CDC cleanup job and replace it with the Oracle cleanup job.  The SQL Server CDC cleanup job is deleted by running the following SQL in the database that is being replicated:

EXECUTE sys.sp_cdc_drop_job ‘cleanup’

The Oracle cleanup job is created by running ogg_cdc_cleanup_setup.bat from the GGHOME directory.  This bat file does not accept Windows authentication and should be run with a SQL Server account.  Add the Oracle cdc cleanup job from the hub server by running the following command from the GoldenGate home directory.

ogg_cdc_cleanup_setup.bat createJob <GoldenGate User> <password> <database> <server> <GoldenGate schema>

ogg_cdc_cleanup_setup.bat createJob ggadmin xxxxxx tpcc mssql03 ggadmin

D:\gghome>ogg_cdc_cleanup_setup.bat createJob ggadmin gg123 <database> <host> ggadmin

Oracle GoldenGate CDC cleanup job setup script

==============================================

Command: createJob

Oracle GoldenGate CDC Cleanup job and its relevant table(s) and procedure(s) are created.

In the SQL Server Agent Jobs list you should see OracleGGCleanup_<database>_Job.  This job can and should be tuned.

If you are running with Windows authentication the Oracle cleanup job is created by running ogg_cdc_cleanup_setup_win.bat from the GGHOME directory.

This command file is created by copying the ogg_cdc_cleanup_set.bat file and removing all references to username and password.  With these changes sqlcmd.exe will be called without those parameter thus running in Windows authentication mode.

This bat file does accept Windows authentication.  Add the Oracle cdc cleanup job from the hub server by running the following command from the GoldenGate home directory.

ogg_cdc_cleanup_setup_win.bat createJob <database> <server> <GoldenGate schema>

ogg_cdc_cleanup_setup.bat createJob tpcc mssql03 ggadmin

D:\gghome>ogg_cdc_cleanup_setup.bat createJob <database> <host> ggadmin

Oracle GoldenGate CDC cleanup job setup script

==============================================

Command: createJob

Oracle GoldenGate CDC Cleanup job and its relevant table(s) and procedure(s) are created.

In the SQL Server Agent Jobs list you should see OracleGGCleanup_<database>_Job.  This job can and should be tuned.

Summary

Following these steps will setup the cluster GoldenGate Manager.  Once the Manager starts, with AUTOSTART and AUTORESTART set, the extracts and replicats will start automatically.