Install Oracle GoldenGate for Microsoft SQL Server (CDC)
Performance Tuning Corporation
Oracle has recently released GoldenGate 12.3. This version of GoldenGate adds two key features for Microsoft SQL Server; CDC capture and remote capture. This paper will show how to install Oracle GoldenGate for Microsoft SQL Server v12.3 using the remote capture option and CDC. For this paper the source will be MS SQL Server 2012 and the target will be SQL Server 2016. I will be using a hub model, where both the extract and replicat will be running on a third server.
For the example used here the following servers will be used:
- mssqlhub02 GoldenGate 12.3 Hub Server
- mssql03 SQL Server 2012 (Source)
- mssql04 SQL Server 2016 (Target)
Note: I will be using the tpcc database that was created by a benchmark program, however, I will be using my own test tables rather than the tpcc tables for this paper.
The GoldenGate processes replicates data from a single database. Thus, if you want to replicate from multiple databases you must setup multiple GoldenGate processes. These processes fall into two categories:
Extract Takes data from the MS SQL Server database (initial extract) and puts it into a GoldenGate trail file. Takes data from a GoldenGate trail file and writes to a remote trail file (pump extract).
Replicat Takes trail file data and submits it to a MS SQL Server database.
The GoldenGate trail file is universal. Data generated from a MS SQL Server database can be processed on any supported platform including Oracle, MySQL, Big Data (Kafka), flat files, etc.. A single trail file can be used to replicate data to multiple targets and data from multiple targets can be replicated to a single target.
SQL Server Setup
For GoldenGate to work you must have installed SQL Server with the replication option, since it will be using SQL Server CDC as part of the replication process. In addition, a GoldenGate login must be created and must be granted sysadmin privileges on the source or target database. The typical user name for this GoldenGate administrator is ggadmin, but you can call it whatever you want.
The ggadmin login should be created and associated with the database that is to be replicated from/to as the db_owner as well as be granted sysadmin privileges. In addition, a ggadmin schema should be created in the source/target database to be used for GoldenGate objects. SQL Server can be setup to used Mixed Mode authentication as shown in this example or Windows authentication. Some prefer to use a domain service account for GoldenGate replication. Either method is fine.
Create the ggadmin user:
Assign the sysadmin Server role:
Map to tpcc db_owner:
Add ggadmin schema to tpcc database:
Repeat these steps on the target database.
These steps complete the initial database setup for MS SQL Server. There are a few steps later in the GoldenGate setup process.
This paper has shown how to properly Install Oracle GoldenGate for Microsoft SQL Server. The example uses MS SQL Server 2012 as the source and 2016 as the target. We could easily do the opposite, or even setup a different database as the target database. Since GoldenGate is heterogeneous the source and target don’t need to be the same version or the same database. By following these steps, your install of Oracle GoldenGate for Microsoft SQL Server will be successful.