Capture From SQL Server Using Direct Transaction Log Access

In this capture method (Capture_Method=DIRECT), Fivetran HVR 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. HVR 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 permissions. For versions up to 6.1.0/12, only SysAdmin permission model is supported.
The HVR remote agent must be installed on the SQL Server source database server.
On Linux, the HVR operating system 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 HVR Agent Listener service must run either:
- as the same Windows user that the source SQL Server service runs, or
- as other Windows user (e.g. HVR database User), which must have Debug programs (SeDebugPrivilege) policy enabled. For more information about Debug programs security policy, refer to the Microsoft documentation.
  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

HVR 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 HVR's user interface (UI), command-line interface (CLI), or REST API.

HVR supports capture from databases whose Database Encryption Key (DEK) is protected by Certificates.
The Direct and Archive Only capture methods do not support capturing changes from databases whose DEK is protected by an asymmetric key. However, you can use the SQL Access method to capture changes from these databases, unless the Archive_Log_Path and Archive_Log_Format properties are defined. If these location properties are defined, the capture limitation applies to the SQL Access method as well.

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 (Step 4) or when editing source and target properties 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 hvrlocationconfig 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"

REST API

To add a TDE certificate, use the REST API endpoint for modifying location properties
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"
        }   
    ]   
}

When the certificate protecting DEK needs to be changed, another certificate must be added to the location properties; HVR 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. HVR handles this seamlessly.