Skip to main content

Setting advanced connection properties

ON THIS PAGE

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.

      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. 

      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.

      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.

      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). You can run the backup utility yourself or you can configure Qlik Replicate to run it as described in Backup file preprocessing command below.

    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. Qlik Replicate will first look for the changes in the online transaction logs. In the event that Replicate cannot find the changes in the online transaction logs, it will look for them in the backup transaction logs instead.
    • Prioritize Backup Logs - When this option is enabled, Qlik 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.

      In the event that Replicate cannot find the changes 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.

    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 Attunity 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 Attunity 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 Limitations.

    • 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.
  • Backup folder preprocessing command: You can use a third-party utility to convert the transaction logs to standard Microsoft SQL Server format (if they are in a different format) and back them up to an alternate backup folder. This option should be used in conjunction with the Alternate backup folder option described above.

    Prerequisites and Notes:

    The command is invoked via the XP_CMDSHELL extended procedure.

    • The backup utility is responsible for setting the system return code (0 for success, 1 for failure), assuming that this code is delegated as the XP_CMDSHELL return value.
    • The backup utility invoked by XP_CMDSHELL must have the same security rights as the Microsoft SQL Server service account.
    • XP_CMDSHELL is normally disabled. It can be enabled and disabled by using the Policy-Based Management or by executing SP_CONFIGURE.
    • Using this extended procedure requires CONTROL SERVER permission (at least).

    Command Usage:

    The backup utility should provide Qlik Replicate with the following parameters:

    • {BACKUP_INFILE} - The full path to the original backed up transaction log.
    • {ALTDIR_OUTFILE} - The specifications of the target file to transfer to the alternate backup folder.
    • {BACKUP_SET} - The backup set to be processed within the backup log.

    Example command:

    C:\Temp\YourBackupUtility.exe -B{BACKUP_INFILE} -A{ALTDIR_OUTFILE}"

    Note:

    Directory names in the command path or file names in the actual command that contain spaces must be enclosed in double-quotes:

    Example:

    C:\temp\test\"my program"\"new version"\converter.exe -A{"input file"} -B{outfile}

  • Delete processed backup logs: Select this option to delete the backup logs after they have been read.
  • 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).

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.

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.