Oracle GoldenGate for SQL Server features Capture support of the Primary and read-only, synchronous mode Secondary databases of an AlwaysOn Availability Group, and Delivery to the Primary database.
Topics:
Parent topic: Using Oracle GoldenGate with SQL Server
Create a System DSN that connects to the Primary (Capture/Delivery) or Secondary (Capture only) replica database as per normal instructions.
If the connection used in the DSN is to the AG Listener, then Capture will only be against the Primary database.
Supplemental logging must be enabled by normal means (ADD TRANDATA) using GGSCI against the Primary database tables to be included for capture, and not against a Secondary replica database.
If Oracle GoldenGate is installed on a secondary replica node, you will need to create a separate DSN to the Primary database, in order to connect using DBLOGIN and run ADD TRANDATA.
The login used with DBLOGIN must have sysadmin membership of the Primary replica instance.
When enabling supplemental logging on the Primary database, the SQL Server Change Data Capture job does not automatically get created, therefore, if a Secondary database becomes a Primary after failover, then you must manually create the SQL Server CDC capture job on the new Primary by running the following against the new Primary:
EXECUTE sys.sp_cdc_add_job 'capture'
If you modify the job's parameters from its default values using EXEC sys.sp_cdc_change_job, then after adding the job to the new Primary database, they would also have to re-run EXEC sys.sp_cdc_change_job against the capture job on the new Primary database.
Note:
Consult the Microsoft documentation on how to enable the CDC Capture job for AlwaysOn Secondary Replicas for more information:.(CDC Extract) When running an Extract from a middle tier Windows server, set the middle tier server's date, time, and time zone to the same as that of the Primary database server.
(CDC Extract) If an Extract is reading from a readable Secondary database, then it will not be able to update the Oracle GoldenGate CDC Cleanup job if used, therefore, Extract must run with TRANLOGOPTIONS NOMANAGECDCCLEANUP when running against a secondary database.
(CDC Extract) If an Extract is configured to capture against the Primary database, upon failover to a new Primary, manually create theOracle GoldenGate CDC Cleanup job before starting the Extract. This only needs to be done once on each potential Primary after it is available. It can be done by scripting the job from the previous Primary and running against the new Primary.