Topics:
Parent topic: Using Oracle GoldenGate with SQL Server
Optional requirements if installing and running GoldenGate from a middle tier Windows server:
Install SQL Server Client Tools on the middle tier server in order to configure primary database ODBC connection
Microsoft SQL Server 2008 SP4 Feature Pack: https://www.microsoft.com/en-us/download/details.aspx?id=44277
Microsoft SQL Server 2008 R2 SP3 Feature Pack: https://www.microsoft.com/en-us/download/details.aspx?id=44272
Microsoft SQL Server 2012 Feature Pack: https://www.microsoft.com/en-us/download/details.aspx?id=29065
Microsoft SQL Server 2014 Feature Pack: https://www.microsoft.com/en-us/download/details.aspx?id=42295
Set the middle tier server's date, time, and time zone to the same as the primary source database server.
Create a network share of the folder that contains the source database transaction log backups. For example, if SQL Server writes log backups to D:\SQLBackups, then create a share on this folder that can be accessed by the Extract running on the middle tier Windows server.
Oracle GoldenGate Manager must run as an account with READ permissions to the log backup folder, the log backups, and the network share if configuring for remote ALO mode capture.
The default of Local System Account will work if 'Everybody' has share and folder access (not very secure).
Oracle recommends that you use a Windows account to run the Manager service and control share and folder access to that account.
Set up SQL Server transaction log backups at a tolerable interval, every 15 minutes for example, if this is still within the recovery SLA for the database.
The log backup frequency will be minimum data lag for replication as well.
Set the transaction log backups to write to the folder that is shared on the network if installing GoldenGate on a middle tier Windows server.
For an Extract in ALO mode running on the database server, create a System DSN for the database as per normal instructions.
For an Extract in ALO mode running on a middle tier Windows server, create a system DSN on that server that connects back to the primary database. This connection will be used by Extract to retrieve table metadata, manage the secondary truncation point, and other tasks.
Supplemental logging must be enabled by normal means (ADD TRANDATA) via GGSCI against the source database tables to be included for capture.
Note:
Tables to be captured only from the ALO mode are still required to have Supplemental Logging enabled.Extract must use either TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT or NOMANAGESECONDARYTRUNCATIONPOINT.
ACTIVESECONDARYTRUNCATIONPOINT is not compatible with the ALO mode and will cause Extract to Abend if it is enabled.
Extract must also use the TRANLOGOPTIONS ARCHIVEDLOGONLY parameter, which instructs the Extract to capture DML from the transaction log backups only.
For remote ALO capture, use TRANLOGOPTIONS ALTARCHIVELOGDEST as well, listing the path of the network share location (in double quotes) where the SQL Server transaction log backups are located. Extract will query the msdb database to find the full path of the SQL Server log backup that is needed, and substitute the value of the path from the query result with the path listed in ALTARCHIVELOGDEST.