Capture from SQL Server using Direct Transaction Log Access

In this capture method (=DIRECT), Fivetran Local Data Processing reads transaction log records directly from the DBMS log file using the file I/O. This method is very fast in capturing changes from the SQL Server database. Local Data Processing uses this capture method to capture changes from the SQL Server's current and backup transaction log files, as well as compressed backup transaction log files.

The benefits of the Direct Transaction Log Access capture method are:

It is faster and less resource-intensive when capturing changes from database locations, especially for highly loaded databases. To ensure uninterrupted, low-latency CDC, capture must be running faster than the database is writing the logs. The Direct Transaction Log Access method and pipelined execution ensures optimum efficiency to keep up with the database log writers. As a result, when capture runs continuously, it will be capturing from the tail end of the log where the log writer(s) are writing.
It supports capture from SQL Server Always On AG secondary database.

To capture using the Direct Transaction Log Access method, the following are required:

The database account must have SysAdmin or DbOwner or Minimal . For versions up to 6.1.0/12, only SysAdmin permission model is supported.
The Local Data Processing remote agent must be installed on the SQL Server source database server.
On Linux, the Local Data Processing database User must have read access to the .ldf files. For this, the User should typically be added to the operating system user group mssql.
On Windows, the service must run either:
- as the same Windows user that the source SQL Server service runs, or
- as other Windows user (e.g. Local Data Processing database User), which must have Debug programs (SeDebugPrivilege) policy enabled. For more information about Debug programs security policy, refer to the .
  Click here for the steps to enable/grant Debug programs policy
  User account policies can be managed using the Windows Local Security Policy console (accessible from Control Panel ▶ Administrative Tools). The shortcut command to access this console is secpol.msc.
  1. In the Local Security Policy window, expand Local Policies and click User Rights Assignment.
  2. Double-click Debug Programs policy available in the list of policies.
  3. In the Debug programs Properties dialog, click Add User or Group; displays the Select Users, Computers, Accounts, or Groups dialog.
  4. In the Enter the object names to select field, enter the user account name to whom you want to enable this policy, and click OK.
  5. Click OK.
  By default, members of the Administrators group have this right enabled.

Capturing from SQL Server TDE

Since v6.1.0/10

Local Data Processing supports capturing data from database(s) encrypted using SQL Server Transparent Data Encryption (TDE). To enable capturing from an encrypted database, you need to specify a certificate that protects the database encryption key (DEK). You can enable this option using Local Data Processing's (UI), (CLI), or .

When using CLI or REST API, you must define the following location properties for a source location:

Let us explore each method in detail.

User Interface

Since v6.1.0/32

To add a TDE certificate, you can use the Upload Certificate dialog either when creating a new SQL Server location () or when of an existing SQL Server location.

For example, when creating a new SQL Server location:

Proceed to step 4 - Configure Capture/Integrate for SQL Server Location, scroll down to and select TDE Certificates.
Click the Add Certificate button.
In the Upload Certificate dialog, specify the certificate identifier, upload the public certificate and private key, and provide the password of the private key.
Click Confirm.

Command Line Interface

To add a TDE certificate, use the CLI command.

For example, the following command adds a certificate (cert1), where dbcert.crt and dbcert.pvk are the files (or paths to files) storing the certificate and private key, respectively.

 hvrlocationconfig hub_name loc_name "SqlServer_TDE_Database_Certificates.cert1=@dbcert.crt" "SqlServer_TDE_Database_Private_Keys.cert1=@dbcert.pvk" "SqlServer_TDE_Database_Private_Key_Passwords.cert1=Passwd1"
content_copy

REST API

To add a TDE certificate, use the REST API endpoint for
For example, the following request body adds a certificate (cert1), where dbcert.crt and dbcert.pvk are the files that store the certificate and private key, respectively.

{
  "loc_prop_args":  [
        {   
            "key": ["SqlServer_TDE_Database_Certificates", "cert1"],
            "value": "<base64-encoded content of file dbcert.crt>"
        },  
        {   
            "key": ["SqlServer_TDE_Database_Private_Keys", "cert1"],
            "value": "<base64-encoded content of file dbcert.pvk>"
        },  
        {   
            "key": ["SqlServer_TDE_Database_Private_Key_Passwords", "cert1"],
            "value": "Passwd1"
        }   
    ]   
}
content_copy

When the certificate protecting DEK needs to be changed, another certificate must be added to the location properties; Local Data Processing requires both the old and new certificates to read the logs at the certificate update point. However, no action is necessary when only the DEK is changed, meaning when a new DEK is generated, but the same certificate is used to protect both the old and new DEKs. Local Data Processing handles this seamlessly.