Capture from SQL Server using Archive Only

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

Normally, Local Data Processing locates the transaction log backup files by querying the backup history table in the msdb database. Defining ARCHIVE WATCH DIRECTORY () tells Local Data Processing 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

Local Data Processing will search for the log archives in the directory specified in this field/property.

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

File Name Format

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

This parameter has no default, so it must be defined if ARCHIVE WATCH DIRECTORY () 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

• Local Data Processing 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.