Topics:
Parent topic: Using Oracle GoldenGate with SQL Server
Oracle GoldenGate requires that you set a SQL Server source database to the Full recovery model and that a full database backup exists.
To verify or set the recovery model:
Oracle GoldenGate Capture for SQL Server can run in two modes: real-time capture mode and archived log only mode.
In archived log only mode, Extract reads only from transaction log backups.
In real-time capture mode, the Extract process may occasionally require information from a log backup depending on how you configure the management of the secondary truncation point. This requirement occurs when log records have been flushed to log backups and are no longer available in the online log.
In either mode, Oracle GoldenGate Capture for SQL Server requires that the log backup files meet the following conditions:
The log backup is a native SQL Server log backup made by issuing the BACKUP LOG command (or the corresponding GUI command). Third-party log backups are not supported.
The log backup can be compressed using native SQL Server compression features.
The log backup is made to a DISK device. Valid examples include:
BACKUP LOG dbname TO DISK = "c:\folder\logbackup.trn" BACKUP LOG dbname TO DISK = "\\server\share\logbackup.trn"
Additional recommendations:
Do not overwrite existing log backups.
Striped log backups are not supported.
Appending log backups to the same file is not recommended.
Mixing compressed and uncompressed log backups to the same device or file is not supported.
These instructions apply to source installations of Oracle GoldenGate for all supported versions of SQL Server .
When you enable supplemental logging with the ADD TRANDATA command, Extract can capture the information that is required to reconstruct transactions. By default, SQL Server logs only some of this information.
ADD TRANDATA must be issued for all tables that are to be captured by Oracle GoldenGate. ADD TRANDATA uses different features of SQL Server, depending on the edition of SQL Server.
In Enterprise Edition, Change Data Capture (CDC) for SQL Server creates a minimal Change Data Capture object on the specified table.
In Standard Edition, a SQL Server transactional publication is created, as well as an article for each table that is enabled with TRANDATA
Enterprise Edition
Enables the database for SQL Server Change Data Capture by running EXECUTE sys.sp_cdc_enable_db.
Creates a Change Data Capture table for each base table enabled with supplemental logging by running EXECUTE sys.sp_cdc_enable_table.
Oracle GoldenGate does not use CDC tables except as necessary to enable supplemental logging.
When SQL Server enables CDC, SQL Server creates two jobs per database:
cdc.dbname_capture
cdc.dbname_cleanup
Make sure to review the information on TRANLOGOPTIONS options in Managing the Secondary Truncation Point for a complete understanding of this required parameter and the interaction it has with the secondary truncation point and these SQL Server jobs.
Standard Edition
To enable TRANDATA for tables of a SQL Server Standard Edition database, SQL Server Replication features must be installed, a Distributor must be configured, and a distribution database must be created.
An Oracle GoldenGate transactional publication is created for the database, and an article for each table is created in the database's publication. The articles are set to not replicate the data to a distribution database. However, they are needed to enable supplemental logging for SQL Server Standard Edition.
When a transactional publication is created, a SQL Server Log Reader Agent job is created for the database. The Log Reader Agent job has a naming convention of instancename-dbname-# and job category of REPL-LogReader.
Make sure to review the information on TRANLOGOPTIONS options in Managing the Secondary Truncation Point for a complete understanding of this required parameter and the interaction it has with the secondary truncation point and these SQL Server jobs.
This procedure requires a database user who is a member of the SQL Server System Administrators (sysadmin) role.
1. On the source system, run GGSCI.
2. Issue the following command to log into the database:
DBLOGIN SOURCEDB DSN[, {USERID user, PASSWORD password | USERIDALIAS alias}]
In this command:
SOURCEDB DSN is the name of the SQL Server data source.
USERID user is the database login and PASSWORD password that is required if the data source connects via SQL Server authentication. If the credentials are stored in a credential store, USERIDALIAS alias is the alias for the credentials. If you are using DBLOGIN with a DSN that is using Integrated Windows authentication, the connection to the database for the GGSCI session is that of the user running GGSCI. In order to issue ADD TRANDATA or DELETE TRANDATA, this user must be a member of the SQL Server sysadmin server role.
3. In GGSCI, issue the following command for each table that is, or will be, in the Extract configuration. You can use a wildcard to specify multiple table names.
ADD TRANDATA owner.table
ADD TRANDATA owner.*
Parent topic: Enabling Supplemental Logging
When you enable supplemental logging with the ADD TRANDATA command for at least one table in a source SQL Server database, a secondary truncation point is created in the transaction log that has to be moved for log space to be released as needed, following subsequent log backups. Use the TRANLOGOPTIONS parameter to control how the secondary truncation point is managed. TRANLOGOPTIONS is a required parameter for a SQL Server Extract and has these available options for managing the secondary truncation point, one of which must be chosen:
Two TRANLOGOPTIONS options instruct the Oracle GoldenGate Extract process to manage the secondary truncation point: ACTIVESECONDARYTRUNCATIONPOINT and MANAGESECONDARYTRUNCATIONPOINT. You can use either of these options if you have the following situations:
Extract is not running concurrently (for the same source database) with SQL Server transactional replication.
SQL Server Change Data Capture (CDC) that is configured by applications other than Oracle GoldenGate.
Note:
Using TRANLOGOPTIONS ACTIVESECONDARYTRUNCATIONPOINT or MANAGESECONDARYTRUNCATIONPOINT for Extract when either SQL Server transactional replication and/or CDC configured by applications other than Oracle GoldenGate are running at the same time causes the SQL Server Log Reader Agent or CDC capture job to fail.
The TRANLOGOPTIONSoption and the ACTIVESECONDARYTRUNCATIONPOINToption has different characteristics that are described in this section.
The ACTIVESECONDARYTRUNCATIONPOINT option has the following characteristics:
Extract sets the secondary truncation point in the transaction log only for those transactions that it has captured.
ACTIVESECONDARYTRUNCATIONPOINT prevents the truncation of the transactions until Extract has captured them from the online transaction log.
Because no online data can be truncated before Extract captures it, Extract is never required to read from log backups. The advantage of this method is that you can use third-party transaction log backup software without concern that Extract may need to access the log backups to retrieve unprocessed transaction data.
Using option ACTIVESECONDARYTRUNCATIONPOINT may result in larger log files if Extract has significant lag. Therefore, you may need to adjust your storage parameters to accommodate the larger logs. Larger logs occur because transaction log backups cannot immediately truncate the transaction log. Truncation occurs only after Extract has finished capturing the data.
Using this option requires that you allow only one Extract to capture from the source database.
This option is not valid for an Extract that is running in archived log mode.
If you are using SQL Server Enterprise Edition, when the Extract starts, it stops and deletes the CDC capture job for the database, preventing data from being loaded to any CDC tables. If any transactions for the tables occurs between the time that supplemental logging is added and Extract starts, the corresponding CDC tables may have data in them. This data is not used by Oracle GoldenGate and can be manually deleted or truncated from the CDC tables. Additionally, the CDC cleanup job for the source database can be disabled or deleted. It no longer functions once the CDC capture job has been removed.
If you are using SQL Server Standard Edition, you must manually stop and disable the SQL Server Log Reader Agent job for the source database, before you position the Extract.
The MANAGESECONDARYTRUNCATIONPOINT option has the following characteristics:
Extract commonly sets the truncation point in the transaction log at preset interval, by setting a high water mark for the truncation point at that point in the log.
As subsequent transaction log backups are made, transactions before the truncation point can be removed from the online transaction log, which frees space in the online log. If Extract has not captured a transaction from the online log before it is flushed to a log backup, it switches to the log backup and reads the transaction from the backup. This ability requires the logs to be backed up only with a native SQL Server backup utility. Third-party backup software cannot be used in this scenario.
Using this option lets multiple Extracts capture from the same source database. Configure Extract with this option if there are multiple Extracts for a single source database.
If you are using SQL Server Enterprise Edition starts, it stop and deletes the CDC capture job for the database, preventing data from being loaded to any CDC tables. If any transactions for the tables occurs between the time that supplemental logging is added and Extract starts, the corresponding CDC tables may have data in them. This data is not used by Oracle GoldenGate and can be manually deleted or truncated from the CDC tables. Additionally, the CDC cleanup job for the source database can be disabled or deleted. It no longer functions once the CDC capture job has been removed.
If you are using SQL Server Standard Edition, you must manually stop and disable the SQL Server Log Reader Agent job for the source database.
When you use either of these options, if Extract is stopped or down for a longer period of time than the log backup frequency, the secondary truncation point does not move and transactions are not removed from the transaction log after subsequent log backups. This may cause the log file to grow to its maximum size. If this occurs, contact Oracle Support for information about manually moving the secondary truncation point.
Parent topic: Managing the Secondary Truncation Point
Use TRANLOGOPTIONS with the NOMANAGESECONDARYTRUNCATIONPOINT option if Extract will run concurrently (for the same source database) with SQL Server transactional replication and/or SQL Server CDC that is configured by applications other than Oracle GoldenGate. SQL Server will manage the secondary truncation point.
Parent topic: Managing the Secondary Truncation Point
Retain enough log backups and backup history (in the msdb database) so that if you stop Extract or there is an unplanned outage, Extract can start again from its checkpoints. Extract must have access to the data in the transaction log or a log backup that contains the start of the oldest uncommitted unit of work, and all log backups thereafter.
If data that Extract needs during processing is not retained, either in online logs or in the backups, one of the following corrective actions might be required:
Alter Extract to capture from a later point in time for which log data is available (and accept possible data loss on the target).
Resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.
To determine where the Extract checkpoints are, use the INFO EXTRACT SHOWCH command. For more information, see Replicat Checkpoints in Oracle Fusion Middleware Administering Oracle GoldenGate for Windows and UNIX Guide.