Configuring GoldenGate to replicate data from MSSQL Standard Edition (CDC, Change Data Capture)

Introduction

imageOne of GoldenGate 12.2 new features is replication support for MSSQL Standard Edition. In the previous GoldenGate 12.1 release, GoldenGate supported capture of DML only from SQL Server Enterprise Edition, due to the method of enabling Supplemental Logging, which is done via a feature only available to Enterprise Editions of SQL Server. Fortunately, beginning with Oracle GoldenGate 12.2, GoldenGate is able to enable Supplemental Logging for Standard Edition instances via a different mechanism. That mechanism to enable Supplemental Logging is done via certain SQL Server Replication components. These SQL Server Replication components are required to be installed and configured in order to enable Supplemental Logging,

Also it is very interesting that MSSQL doesn’t offer Change Data Capture for MSSQL Standard Edition. So if we have 100 offices with MSSQL and we need to collect only changed data to Data Warehouse then it is required to install MSSQL Enterprise Edition in every office because CDC is only available in MSSQL EE.

Requirements to support MSSQL Standard Edition

1. Replicated tables in MSSQL Standard Edition must have primary key.

2. SQL Server Agent will be used during enabling supplemental logging for tables. Let’s enable it (required for MSSQL 2014):

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Agent XPs’, 1;
GO
RECONFIGURE
GO

3. Server Replication must be installed. This is done with SQL Server Installation Center. You should run it to check whether it is installed. If it is not installed then you can use SQL Server Installation Center to add this feature to existing installation.

imageimageimage

4. Once SQL Server Replication component is installed, a Distributor with distribution database must be configured. Really these components will not be used by GoldenGate:distribution database will be empty, log reader jobs will be disabled. But we still need to do these steps to enable supplemental logging. We should do these steps manually before adding supplemental logging for replicated tables.

image

5. Configure local server as Dstributor

imageimageimageimageimageimage

Configuring GoldenGate to capture data

0. Backup MSSQL database planned for replication.

1. Configure and start manager process

2. Create ODBC connection

image

3. Login to database

image

4. Add supplemental logging

image

5. Previous step adds special log reader job to SQL Server Agent. We will disable it through popup menu of job. Also we will stop current running job

image

Other steps are absolutely same as for other databases and MSSQL EE.

Leave a Reply

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