MSQL to MSQL Replication using GG

Prerequisite
Before attempting this post please review the other related GoldenGate posts on the page Other Cateogories for understanding GoldenGate concepts.
Goal
Testing the Oracle Golden Gate for SQLServer to SQLServer real time replication.

Environment
 One SQLServer 2008 running with Test instance. To achieve the source and target purpose, Oracle Golden Gate installed on two separate folders, one for extract (capture) purpose and the second one for Replication (Delivery) purpose.

Following steps performed for this replication

1- First create two folders in the e:\temp on dev-test, and extract the GoldenGate software in them
e:\temp\ggMSQLext
e:\temp\ggMSQLrep

Prepare the GoldenGate Environment
The GoldenGate application must be installed on both the source and target systems. A checkpoint table and Manager Service will be implemented.
SQL Server source database must be configured to support log-based extraction, and an ODBC data source must be created and configured correctly.

The source and target tables will be created with scripts provided in the GoldenGate installation. For log-based SQL Server extraction, additional logging must be enabled to provide enough information to reconstruct update operations.
Prepare the SQL Server 2008 source environment
- create database in sqlserver [source db (ggExtdb and Target DB (ggRepdb)]
- create proper logins for sqlserver database access [I used my domain login]
- create DSN (odbc in control panel for source (ggExtdb) and target (ggRepdb)

Create the test tables and insert source data
goldengate has the sample scritps we will use them
Execute the following commands on the system (ggExtdb) only.

Click the File > Open and navigate to the demo_mss_create.sql script.

Verify the results using the following commands in the input window:

sp_help tcustmer

go

sp_help tcustord

go

Following the steps you used to execute the table create script, run the demo_mss_insert.sql script (found in GoldenGate installation folder) to insert source data into the source tcustmer and tcustord tables.
 
Prepare for transaction logging
To support GoldenGate extraction, the following are required. Execute these steps on the (ggExtdb) system only.

•  Log truncation and non-logged bulk copy must be turned off.
•  The SQL Server database must be set to the full recovery model (this is the default for SQL Server 2008).
•  At least one full database backup must be done before GoldenGate processes are started for the first time.
•  Additional log data must be enabled so GoldenGate can reconstruct update operations.

Turn off log truncation and bulk copy
on the source system perform the following steps
check the status of the log truncation option 

exec sp_dboption 'ggExtdb', 'trunc. log on chkpt.'

If log truncation is ON, turn it off by executing the following command.

exec sp_dboption '', 'trunc. log on chkpt.', false

Check the status of the non-logged bulk copy option by executing the following command:

exec sp_dboption 'ggExtdb', 'select into/bulkcopy'

If non-logged bulk copy is on, disable it with the following command:

exec sp_dboption '', ' select into/bulkcopy ', false

Execute Backup
Take source database (ggExtdb backup to  ensures that no transaction log information is lost when GoldenGate starts up.



Set up capture of additional log data
on the source system configure the database to log full before and after images for each update operation.
- From the source operating system's command shell, run GGSCI.
create subdirectories in gg (source ggExtdb)
- Log into the database with the following command:

GGSCI (dev-test) 2> DBLOGIN SOURCEDB dsn_ggExtdb USERID domain\inam, PASSWORD pwd

Successfully logged into database.

- Issue the following commands to force the logging of the full before and after image for updates (for tables created earlier for our test).

ADD TRANDATA dbo.tcustmer

ADD TRANDATA dbo.tcustord

Verify that supplemental logging has been turned on for these tables.

GGSCI (dev-test) 5> INFO TRANDATA dbo.tcust*

Logging of supplemental log data is enabled for table dbo.tcustmer
Logging of supplemental log data is enabled for table dbo.tcustord



Note: With SQL Server 2005 you cannot drop tables that have TRANDATA set. First you must delete the TRANDATA setting.  (verify this for 2008 also)


Disable SQL Server Log Reader Agent job
The Log Reader Agent job cannot run concurrently with the GoldenGate Extract process. Perform the following steps to stop and disable this job.

•  In SQL Server Management Studio, connect to the SQL Server instance.
•  Start SQL Server Agent, if not running.
•  Expand the SQL Server Agent folder.
•  Expand the Jobs folder.
•  Find the job that was created by the ADD TRANDATA command. The name is based on the server, instance, and database name, plus the iteration of the publication.
•  Right click the job and select Stop Job.
•  Right click the job again and select Disable.

********************************
Prepare the SQL Server target(ggRepdb) system
Configure Manager process on the target

GGSCI (dev-test) 2> EDIT PARAMS MGR

PORT 7800

GGSCI (dev-test) 3> START MANAGER

Manager started.

GGSCI (dev-test) 4> INFO MANAGER

Manager is running (IP port dev-test.7800).



Create test tables on Target DB (ggRepdb)
Create the practice tables (we need to create in target now) by demo_mss_create.sql, don't use the script for insert.
**********************************************************

Initial Data Load using Direct Load Method
Steps to configure initial load
Add the initial data load extract batch task group

Execute the following commands on the source system to add an Extract process called EXTINI_1.
Execute the following command in GGSCI to create the batch task.
Shell> cd
CD E:\temp\ggMSQLext
Shell> ggsci

GGSCI (dev-test) 6> ADD EXTRACT EXTINI_1, SOURCEISTABLE

EXTRACT added.

Verify the results:

GGSCI (dev-test) 7> INFO EXTRACT *, TASKS

EXTRACT    EXTINI_1  Initialized   2012-04-01 14:22   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

 

Configure the initial data load Extract parameter file

Execute the following commands on the system. and update parameters in file

GGSCI (dev-test) 8> EDIT PARAMS EXTINI_1

--

-- GoldenGate Initial Data Capture

-- for TCUSTMER and TCUSTORD

--

EXTRACT EXTINI_1

SOURCEDB dsn_ggExtdb, USERID domain\inam, PASSWORD pwd

RMTHOST dev-test, MGRPORT 7800

RMTTASK REPLICAT, GROUP REPINI_1

TABLE dbo.TCUSTMER;

TABLE dbo.TCUSTORD;

Add the initial data load Replicat batch task group

Execute the following commands on the target (ggRepdb) system.
Execute the following command in GGSCI to create the batch task.
GGSCI (dev-test) 5>  ADD REPLICAT REPINI_1, SPECIALRUN
REPLICAT added.

Verify the results:

GGSCI (dev-test) 8> INFO REPINI*, TASKS

REPLICAT   REPINI_1  Initialized   2012-04-01 14:31   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:01 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


Configure the initial data load Replicat parameter file

Execute the following commands on the system.
Execute the following command in GGSCI to open the editor.

GGSCI (dev-test) 9> EDIT PARAMS REPINI_1

Add the following lines to the parameter file

-- GoldenGate Initial Data Load Delivery

--

REPLICAT REPINI_1

ASSUMETARGETDEFS

TARGETDB dsn_ggRepdb, USERID domain\inam, PASSWORD pwd

DISCARDFILE ./dirrpt/REPINI_1.txt, PURGE

MAP dbo.TCUSTMER, TARGET dbo.TCUSTMER;

MAP dbo.TCUSTORD, TARGET dbo.TCUSTORD;

Execute the initial data load process
Execute the following commands on the (ggExtdb)system.
Execute the following command to start the initial data load process. (first ensure that you start the manager process on source also, for me it was not running , so I ran it on 7900 port. so now source(extract) mgr is running on 7900 and target(replicat) mgr is running on 7800 ports )

START EXTRACT EXTINI_1

Verify the results:

VIEW REPORT EXTINI_1

++++++++++++++++++++++++++++++++++++++++++++++++++++++
I got the following error 
2012-04-01 14:41:56  WARNING OGG-01194  EXTRACT task REPINI_1 abended : OLE DB E
rror: Incompatible driver error DSN 'dsn_ggRepdb' SQL Server 2008 requires SQLNCLI10.

it was a driver issue , i just changed the driver and it was OK
++++++++++++++++++++++++++++++++++++++++++++++++++++++

Execute the following commands on the (ggRepdb) system.
Verify the results:
VIEW REPORT REPINI_1

**********************************************************
Configure Change Capture
Now we will add the Extract process that will capture changes. We will add the trail that will store the changes and start the Extract process.

Steps to configure change
Add the Extract checkpoint group
Execute the following commands on the system to create the Extract group.
++++++++++++++++++++++++++++++++++++++++++++++++++++++
GGSCI (dev-test) 19> ADD EXTRACT EXTggExtdb1, TRANLOG, BEGIN NOW
ERROR: Invalid group name (must be at most 8 characters).
++++++++++++++++++++++++++++++++++++++++++++++++++++++

GGSCI (dev-test) 20> ADD EXTRACT EXTSRC1, TRANLOG, BEGIN NOW

EXTRACT added.

Verify the results:

GGSCI (dev-test) 21> INFO EXTRACT EXTSRC1

EXTRACT    EXTSRC1   Initialized   2012-04-01 14:58   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:13 ago)
VAM Read Checkpoint  2012-04-01 14:58:59.030000

Create the Extract parameter file

Execute the following commands on the (ggExtdb) system.
Execute the following command in GGSCI to open the editor.

GGSCI (dev-test) 22> EDIT PARAM EXTSRC1

Add the following lines to the parameter file.
Note: MANAGESECONDARYTRUNCATIONPOINT is valid only for SQL Server 2005/2008, so should not be included for SQL Server 2000.

--

-- Parameter file to capture

-- TCUSTMER and TCUSTORD Changes

--

EXTRACT EXTSRC1

--Include the following only for SQL Server 2005/2008

TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT

-- If SQL Server Replication is also running, choose

-- the NOMANAGESECONDARYTRUNCATIONPOINT option

SOURCEDB dsn_ggExtdb, USERID domain\inam, PASSWORD pwd

RMTHOST dev-test, MGRPORT 7800

RMTTRAIL ./dirdat/tr

TABLE dbo.TCUSTMER;

TABLE dbo.TCUSTORD;

Note: Record the two characters (tr) selected for your You will need this in the next step and when you set up the Replicat.
Remove the USERID and PASSWORD if you are using the AD user for SQL Server connection.

Define the GoldenGate extract trails
Execute the following commands on the (ggExtdb) system to create a trail.

GGSCI (dev-test) 25> ADD RMTTRAIL ./dirdat/tr, EXTRACT EXTSRC1, MEGABYTES 50

RMTTRAIL added.

Verify the results:

GGSCI (dev-test) 26> INFO RMTTRAIL *

       Extract Trail: ./dirdat/tr
             Extract: EXTSRC1
               Seqno: 0
                 RBA: 0
           File Size: 50M

Start the Extract process
On the source (ggExtdb) system, issue the following command in GGSCI to start the Extract process.

START EXTRACT EXTSRC1

Verify the results:

INFO EXTRACT EXTSRC1, DETAIL

**********************************************************
Configure Change Delivery
Now we will set up the checkpoint table on the target (ggRepdb) system and create a named group that includes the Replicat process and the checkpoint tables. We will configure the Replicat group by adding parameters and start the Replicat group.

Set up checkpoints
Edit the GLOBALS file on the target system

Execute the following commands on the system.
Edit the GLOBALS parameter file to add the checkpoint table.
Shell> cd
Shell> ggsci
GGSCI> EDIT PARAMS ./GLOBALS
In the text editor, add the following line:

CHECKPOINTTABLE dbo.ggschkpt

Activate the GLOBALS parameters

For the changes to the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit GGSCI.

GGSCI> EXIT

Add a Replicat checkpoint table
Execute the following commands on the system.
This step adds the checkpoint table that you specified when you created the GLOBALS parameter file. It will be created in the dsn_ggRepdb database.
Run GGSCI on the target.
Shell> cd
Shell> ggsci

Execute the following commands in GGSCI.
GGSCI> DBLOGIN SOURCEDB dsn_ggRepdb, USERID domain\inam, PASSWORD pwd

GGSCI> ADD CHECKPOINTTABLE

Configure delivery
Add the Replicat checkpoint group
Execute the following commands on the system.to create the Replicat group named REPTGT1.
GGSCI> ADD REPLICAT REPTGT1, EXTTRAIL ./DIRDAT/tr
Note: Refer to your Extract set up for the correct two-character .


Create Replicat parameter file
Execute the following command on the system to edit the Replicat parameter file.
GGSCI> EDIT PARAM REPTGT1

Add the following lines to the parameter file.

REPLICAT REPTGT1

TARGETDB dsn_ggRepdb, USERID domain\inam, PASSWORD pwd

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./DIRRPT/REPTGT1.DSC, PURGE

MAP dbo.TCUSTMER, TARGET dbo.TCUSTMER;

MAP dbo.TCUSTORD, TARGET dbo.TCUSTORD;

Start the Replicat process
Execute the following commands on the system.
Execute the following command in GGSCI to start the Replicat process.

GGSCI> START REPLICAT REPTGT1

Verify the results:

GGSCI> INFO REPLICAT REPTGT1

Generate Activity & Verify Results
Generate inserts, updates, and deletes
Execute the following commands on the system.
Go to SQL Server Management Studio, select your database and press New Query.
Click the File > Open and navigate to the demo_mss_misc.sql script and click Open to open it in the input window.
Execute the script, Verify and record processing statistics with the following command in GGSCI.


GGSCI (dev-test) 28> SEND EXTRACT EXTSRC1, REPORT
Sending REPORT request to EXTRACT EXTSRC1 ...
Request processed.

GGSCI (dev-test) 29> VIEW REPORT EXTSRC1

Verify your results on the SQL Server target
Verify and record processing statistics on the target  with the following command in GGSCI.
GGSCI (dev-test) 11> SEND REPLICAT REPTGT1, REPORT
Sending REPORT request to REPLICAT REPTGT1 ...
Request processed.

GGSCI (dev-test) 12> VIEW REPORT REPTGT1

 
Turn off error handling
Turn off initial load error handling for the running delivery process

GGSCI (dev-test) 13> SEND REPLICAT REPTGT1 , NOHANDLECOLLISIONS
Sending NOHANDLECOLLISIONS request to REPLICAT REPTGT1 ...
REPTGT1 NOHANDLECOLLISIONS set for 2 tables and 0 wildcard entries


Remove initial load error handling from the parameter file

GGSCI (dev-test) 14> EDIT PARAMS REPTGT1

Remove the HANDLECOLLISIONS parameter.

Related Posts:

Oracle GoldenGate Tutorial Part 1 - Concepts and Architecture

Oracle GoldenGate Tutorial Part 2 - Installation (Windows 2003)

Oracle GoldenGate Tutorial Part 3 - Manager process

Oracle GoldenGate Tutorial Part 4 - Working with OGG

Live Reporting with OGG

Live Reporting with Data Pump -GG

OGG Reference - Commands and Options