Issue

When running HVR Capture at the same time as native SQL Server replication or using native CDC tables, special considerations apply.

Description

If HVR Capture is not configured correctly when coexisting with native replication or CDC tables, it can cause data integrity issues with the data replicated by HVR as well as failures of the native replication or CDC components. This Knowledge Base article attempts to describe the considerations and provide recommended best practices to ensure error free operations.

Details

Transaction Log Truncation

HVR has a few different Capture options to control who manages the transaction log truncation and how that truncation occurs. It is important for this to be set correctly as HVR Capture, native replication and CDC read change data records from the database transaction log and no one replication product should impact the continuous operation of another.

When native replication or native CDC is running on the source database, HVR's recommended approach is to allow the SQL Server native component control the log truncation point and not have HVR Capture involved at all in moving the log truncation point. To do this, set the Capture action's parameter /LogTruncate to 'NATIVE_DBMS_AGENT'. This tells HVR that the native replication or CDC agent job will take care of the truncation point management without regard for where HVR Capture is reading the log from. This ensures that HVR will never prevent the truncation of the online log.

The database must be in Full Recovery Mode for this to work reliably as HVR Capture might have to resort to reading from transaction log backup(s) if native replication or CDC caused the online log to be truncated before HVR was able to read the truncated portion of the online log. Without Full Recovery (e.g running the database in Simple recovery mode) there are no transaction log backups to fall back to.

Supplemental Logging

By default, SQL Server does not write sufficient information into the change records in the transaction log to allow HVR to be able to reliably replicate change data. To force SQL Server to log all additional values (HVR uses the term ‘Supplemental Logging’ to describe this additional information) HVR leverages one of two SQL Server capabilities:

1. Native SQL Server CDC Tables

When a table is enabled for native CDC change tracking, a side effect is that the database then logs all the information HVR needs in the transaction log. HVR can automatically create the CDC tables associated with the replicated tables using the Capture parameter /SupplementalLogging to 'CDCTAB'. As HVR never reads the CDC tables, they just have to exist, HVR will usually delete the SQL Server agent capture job that populate the CDC tables to reduce unnecessary overhead of database resources. However, if SQL Server native replication is also in use, or if the customer is using CDC tables for other purposes outside of HVR, the SQL Server agent capture job cannot be deleted without breaking native Replication or stopping the other CDC tables getting updated.

Due to this limitation, using CDC tables to enable the supplemental logging of transaction data is not recommended.

2. Native SQL Server Replication publication articles

Similar to using the CDC tables to force the supplemental logging, the existence of a publication article on a replicated table will also force the database to log the required supplemental log information into the transaction log. Replication Articles are automatically created by HVR when the Capture parameter /SupplementalLogging is set to 'ARTICLE_OR_CDCTAB'. HVR creates the articles at channel initialization time, but they are created in a way so that they do not publish any data so there is no resource overhead associated with the articles HVR creates. One limitation of forcing supplemental logging through articles is that they can only be created for tables that have primary keys. If HVR finds a table does not have a primary key, it falls back to creating a CDC table for the tables without primary keys.

If the tables being replicated by HVR are already being replicated by SQL Server native replication, or the table changes are already being captured by CDC tables, then the supplemental logging HVR requires already exists and setting the Capture parameter /SupplementalLogging to 'EXISTING' is the recommended approach. In this case HVR does not attempt to create any database object to force supplemental logging.