Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, April 01, 2012

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 

2 comments:

Brielle Franklin said...

What a lengthy looking process. I am doing research on server replication for my IT class. I believe this post answers all my questions and more. Thanks for the post!

Thangam said...

I have been searching a document for a long time which explains how to configure the replication from mssql to mssql.

The moment i got your link, i thought my work is half done.

But still i have not gone through.

Thanks a lot and keep up the good work.

I will work it out and update.