Migrate and Replicate Data with Qlik Replicate

On this page Carat arrow pointing down
Warning:
Cockroach Labs will stop providing Assistance Support for v22.2 on June 5, 2024. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

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 v22.2 production release, and have created a SQL user that you can use for your Qlik Replicate target endpoint.

    • If your CockroachDB cluster is running v22.2.4 or later, set the following session variables using ALTER ROLE ... SET {session variable}:

      icon/buttons/copy
      ALTER ROLE {username} SET copy_from_retries_enabled = true;
      
      icon/buttons/copy
      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.

  • 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.

Note:

To use a CockroachDB Serverless cluster as the target endpoint, set the Database name to {serverless-hostname}.{database-name} in the Qlik Replicate dialog. For details on how to find these parameters, see Connect to a CockroachDB Serverless cluster. Also set Secure Socket Layer (SSL) mode to require.

  • 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


Yes No
On this page

Yes No