Oracle GoldenGate: SQL Server to SQL Server Instantiation

Introduction

The target database instantiation, or initial load, process is key to a successful Oracle GoldenGate implementation. In this article we shall present best practices when instantiating a target Microsoft SQL Server database from a source SQL Server database when implementing Oracle GoldenGate.

Main Article

When planning a target database instantiation, there are several questions you must first ask yourself:

1. Are we taking the source database off-line, or will this be a zero downtime implementation?
2. Are we replicating the entire database, or a subset of tables?
3. What utility will we use to perform the target instantiation; backup/restore, export/import, database copy, Oracle GoldenGate, or some other application?

In this 24/7 world, it is unlikely that we can take our application and database off-line for any length of time. Oracle GoldenGate allows you to implement replication and perform target instantiation while activity is occurring in the source database.

Since we are working in a SQL Server Database environment, the obvious choice is to utilize functionality in Microsoft SQL Server Management Studio (SSMS) as most SQL Server DBAs will be intimately familiar with this utility.

For an Oracle GoldenGate implementation, we need to know details about the last LSN committed in the source database; so we’ll use the SSMS backup and restore for our zero downtime instantiation. If we are taking an outage, we could use the SSMS export/import, or database copy utilities; although, you should be aware of any limitations for those detailed on Microsoft’s Technology Network.

For the purpose of this discussion, we’ll be setting up Oracle GoldenGate replication between a SQL Server 2008 and SQL Server 2014 database, and instantiating the SQL Server 2014 target database, as depicted below. We are assuming that the SQL Server 2008 database has been configured to allow replication by Oracle GoldenGate and Oracle GoldenGate has been installed as specified in the Installing and Configuring Oracle GoldenGate for Microsoft SQL Server reference guide.

OGG for SQL Server Replication

Zero Downtime Implementation and Instantiation

For our zero downtime implementation, we’ll setup a Classic Extract and Extract Data Pump to capture transactional data from the SQL Server 2008 source database and stage it at the target Oracle GoldenGate instance, and a Coordinated Apply for delivery of this data to the SQL Server 2014 target database. My Oracle GoldenGate configuration looks like this:

Clear text passwords are a bad thing, I only show them here for simplicity. For SQL Server, it is best practice to use Windows Authentication. If that is not possible, you should use password encryption functionality provided with the Oracle GoldenGate product.

Source Settings

Classic Extract  Extract Data Pump
extract eljp
sourcedb mss08ljp, userid ggadmin, password Oracle1
tranlogoptions nomanagesecondarytruncationpoint
exttrail ./dirdat/ep
reportcount every 60 seconds, rate
table dbo.*;
extract pljp
rmthost den01eln.us.oracle.com, mgrport 15000, compress
rmttrail ./dirdat/rp
passthru
reportcount every 60 seconds, rate
table dbo.*;

 

Target Settings

Coordinated Apply
replicat rljp
targetdb mss14ljp, userid ggadmin, password Oracle1
reportcount every 60 seconds, rate
usededicatedcoordinationthread
map dbo.orders, target dbo.orders, thread(1);
map dbo.orders_products, target dbo.orders_products, threadrange(2-3);
map dbo.orders_status_history, target dbo.orders_status_history, threadrange(2-3);
map dbo.*, target dbo.*, thread(20);

 

Before starting the target instantiation process, I want to create the source Oracle GoldenGate instance:

Source Oracle GoldenGate Instance

GGSCI (den01eln as ggadmin@MSS08LJP) 3> add extract eljp, tranlog, begin now
EXTRACT added.
GGSCI (den01eln as ggadmin@MSS08LJP) 4> add exttrail ./dirdat/ep, extract eljp
EXTTRAIL added.GGSCI (den01eln as ggadmin@MSS08LJP) 5> add extract pljp, exttrailsource ./dirdat/ep
EXTRACT added.
GGSCI (den01eln as ggadmin@MSS08LJP) 6> add rmttrail ./dirdat/rp, extract pljp
RMTTRAIL added.

Next we need to check the database to ensure there are no active long running transactions. Transactions that were started prior to adding the Classic Extract must either be committed or aborted before starting the SSMS backup.

In GGSCI, execute the STATUS command to get the VAM Read Checkpoint for the Classic Extract:

GGSCI (den01eln as ggadmin@MSS08LJP) 7> info eljp
EXTRACT    ELJP      Initialized   2017-03-30 10:21   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:09:27 ago)
VAM Read Checkpoint  2017-03-30 10:21:00.250000

In SSMS, use the DBCC OPENTRAN command to view running database transactions:

OGGMSS2

In the output shown above, an active transaction started at Mar 30 2017 10:15:00:727 AM is still running. Since the Classic Extract was added at Mar 20 2017 10:21:00.250 AM, the transaction will not be captured by Classic Extract. Therefore, the transaction must either (1) be committed so the data is available in the database for the backup, or (2) aborted and restarted so Classic Extract will capture the data.

After aborting the transaction, there are now no transactions running that were started prior to adding the Classic Extract; all source data will be instantiated to the target either by SSMS back and restore, or by Oracle GoldenGate:

OGGMSS3

In the source Oracle GoldenGate instance, start the Classic Extract and Extract Data Pump. Real-time data will be captured and queued at the target Oracle GoldenGate instance while we instantiate the target database:

GGSCI (den01eln as ggadmin@MSS08LJP) 8> start eljp
Sending START request to MANAGER …
EXTRACT ELJP starting
GGSCI (den01eln as ggadmin@MSS08LJP) 9> start pljp
Sending START request to MANAGER …
EXTRACT PLJP starting
GGSCI (den01eln as ggadmin@MSS08LJP) 10> status eljp
EXTRACT ELJP: RUNNING
GGSCI (den01eln as ggadmin@MSS08LJP) 11> status pljp
EXTRACT PLJP: RUNNING

You are ready to perform the SSMS backup and restore as per Microsoft documentation. When performing the restore, take note of the value Last LSN recorded for the backup set; well need this information when we’re ready to start the Coordinated Apply:

OGGMSS4

If you happen to forget recording this information, you can run the following query against the source or target database:

SELECT bf1.physical_device_name,  bs1.position, bs1.type,
bs1.last_lsn, bs1.backup_start_date, bs1.backup_finish_date
FROM msdb..backupset bs1
inner join
msdb..backupmediafamily bf1
on bf1.media_set_id = bs1.media_set_id
WHERE bs1.database_name = DB_NAME()
AND bs1.type = ‘D’
ORDER BY bs1.backup_start_date DESC

OGGMSS5

When the restore completes, we are ready to activate Oracle GoldenGate replication. Since we started Classic Extract and Extract Data Pump on the source side, all transactional data committed to that database is queued and waiting to be applied on our target GoldenGate instance. Remember that we started Classic Extract before beginning the database backup? In a live environment, it is very likely that Classic Extract captured transactional data that was applied to the target database during the backup and restore. We need to make the Coordinated Apply aware of this.

That is where the backup Last LSN value comes into play.

We will use the backup Last LSN value, with the GGSCI START command option AFTERCSN to tell Coordinated Apply what data to skip in it’s GoldenGate Trail. As specified in the Oracle GoldenGate reference Guide, AFTERCSN does the following:

Causes Replicat to start processing at the transaction that occurred after the one with the specified CSN. Any transactions in the trail that have CSN values that are less than, or equal to, the specified one are skipped.

Now we can add our Coordinated Apply in the target Oracle GoldenGate instance:

GGSCI (den01eln) 1> dblogin sourcedb mss14ljp, userid ggadmin, password Oracle1
Successfully logged into database.
GGSCI (den01eln as ggadmin@MSS14LJP) 2> add checkpointtable dbo.ggchkpt
Successfully created checkpoint table dbo.ggchkpt.
GGSCI (den01eln as ggadmin@MSS14LJP) 3> add replicat rljp, coordinated, exttrail ./dirdat/rp, checkpointtable dbo.ggchkpt
REPLICAT (Coordinated) added.

Check the Coordinated Apply:

GGSCI (den01eln as ggadmin@MSS14LJP) 4> info rljp
REPLICAT   RLJP      Initialized   2017-03-30 12:07   Status STOPPED
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:00:00 (updated 00:00:52 ago)
Log Read Checkpoint  File ./dirdat/rp000000000
First Record  RBA 0

Start the Coordinated Apply, specifying the LSN after which it should apply data to the target database:

GGSCI (den01eln as ggadmin@MSS14LJP) 5> start replicat rljp, aftercsn 71000000015300001
Sending START request to MANAGER …
REPLICAT RLJP starting

The GGSCI INFO command will show any processing lag in the Coordinated Apply. When the value for Checkpoint Lag reaches 00:00:00, all queued data has been applied to the target database:

GGSCI (den01eln as ggadmin@MSS14LJP) 31> info rljp
REPLICAT   RLJP      Last Started 2017-03-30 12:33   Status RUNNING
COORDINATED          Coordinator                      MAXTHREADS 25
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           17456
Log Read Checkpoint  File ./dirdat/rp000000005
2017-03-30 12:30:50.802000  RBA 869124

We can verify data is being applied by looking at report statistics, or via the GGSCI STATS command:

2017-03-30 12:37:07  INFO    OGG-01021  Command received from GGSCI: REPORT.
*
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
*
Reading ./dirdat/rp000000000, current RBA 869124, 1351 records, m_file_seqno =
, m_file_rba = 0
*
Report at 2017-03-30 12:37:07 (activity since 2017-03-30 12:33:09)
*
From Table DBO.MYTABLE to DBO.MYTABLE:
#                   inserts:         3
#                   updates:         0
#                   deletes:         0
#                  discards:         0
From Table DBO.CUSTOMERS_INFO to DBO.CUSTOMERS_INFO:
#                   inserts:       200
#                   updates:        40
#                   deletes:         0
#                  discards:         0
From Table DBO.ORDERS to DBO.ORDERS:
#                   inserts:        40
#                   updates:         0
#                   deletes:         0
#                  discards:         0
From Table DBO.ORDERS_PRODUCTS to DBO.ORDERS_PRODUCTS:
#                   inserts:       228
#                   updates:         0
#                   deletes:         0
#                  discards:         0
From Table DBO.ORDERS_STATUS_HISTORY to DBO.ORDERS_STATUS_HISTORY:
#                   inserts:        40
#                   updates:         0
#                   deletes:         0
#                  discards:         0
From Table DBO.ORDERS_TOTAL to DBO.ORDERS_TOTAL:
#                   inserts:       120
#                   updates:         0
#                   deletes:         0
#                  discards:         0
From Table DBO.NEXT_ORDER to DBO.NEXT_ORDER:
#                   inserts:        40
#                   updates:         0
#                   deletes:        40
#                  discards:         0
From Table DBO.CUSTOMERS to DBO.CUSTOMERS:
#                   inserts:       200
#                   updates:         0
#                   deletes:         0
#                  discards:         0
From Table DBO.NEXT_CUST to DBO.NEXT_CUST:
#                   inserts:       200
#                   updates:         0
#                   deletes:       200
#                  discards:         0

The source and target databases are fully in sync at this point. You could use Oracle GoldenGate Veridata to perform a full functional comparison of the two databases; but, that is another blog for another day.

Summary

In this article we presented details on how to perform a zero downtime implementation of Oracle GoldenGate, with a full target database instantiation using Miccrosoft SQL Server Management Studio backup and restore. We also presented Oracle GoldenGate functionality to have a Coordinated Apply begin applying transactional data to the target database for records captured by Oracle GoldenGate after the backup completed.

Comments

  1. Thomas Vengal says:

    SQL Server 2016 is supported by GoldenGate for SQL Server 12.3+ version onwards.
    Refer to 12.3 certification matrix https://www.oracle.com/technetwork/middleware/data-integration/goldengate-12-3-x-cert-matrix-3424388.xls

  2. Given GoldenGate does not support replication from Microsoft SQL Server 2016 what technologies do you suggest to use instead?

    • Paul,

      Spoke with Product Management concerning your question. He requests that you log a SR with Oracle Support if you require clarification on the product direction for GoldenGate for SQL Server.

      Regards,
      Loren Penton

Add Your Comment