Supplemental logging must be enabled by normal means (ADD TRANDATA) using Admin Client connected to the primary replica and not against a secondary replica.

• Create a DSN to the primary replica, or to the Always On Availability Group Listener, to connect using DBLOGIN to run ADD TRANDATA.

• The login used to enable supplemental logging must have sysadmin membership of the primary replica instance.

• When enabling supplemental logging against the primary replica database, the SQL Server Change Data Capture job does not automatically get created on any secondary replicas. Upon failover from a primary to a secondary, you must manually create the SQL Server Change Data Capture job and the Oracle CDC Cleanup job if in use, on the new primary replica.

  EXECUTE sys.sp_cdc_add_job N'capture

  • When creating the SQL Server CDC Capture job on the new primary, the default configuration settings are put in place. So if you have previously modified the default values on the former primary replica, you need to run sys.sp_cdc_change_job on the new primary and set the values accordingly.