OGG 12.3 for SQL Server CDC mode configuration

This article mainly describes the Ogg 12.3 through the CDC extraction of SQL Server enterprise process, delivery configuration is relatively simple, so not elaborated here. Overview of configuration steps

1. Unzip the Ogg 12.3 for SQL Server Software, execute create subdirs, edit Mgr and start.

2. Database installation configuration, and patching

3. Create schema Ogg

4. Create a test table

5. Create a Globals file

6. Create a synced user

7. Make sure the SQLAgent starts normally

8. Enable CDC on the source-side DB

9. Add Trandata

10. Create a job that cleans up historical change data

11. Create ODBC

12. Creating the extraction Process

13. Testing

installation process

The Ogg software and SQL Server 2016 installation steps are omitted here. Patch Download Installation

After installing the SQL Server ENT version, you will need to patch:

https://support.microsoft.com/en-us/help/3166120/ Fix-could-not-find-stored-procedure-sys-sp-cdc-parse-captured-column-l creating Ogg schema on Source Library

Used to hold a configuration table where the CDC schema is created by a stored procedure that enables CDC steps.

Use [source]

GO

CREATE SCHEMA [ogg] AUTHORIZATION [dbo]

Go to create a test table

Use source; CREATE TABLE Dbo.test1 (ID int primary key, name varchar (50)); CREATE TABLE dbo.test2 (ID int, name varchar (), age int); Use target; CREATE TABLE Dbo.test1 (ID int primary key, name varchar (50)); CREATE TABLE dbo.test2 (ID int, name varchar (), age int);

Create a Globals file

In the Ogg directory, create a new Globals file and enter the following:

Ggschema ogg create ogg sync user

and empower

Target End user

Start SQLAgent

The agent is used to start the CDC corresponding job, so the agent must be started to synchronize properly.

Enable CDC on Source Library

Use source

EXECUTE Sys.sp_cdc_enable_dbogg Trandata

Ggsci>dblogin sourcedb srcdb, userid srcogg, password Srcogg

Ggsci>add Trandata Dbo.test1

Ggsci>add Trandata Dbo.test2

Once the execution is complete, you can see one more configuration table

Ogg. Oracleggtrantables'll be added automatically.

If the data table does not have add trandata, its change data cannot be captured. Create Ogg Clean Job

You need to remove the clean job that comes with DB first

EXECUTE sys.sp_cdc_drop_job ' cleanup '

Then, under the Ogg installation directory, go to the command line and execute the following statement, where (local) is the default SQL Server instance

Ogg_cdc_cleanup_setup.bat createjob srcogg Srcogg Source (local) Ogg

Add Tables:

and add a new job

Create ODBC

If you need to remotely capture SQL Server, you can do so as long as the configuration is remote Access DB via ODBC.

Configuring the extraction Process

Ggsci (DESKTOP-V8IQDQP) 6> view param exsrc

Extract EXSRC Sourcedb srcdb, userid srcogg, password Srcogg Exttrail./dirdat/aa Table dbo.*;

Add extract exsrc, Tranlog, begin now

Add Exttrail/dirdat/aa, extract Exsrctest

INSERT into Dbo.test1 values (1, ' BC is Kos ');

View the extraction process status for OGG

There is a capture to the data.

OGG 12.3 for SQL Server CDC mode configuration