Install OGG for SQL Server in windows

Install OGG for Sql Server

Prepare

Check sqlserver veriosn

 SELECT @@version

Microsoft SQL Server 2016 (SP1-CU10-GDR) (KB4293808) – 13.0.4522.0 (X64) Jul 17 2018 22:41:29 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Requirements

https://docs.oracle.com/goldengate/c1230/gg-winux/OGGIN/installing-sql-server1.htm#OGGIN-GUID-EB7DD65D-4344-4F72-9B45-18631536AC84

For SQL Server 2016, use or install the Microsoft ODBC Driver 13.1:https://www.microsoft.com/en-us/download/details.aspx?id=53339.

  • Classic Extract supports SQL Server Enterprise and Standard editions of versions 2008, 2008R2, 2012, and 2014.
  • CDC Extract supports SQL Server Enterprise editions of versions 2008, 2008 R2, 2012, 2014, 2016, and Standard Edition of SQL Server 2016 with Service Pack 1 (or above).
  • (CDC Extract) If you are using SQL Server 2016, before you enable supplemental logging, ensure that you have applied the following bug fix from Microsoft: https://support.microsoft.com/en-us/help/3166120/fix-could-not-find-stored-procedure-sys.sp-cdc-parse-captured-column-list-error-in-sql-server-2016 If this fix is not applied to your SQL Server instance, issuing ADD TRANDATAagainst a table for the database may incorrectly report that supplemental logging succeeded, when in fact it may not have, and no records will be captured for the table.
  • For SQL Server 2012/2014/2016, use the SQL Server Native Client 11.0 driver.
  • For SQL Server 2014/2016, only the SQL Server Native Client 11.0 driver is supported. The ODBC Driver 11/13 for SQL Server is not supported.

Fix Bugs

There are Microsoft bugs that impact the ability of the CDC job to capture correctly, and have been identified with the following two bugs that you need to apply based on your SQL Server version:

1. For SQL Server 2012, 2014, and 2016, Microsoft has identified and fixed an issue where some UPDATE operations may be written incorrectly to a CDC staging table as an INSERT followed by a DELETE, rather than a DELETE and INSERT pair. This may cause downstream replication issues (such as a primary key violation) so Oracle recommends that you apply this Microsoft fix:

https://support.microsoft.com/en-us/help/3030352

2. For SQL Server 2016, prior to enabling supplemental logging, you must ensure that you have patched the SQL Server instance based on the following Microsoft bug fix:

https://support.microsoft.com/en-us/help/3166120/fix-could-notfind-stored-procedure-sys.sp-cdc-parse-captured-column-list-error-in-sqlserver-2016

If the instance is not correctly patched with this Microsoft fix, issuing ADD TRANDATA against a table for the database may incorrectly report that supplemental logging succeeded, when in fact it may not have. This results in no records being captured for that table. You can verify that the TRANDATA command was successful by issuing the INFO TRANDATA command.

Only user databases are supported (can be AlwaysOn Primary or readable Synchronous Secondary).

The SQL Server Agent must be running. The SQL Server Native Client Drivers are supported; the Microsoft ODBC Driver 11 and 13 are not supported.

Install OGG4MSSQL

 

Download ggs_Windows_x64_MSSQL

             Download http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

123016_                  ggs_Windows_x64_MSSQL_64bit_CDC.zip

Install 123016_ggs_Windows_x64_MSSQL_64bit

F:\OGG4MSSQL\>ggsci

Oracle GoldenGate Command Interpreter for SQL Server

Version 12.3.0.1.6 OGGCORE_12.3.0.1.0_PLATFORMS_180531.2007

Windows x64 (optimized), Microsoft SQL Server on Jun 1 2018 13:03:19

Operating system character set identified as windows-1252.

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.

GGSCI (OGGVM) 1>             create subdirs

Creating subdirectories under current directory F:\OGG4MSSQL

Parameter file F:\OGG4MSSQL\dirprm: created.

Report file F:\OGG4MSSQL\dirrpt: created.

Checkpoint file F:\OGG4MSSQL\dirchk: created.

Process status files F:\OGG4MSSQL\dirpcs: created.

SQL script files F:\OGG4MSSQL\dirsql: created.

Database definitions files F:\OGG4MSSQL\dirdef: created.

Extract data files F:\OGG4MSSQL\dirdat: created.

Temporary files F:\OGG4MSSQL\dirtmp: created.

Credential store files F:\OGG4MSSQL\dircrd: created.

Masterkey wallet files F:\OGG4MSSQL\dirwlt: created.

Dump files F:\OGG4MSSQL\dirdmp: created.

GGSCI (OGGVM) 2>

GGSCI (OGGVM) 2>

Installing Manager as a Windows Service

Specifying a Custom Manager Name

You must specify a custom name for the Manager process if either of the following is true:

You want to use a name for Manager other than the default of GGSMGR.

There will be multiple Manager processes running as Windows services on this system. Each Manager on a system must have a unique name. Before proceeding further, note the names of any local Manager services.

GGSCI (OGGVM) 2> view PARAMS ./GLOBALS

MGRSERVNAME OGGSMGR

ggschema ogg

F:\OGG4MSSQL>INSTALL ADDSERVICE

Service ‘OGGSMGR’ created.

Install program terminated normally

GGSCI (OGGVM) 5> view params mgr

PORT 7809

GGSCI (OGGVM) 6> info mgr

Manager is running (IP port OGGVM.7809, Process ID 7192).

Set OGG4MSSQL Replication

Setting the Database to Full Recovery Model

Oracle GoldenGate requires that you set a SQL Server source database to the Full recovery model and that a full database backup exists.

  • Connect to the SQL Server instance with SQL Server Management Studio for SQL Server.
  • Expand the Databases folder.
  • Right-click the source database, and then select Properties.
  • Select the Options tab.
  • Under Recovery, set the model to Full, if it is not already set.
  • If the database was in simple recovery or never had a full database backup, take a full database backup before positioning Extract.
  • Click OK.

Create ogg user on Sql Server

Create user with Windows Authentication

 

Create use with SqlServer Authentication

Create DSN

The DSN have to be created on System DSN, otherwise OGG extract will can be started caused by db connection failed.

      Create DSN by using Windows authentication

Choose database

 

 

Create DSN by using SQL server authentication

Microsoft SQL Server Native Client Version 11.00.6540

Data Source Name:                   OGGSRCSA

Data Source Description:

Server: OGGVM

Use Integrated Security: No

Database: Mydb

Language: (Default)

Data Encryption: No

Trust Server Certificate: No

Multiple Active Result Sets(MARS): No

Mirror Server:

Translate Character Data: Yes

Log Long Running Queries: No

Log Driver Statistics: No

Use Regional Settings: No

Use ANSI Quoted Identifiers: Yes

Use ANSI Null, Paddings and Warnings: Yes

       GGSCI (OGGVM) 5> dblogin sourcedb OGGSRCSA,userid ggadmin password ggadmin

2018-09-05 15:40:25 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.

2018-09-05 15:40:25 INFO OGG-03037 Session character set identified as windows-1252.

Successfully logged into database.

To Enable Supplemental Logging

             USE Mydb

GO

CREATE SCHEMA [ogg] AUTHORIZATION [dbo]

GO

GGSCI (OGGVM as ggadmin@SRCOGG) 10> edit params ./GLOBALS

       ggschema ogg

GGSCI (OGGVM) 5>                               dblogin sourcedb SRCOGGWA,userid ggadmin password ggadmin

2018-09-05 16:38:13 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.

2018-09-05 16:38:13 INFO OGG-03037 Session character set identified as windows-1252.

Successfully logged into database.

GGSCI (OGGVM as ggadmin@SRCOGG) 2>             ADD TRANDATA dbo.*

Logging of supplemental log data is enabled for table dbo.argo_report_job_definition

ogg.OracleGGTranTables will be added automatically.

Enable CDC

select is_cdc_enabled from sys.databases

select * from sys.databases

USE master

GO

SELECT [name], database_id, is_cdc_enabled

FROM sys.databases

GO

USE

GO

EXEC sys.sp_cdc_enable_db

GO

      Drop OGG clean job

In the Management Studio, within a query window for the source database. You must manually drop the SQL Server CDC cleanup job for the database because it may cause data loss for the Extract.

USE Mydb

go

EXECUTE sys.sp_cdc_drop_job ‘cleanup’;

go

Create the Oracle GoldenGate CDC cleanup job and associated objects

Use the ogg_cdc_cleanup_setup.bat utility (in the Oracle GoldenGate installation directory) to create the Oracle GoldenGate CDC cleanup job and associated objects. The ggschema name used must be the same that you used with the GGSCHEMA parameter of the GLOBALS file. You must use a SQL Server authenticated user that has sysadmin rights.

Example:

ogg_cdc_cleanup_setup.bat createJob user1 pword1 dbname1 HOSTNAME\INSTANCE ggschema

ogg_cdc_cleanup_setup.bat dropJob user1 pword1 dbname1 HOSTNAME\INSTANCE ggschema

       F:\OGG4MSSQL>ogg_cdc_cleanup_setup.bat createjob ggadmin ggadmin Mydb OGGVM ogg

Oracle GoldenGate CDC cleanup job setup script

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

Command: createjob

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

Command will add one job:

Add two tables:

ogg.OracleGGExtractCheckpoint

ogg.OracleGGCleanupJobStatus

Edit mgr

       GGSCI (OGGVM) 4> edit params mgr

PORT 7809

GGSCI (OGGVM) 3> start mgr

Starting Manager as service (‘GGSMGR’)…

Service started.

Configure Replication

Prepare Source:

select @@SERVERNAME

OGGVM

USE master

GO

SELECT [name], database_id, is_cdc_enabled

FROM sys.databases

GO

To Enable Supplemental Logging

GGSCI (OGGVM) 1>                   dblogin             sourcedb SRCOGG,userid ggadmin password ggadmin

                   GGSCI (OGGVM as ggadmin@SRCOGG) 2>             add trandata dbo.test01

Logging of supplemental log data is enabled for table dbo.test01

Create defgen file

Syntax of Defgen

GGSCI (source) 2> edit PARAMS DEFGEN

defsfile <DefgenPath> \<DefgenName>.def

sourcedb <DSN_Name>,userid <dbusername> password <dbpassword>

table dbo.<table_name>;

F:\OGG4MSSQL\>defgen paramfile <Pameter_Path>\defgen.prm

Note:

<DSN_Name> Should be replaced by real DSN name, example: SRCOGG.

<dbusername> should be replaced by username of MS SQL Database, example: ggadmin

<dbpassword> should be replaced by password of db username, example: ggadmin

<Pameter_Path> should be replaced by real path of ogg parameter

Example

GGSCI (OGGVM as ggadmin@SRCOGG) 2>                               edit PARAMS DEFGEN

       defsfile F:\OGG4MSSQL\dirdef\mydb.def

sourcedb OGGSRCSA, userid ggadmin password ggadmin

table dbo.*;

F:\OGG4MSSQL\>            defgen paramfile F:\OGG4MSSQL\dirprm\defgen.p

Extract Process

Syntax of Extract

GGSCI (source) 2> edit params <ExtractName>

extract <ExtractName>

sourcedb <DSN_Name>,userid <dbusername> password <dbpassword>

exttrail ./dirdat/<Trail_Name>

       table <schema_name>.<table_name>;

       add extract             <ExtractName>, integrated tranlog, begin now

       add exttrail ./dirdat/<Trail_Name>, extract , <ExtractName>

start <ExtractName>

Note:

<DSN_Name> Should be replaced by real DSN name, example: SRCOGG.

<dbusername> should be replaced by username of MS SQL Database, example: ggadmin

<dbpassword> should be replaced by password of db username, example: ggadmin

<trail name> should be replaced by real trail file name, which has to be two letters, example: aa

Example: extract ESAS

extract ESAS

sourcedb OGGSRCSA,userid ggadmin, password ggadmin

EXTTRAIL F:\OGG4MSSQL\dirdat\SA

DDL EXCLUDE ALL

compressupdates

compressdeletes

ignoreupdatebefores

updaterecordformat compact

TABLE dbo.*;

add extract ESAS, TRANLOG, BEGIN NOW

add exttrail F:\OGG4MSSQL\dirdat\SS, extract ESAS, megabytes 300

start ESAS

Note: Terminal side need to modify the trail file path according to your real directory.

Pump process

      Syntax of Pump

GGSCI (source) 2> edit params <PumpName>

EXTRACT <PumpName>

RMTHOST <hostname for TIC>, MGRPORT <TIC Port Number>

RMTTRAIL <Soure_Trail_Path>/<Trail_Name>

PASSTHRU

table <schema_name>.<table_name>;

add extract                   pmp01, EXTTRAILSOURCE <Soure_Trail_Path>/<Trail_Name>, begin NOW

add rmttrail <Target_Trail_Path>/<Trail_Name>, EXTRACT <PumpName>, megabytes 500

start <PumpName>

Note:

<PumpName> should be replaced by name of the pump process ( limited to 8 characters ), example: PTE

<hostname for TIC> should be replaced by hostname or IP of target

<TIC Port Number> should be replaced by the manager port of target

<Soure_Trail_Path>/<Trail_Name> should be replaced as the real directory and trail name of source side, example: “F:\OGG4MSSQL\dirdat\aa”

<Target_Trail_Path>/<Trail_Name> should be replaced as the real directory and trail name of target side, example: “F:\OGG4ORA\dirdat\aa”

<trail name> should be replaced by real trail file name, which has to be two letters, example: aa

Example: pump PSAS

GGSCI (source) 2> edit params PSAS

EXTRACT PSAS

RMTHOST OGGVM, MGRPORT 7609

RMTTRAIL F:\OGG4ORA\dirdat\\TI

PASSTHRU

TABLE dbo.*;

add extract                         PSAS, EXTTRAILSOURCE F:\OGG4MSSQL\dirdat\SA, begin NOW

ADD RMTTRAIL F:\OGG4ORA\dirdat\\PP, EXTRACT PSAS, megabytes 300

start PSAS

Note: Terminal side need to modify the trail file path according to your real directory.

Problem

Problem:             OGG-00551 Database operation failed: Couldn’t connect to SRCOGG

                         OGG-00551 Database operation failed: Couldn’t connect to SRCOGG. ODBC error:             SQLSTATE IM002 native database error 0. [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

      

https://dbasolved.com/2016/01/07/error-when-starting-extract-against-ms-sql-server/

Open SQL Server Management studio -> Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -> Properties–>Server Role –>Enable sysadmin role

Leave a Reply

Your email address will not be published. Required fields are marked *