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
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:
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 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
GO
CREATE SCHEMA [ogg] AUTHORIZATION [dbo]
GO
GGSCI (OGGVM as ggadmin@SRCOGG) 10> edit params ./GLOBALS
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
