Setting advanced connection properties

In the Advanced tab, you can set the following properties:

• Prevent truncation of unread changes from TLOG: For optimal performance, Qlik Replicate will try to capture all unread changes from the active transaction log (TLOG). However, sometimes due to truncation, the active TLOG may not contain all of the unread changes. When this occurs, Qlik Replicate accesses the backup log to capture the missing changes. To minimize the need to access the backup log, Qlik Replicate prevents truncation using one of the following methods:

  • Start transactions in the database: This is the default method. When this method is used, Qlik Replicate prevents TLOG truncation by mimicking a transaction in the database. As long as such a transaction is open, changes that appear after the transaction started will not be truncated. If you need Microsoft Replication to be enabled in your database, then you must choose this method.

    Information note

    This method also requires the Log Reader Agent to be running to enable truncation of the Microsoft SQL Server active transaction log. Note that if the Log Reader Agent is not running, the active log may become full, causing the source database to be essentially "read-only" until the issue is resolved. 

    Information note

    When this option is selected, Replicate creates a table named attrep_truncation_safeguard in the source database. This is a very small but important table whose purpose is to prevent truncation of the transaction log by mimicking a transaction in the database. Make sure that the table is not included any maintenance plan as it may cause the maintenance job to fail. The table can be safely deleted if there are no tasks configured with the Start transactions in the database option.

  • Exclusively use sp_repldone within a single task: When this method is used, Qlik Replicate reads the changes and then uses sp_repldone to mark the TLOG transactions as ready for truncation. Although this method does not involve any transactional activities, it can only be used when Microsoft Replication is not running. Also, using this method, only one Qlik Replicate task can access the database at any given time. Therefore, if you need to run parallel Qlik Replicate tasks against the same database, use the default method.

    Information note

    • This method requires the Log Reader Agent to be stopped in the database. If the Log Reader Agent is running when the task starts, Qlik Replicate will forcibly stop it. Alternatively, you can stop the Log Reader Agent manually, before starting the Qlik Replicate task. For instructions on how to do this, refer to the Microsoft SQL Server Management Studio help.
    • When using this method with MS-CDC, the CDC Capture and CDC Cleanup jobs should be stopped and disabled.
    • This method is not available when the Microsoft SQL Server Replication job resides on a remote Distributor machine as Replicate does not have access to the remote machine.

  • Apply TLOG truncation prevention policy every (seconds): Specify how often to prevent TLOG truncation using one of the methods describes above. Factors that you should consider when determining the policy frequency include storage availability, backup and log routines, and the rate at which Qlik Replicate processes events.

    Information note

    If the Exclusively use sp_repldone within a single task option is also selected, setting a very short interval (e.g. 1) may adversely affect task processing, and possibly even result in an error.

• Alternate backup folder: The location of the backup logs when using a third-party utility to back up the transaction logs (i.e. instead of Microsoft SQL Server’s own backup mechanism).

  Note that the backup files must be exported to the specified location in standard Microsoft SQL Server format.

• Change processing mode: Choose one of the following change processing modes:

  • Prioritize Online Logs - This is the default. Replicate will first look for the changes in the online transaction logs. If the changes cannot be found in the online transaction logs, it will look for them in the backup transaction logs instead.

  • Prioritize Backup Logs - When this option is enabled, Replicate will first look for the changes in the backup transaction logs. This can improve performance when reading from the online transaction log is slow (e.g due to lock contention) or when using file-level access to access the backup transaction logs.

    If the changes cannot be found in the backup transaction logs, it will look for them in the online transaction logs instead.

  • Backup Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the backup transaction logs only. Selecting this method results in increased latency due to the interval between backups. The actual latency time will remain constant, but will vary according to the backup schedule.
  • Online Logs Only - When this option is selected, Qlik Replicate will try and find the changes in the online transaction logs only.

• Replicate has file-level access to the backup log files: Select this option if Qlik Replicate has been granted file-level access to the backup log files in the Alternate backup folder.

  Information note

  When Qlik Replicate has file-level access to the backup transaction logs, the following rules apply:

  • The Alternate backup folder must be a common shared network folder, for example: \\temp\backup.

  • The Qlik Replicate Server service must be configured to log on using the user name and password specified in the Backup folder user name and Backup folder password fields.

    To do this:

    In the Windows Services console, double-click the Qlik Replicate Server service.

    In the Log On tab, select This account and then enter the user name and password.

  • The specified user must be granted Read permission to the alternate backup folder (i.e. the shared network folder).

  For a complete list of the limitations affecting file-level access, see Limitation and considerations.

  • Backup folder user name: The user name required to access the backup folder when Qlik Replicate has file-level access.
  • Backup folder password: The password required to access the backup folder when Qlik Replicate has file-level access.
• Select virtual backup device types: When this option is selected, Qlik Replicate will read changes from the specified virtual device(s). Usually, this option only needs to be enabled when using a third-party backup utility (which will be recorded as a virtual device).

• Decrypt Always Encrypted columns: An Always Encrypted column is encrypted using a Column Encryption Key (CEK), which itself is encrypted using the Column Master Key (CMK). As such, the CMK is needed to decrypt the CEK, which can then be used to view the data in plain text. To use the Decrypt Always Encrypted columns option, the DBA should first export all of the required Column Master Keys to a single PFX file.

  When exporting the certificates, make sure to:

  • Select Export the private key, PKCS #12 format, and Export all extended properties.
  • Set a password for the PFX file and select AE256-SHA256 encryption.

  Certificates can be exported using either Windows Certificate Manager or a PowerShell script.

   

  After the PFX file has been created, complete the following fields to allow Replicate to decrypt data from Always Encrypted columns.

  • Column master keys file: Enter the full path of the PFX file containing the CMKs and private keys.

    Example: C:\temp\AE\CMK_certs.pfx

  • Column master keys password: The password specified when the PFX file was created.

  Information noteThe decrypted data will appear as plaintext on the target.

Internal parameters

Internal parameters are parameters that are not exposed in the UI. You should only use them if instructed by Qlik Support.

To add internal Qlik Replicate parameters:

1. Click the Internal Parameters link.

   The Internal Parameters dialog box opens.

2. In the edit box, type the name of the parameter you need to add and then click it.
3. The parameter is added to the table below the search box with its default value.
4. Change the default value as required.
5. To reset the parameter value to its default, click the "Restore default value" icon at the end of the row.

More options

These options are not exposed in the UI as they are only relevant to specific versions or environments. Consequently, do not set these options unless explicitly instructed to do so by Qlik Support or product documentation.

To set an option, simply copy the option into the Add feature name field and click Add. Then set the value or enable the option according to the instructions you received.

Settings summary

You can view a summary of your settings by clicking the Setting Summary link. This is useful if you need to send a summary of your settings to Qlik Support.