Migrate and Replicate Data with Qlik Replicate

Qlik offers a service called Qlik Replicate that you can use to do the following:

• Migrate data to CockroachDB from an existing, publicly hosted database containing application data, such as PostgreSQL, MySQL, Oracle, or Microsoft SQL Server.

As of this writing, Qlik supports the following database sources:

• Cassandra
• Couchbase
• DB2 for iSeries
• DB2 for LUW
• DB2 for z/OS
• HP Nonstop Enscribe (AIS)
• HP Nonstop SQL/MP (AIS)
• IBM Informix
• IMS/DB
• MariaDB
• Microsoft SQL Server
• MongoDB
• MySQL
• OpenVMS RMS
• Oracle
• Percona
• PostgreSQL
• SAP HANA
• SAP Sybase ASE
• Other via ODBC (with or without CDC)

This page describes the Qlik Replicate functionality at a high level. For detailed information, refer to the tutorial and documentation provided when signing up for Qlik Replicate.

Before you begin

Complete the following items before using Qlik Replicate:

• Ensure you have a secure, publicly available CockroachDB cluster running the latest v24.1 production release, and have created a SQL user that you can use for your Qlik Replicate target endpoint.

  • Set the following session variables using ALTER ROLE ... SET {session variable}:

    ALTER ROLE {username} SET copy_from_retries_enabled = true;
    

    ALTER ROLE {username} SET copy_from_atomic_enabled = false;
    

    This prevents a potential issue when migrating especially large tables with millions of rows.

• If you are migrating to a CockroachDB Cloud cluster and plan to use replication as part of your migration strategy, you must first disable revision history for cluster backups for the migration to succeed.

  Warning:

  You will not be able to run a point-in-time restore as long as revision history for cluster backups is disabled. Once you verify in the Qlik Replicate Monitor view that the migration succeeded, you should re-enable revision history.
  • If the output of SHOW SCHEDULES shows any backup schedules, run ALTER BACKUP SCHEDULE {schedule_id} SET WITH revision_history = 'false' for each backup schedule.
  • If the output of SHOW SCHEDULES does not show backup schedules, contact Support to disable revision history for cluster backups.

• Manually create all schema objects in the target CockroachDB cluster. Qlik can create a basic schema, but does not create indexes or constraints such as foreign keys and defaults.

  • If you are migrating from PostgreSQL, MySQL, Oracle, or Microsoft SQL Server, use the Schema Conversion Tool to convert and export your schema. Ensure that any schema changes are also reflected on your tables, or add transformation rules. If you make substantial schema changes, the Qlik Replicate migration may fail.

    Note:

    All tables must have an explicitly defined primary key. For more guidance, see the Migration Overview.

Migrate and replicate data to CockroachDB

You can use Qlik Replicate to migrate tables from a source database to CockroachDB. This can comprise an initial load that copies the selected schemas and their data from the source database to CockroachDB, followed by continuous replication of ongoing changes using Qlik change data capture (CDC).

In the Qlik Replicate interface, the source database is configured as a source endpoint with the appropriate dialect, and CockroachDB is configured as a PostgreSQL target endpoint. For information about where to find the CockroachDB connection parameters, see Connect to a CockroachDB Cluster.

• To perform both an initial load and continuous replication of ongoing changes to the target tables, select Full Load and Apply Changes. This minimizes downtime for your migration.
• To perform a one-time migration to CockroachDB, select Full Load only.

To preserve the schema you manually created, select TRUNCATE before loading or Do nothing.

See also

• Migration Overview
• Schema Conversion Tool
• Change Data Capture Overview
• Third-Party Tools Supported by Cockroach Labs
• Migrate with AWS Database Migration Service (DMS)