SQL Server Log-Based Capture Options

Question

What option should I select when setting up SQL Server log-based capture?

When setting up log-based capture from SQL Server there are three closely related options in the Capture action: /SupplementalLogging, /LogReadMethod, and /LogTruncate.

What option should I select?

Environment

HVR 5

Answer

This post describes the relationship between these options and is based on the minimal version HVR 5.2. Log-based capture is available with older versions of HVR. However, with HVR 4.x log-based capture from SQL Server requires manual intervention each time primary indexes are rebuilt. To achieve the best results new implementations of HVR capture from SQL Server should use HVR 5.x (or higher).

Background

SQL Server's Transaction Log (TLog going forward) records database changes to enable high availability and point-in-time recovery. The TLog is also the source for data replication products like HVR to know what operations were submitted to the database.

The TLog is implemented as a single logical file implemented by one or more physical files. The TLog is designed to be overwritten provided nothing is blocking overwrite from happening. The TLog will grow and potentially fill up all available disk space if it cannot be overwritten.

The concept of moving the TLog "forward" to allow a section to be overwritten is called Log Truncation.

A DBA can find out at any point in time what delays Log Truncation using the following query:

select name, log_reuse_wait_desc from sys.databases

SQL Server Recovery Model

At present, there are 9 possible reasons why Log Truncation cannot proceed.

To ensure data replication will not miss any data from the transaction logs the database must be in full recovery mode. Following this take an initial full backup of the database to start an uninterruptible backup trail.

Action Capture

In HVR the data flow between location groups is defined using actions. The Capture action sets up Change Data Capture (CDC) from a Location Group. By default, database capture uses access to the transaction log to retrieve incremental changes. HVR filters the available options for the action based on the database type(s) in the context of the Location Group(s) where the action is defined. The screenshot above is filtered for database SQL Server, highlighting the options discussed in this post.

By default, HVR performs change data capture by reading changes out of the current TLog. The technology will transparently read from the backup logs to capture changes as quickly as possible if for whatever reason capture runs behind (e.g. because it was turned off for some time). HVR generally supports multiple concurrent change data capture setups out of a single database and, in line with HVR's data integration capabilities, users can choose to replicate a subset of the data by a table, column, or row.

HVR also supports log-based capture on just transaction log backup files. This so-called Archive Log Only (ALO) mode is activated using the options /ArchiveLogPath, /ArchiveLogFormat, and /ArchiveLogOnly in the Capture action. ALO mode will generally expose higher latency than non-ALO mode because changes can only be capture when the TLog backup file arrives.

Log Read Method

The option /LogReadMethod has two values:

1. DIRECT (default) mode is generally faster and more efficient than the other mode. The DIRECT mode must be used when running capture on a standby node of an AlwaysOn cluster, and when using the Archive Log Only (ALO) mode. Except for ALO mode, the DIRECT mode requires (1) an OS user with Administrator rights, and (2) a SQL Server account with sysadmin privilege. ALO mode enables high-performance log-based CDC with minimal OS and minimal database privileges, at the cost of higher capture latency.
2. SQL mode uses stored database function calls to retrieve incremental log fragments. The benefits of this mode include (1) the ability to run with minimal OS and database privileges, and (2) the ability to run capture remotely on a server that is not running the database. Note that remote capture is less efficient than capture using an HVR installation on the database server. SQL mode will impose more overhead on the transactional database than the DIRECT mode. Prior to HVR 5.2, the SQL mode did not support DDL capture. Also, prior to HVR 5.2 the SQL mode requires manual intervention after a primary index is rebuilt to prevent data loss. If on an ongoing basis /LogReadMethod=SQL cannot keep up with the transaction load on the database then you must fall back to using /LogReadMethod=DIRECT.

You can switch between /LogReadMethod=SQL and /LogReadMethod=DIRECT by changing the option and running HVR Initialize with the advanced option Scripts and Jobs selected.

Supplemental Logging

By default, SQL Server only writes to the TLog old and new values for columns that changed in the update, including an internal reference to the row that was changed. Logical replication requires knowledge of the external row identifier in order to replay the update in a different system. To indicate that the database must write more column data to the TLog for updates, HVR uses the term "supplemental logging". Unlike some other databases (like Oracle), SQL Server does not have an alter table command or similar to enable supplemental logging. However, two native SQL Server features result in the side effect that column values are supplementally logged:

1. CDC tables, which, until SQL Server 2016 SP1, is an Enterprise Edition only feature, available on SQL 2008 and higher. Note a CDC table on a base table prevents table truncation, but allows other DDL operations. This option can be used without the Replication Option installed on SQL Server.
2. Articles are part of SQL Server Replication, available on Standard and Enterprise Editions but not on Express Edition. In order to create Articles the Replication Option must be installed and a Distribution Database must be created. Articles can only be created on tables with a primary key, and an Article on a table prevents any DDL operation on the table.

Option /SupplementalLogging has four possible values:

1. CDCTAB (default): operation HVR Initialize using Create or Replace Objects, for Advanced Option Supplemental Logging, verifies that every table in the channel has a CDC table. HVR uses hvr_<object id> for the CDC tables it generates. When running HVR Initialize with option Drop Objects and advanced option Supplemental Logging selected, HVR will drop the CDC tables it created.
2. ARTICLE_OR_CDCTAB results in HVR creating an Article on the table if possible (i.e. the table has a primary key), and falling back to creating a CDC table otherwise.
3. EXISTING results in HVR not creating any extra objects, assuming that supplemental logging is already enabled, for example because the entire database is replicated already.
4. EXISTING_OR_CDCTAB results in HVR verifying whether supplemental logging is already enabled on tables in the channel, and if not CDC tables will be created.

A common concern for CDC tables is the overhead associated with capturing and storing (and again logging) the changes in the change tables. HVR mitigates most of these issues through the /LogTruncate setting (discussed below).

Log Truncate

In order for SQL Server to move the truncation point forward in its TLog other operations must have released the truncation point they cannot move beyond. The objects HVR created to enable supplemental logging (CDC tables and/or Articles) trigger internals in SQL Server that will cause REPLICATION to show up as the reason why a truncation point cannot be moved. The option /LogTruncate specifies how the truncation point for REPLICATION will be released so that SQL Server's log truncation can happen. Needless to say, it is critically important that the truncation point moves forward to avoid the TLog from growing until disk space fills up.

Following are the available options for /LogTruncate:

1. CAP_JOB (default) is used to indicate that the capture job regularly calls sp_repldone to unconditionally release the hold on the truncation point for REPLICATION. When this option is selected and HVR Initialize is run, depending on the value of /SupplementalLogging, HVR will also drop/disable the SQL Server agent jobs that get created when CDC tables are created through the CDC stored procedures, and the agent jobs related to data distribution. As a result, the additional impact of the auxiliary database objects to enable supplemental logging is minimized. For example, CDC tables are not populated (and space allocation increased) because the agent job to do this is dropped. Multiple capture jobs can be set up on a single database with option CAP_JOB selected. However, note that if no capture job is running with the CDC tables and/or Articles in place the TLog will grow because the truncation point for REPLICATION is not released. Do not set this option if another data replication solution is in place or CDC tables are used in the database.
2. CAP_JOB_RETAIN should only be used if the database's recovery mode is set to Simple Recovery. HVR supports only a single capture process on a database in Simple Recovery mode. This setting will also result in SQL Server's agent jobs being dropped/disabled, so the TLog will grow when the capture job is not running and CDC tables and/or Articles are still in place. Do not set this option if another data replication solution is in place or CDC tables are used in the database.
3. LOGRELEASE_TASK should be set if a separate job/task is created to release the truncation point for REPLICATION. For example schedule a separate SQL Server Agent job to unconditionally call sp_repldone at an interval. HVR also provides its own Log Release capability that can be accessed in the GUI through the pop-up menu on the SQL Server location. Choosing the option LOGRELEASE_TASK will also result in SQL Server's agent jobs being dropped/disabled. However, as long as the scheduled log release task runs the truncation point for REPLICATION is released, even if the capture job(s) is (are) not running. This option should only be used in conjunction with another replication or CDC solution if the log release task that is scheduled is aware of the requirements of the other solution.
4. NATIVE_DBMS_AGENT should be used if native replication and/or CDC tables are in use on the database. With this option, HVR will not drop/disable the native SQL Server Agent jobs that are created when CDC tables and/or Articles are created. HVR will also not interfere with the release of the truncation point for REPLICATION.

Make sure to pick the correct option to not interfere with any other replication on your database, and monitor TLog growth to ensure the log file is not going to fill up the available disk space.