Capture From SQL Server Using Archive Only

In this capture method (Capture_Method=ARCHIVE_ONLY), Fivetran HVR reads changes from redo archive files available in the directory specified in ARCHIVE WATCH DIRECTORY (Archive_Log_Path). Also, HVR must be configured to consider only the files that match the format defined in FILE NAME FORMAT (Archive_Log_Format).

Normally, HVR locates the transaction log backup files by querying the backup history table in the msdb database. Defining ARCHIVE WATCH DIRECTORY (Archive_Log_Path) tells HVR to search for the log backup files in the directory specified in this field/property.

This capture method will generally expose higher latency than the other capture methods because changes can only be captured when the transaction log backup file is created. This capture method enables high performance log-based Change Data Capture (CDC) with minimal OS and minimal database privileges, at the cost of higher capture latency.

Archive Watch Directory

HVR will search for the log archives in the directory specified in this field/property.

When ARCHIVE WATCH DIRECTORY (Archive_Log_Path) is defined, it is mandatory to define FILE NAME FORMAT (Archive_Log_Format).

File Name Format

This field/property describes the filename format (template) of the transaction log archive files stored in the ARCHIVE WATCH DIRECTORY (Archive_Log_Path).

This parameter has no default, so it must be defined if ARCHIVE WATCH DIRECTORY (Archive_Log_Path) is defined.

This field/property accepts the following format variables:

• %d - database name
• %Y - year (up to 4 digit decimal integer)
• %M - month (up to 2 digit decimal integer)
• %D - day (up to 2 digit decimal integer)
• %h - hours (up to 2 digit decimal integer)
• %m - minutes (up to 2 digit decimal integer)
• %s - seconds (up to 2 digit decimal integer)
• %n - file sequence number (up to 64 bit decimal integer)
• %% - matches %
• * - wildcard, matches zero or more characters

• HVR uses the %Y, %M, %D, %h, %m, %s and %n values to sort and processes the log backup files in the correct (chronological) order.
• The combinations of the %Y, %M, %D and %h, %m, %s values are expected to form valid date and time values; however, no validation is performed.
• Any value that is missing from the format string is considered to be 0.
• When sorting the files comparison is done in the following order: %Y, %M, %D, %h, %m, %s, %n.